Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the documentation of PostgreSQL that Asynchroneous commits might be an option for boosting performance when small data loss in case of a server crash is acceptable. We run the backend for an iPhone multiplayer game with quite simple, but a lot of, requests per second. Since it's a game and not a financial system we are running, the risk of data loss seems acceptable.

Does anyone have experience in difference in performance comparing synchroneous/asynchroneous commits? Is there any risks with using this option, apart from data loss in case of a server crash? Is there any risk that the queue of uncommitted transactions will keep growing since we will serve more requests?

Our setup PostgreSQL, Nginx, Ruby on Rails, Unicorn

share|improve this question

migrated from stackoverflow.com Nov 1 '12 at 23:39

1 Answer

up vote 4 down vote

Your risk is data loss, not data corruption. An ACID platform guarantees transactions are

  • atomic,
  • consistent,
  • isolated, and
  • durable.

With asynchronous commits, you give up "durable". For your application, that seems a defensible decision.

If you have some transactions that need to be durable (new user registrations?) you can have that, too. PREPARE TRANSACTION is always synchronous; so is SET synchronous_commit = on. But it will interfere a little with all those asynchronous commits.

Is there any risk that the queue of uncommitted transactions will keep growing

No, not if my understanding is correct. The WAL writer periodically writes all unwritten WAL records to disk. It doesn't see 1000 unwritten WAL records and decide, "Well, I can write 17 of them this time." If there are 1000 unwritten WAL records when the timer fires, it writes all 1000 to disk.

If your database server is constantly and heavily overloaded, I guess users might experience some "jerks" in performance if the WAL writer pushes a huge backlog to disk.

In every useful database application, you eventually have to think about upgrading hardware. Sometimes, the only defensible solution is a faster box.

PostgreSQL 9.1's wal_writer_delay setting.

share|improve this answer
2  
You can also just SET synchronous_commit = on within transactions that must be committed immediately, rather than mucking with complicated and inefficient two-phase commit via PREPARE TRANSACTION. – Craig Ringer Nov 1 '12 at 23:41
Thanks, Craig. I incorporated that into the answer. – Mike Sherrill 'Catcall' Nov 2 '12 at 12:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.