Configuring PostgreSQL for write performance

Question

One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed data for that particular instant. The data rate is fairly high; it works out to about a gigabyte a day for one database, about a tenth of that for another one. I don't expect this rate to increase. Read performance is a much lower priority and is currently acceptable.

In the logs I have this message:

LOG:  checkpoints are occurring too frequently (15 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

This value is currently set to 16, which is courtesy of pgtune.

What are the settings I should consider to improve write performance? I would prefer to keep as much safety as possible. Considering the volume of data coming in, I could accept losing some recent data in a failure as long as the bulk of the data were intact.

Edit: I'm using PostgreSQL 9.0 for now, but I plan to upgrade to 9.1. I am not posting the hardware details because while I acknowledge their importance, I ultimately will be needing to make this optimization on several machines with very diverse hardware. If the hardware is essential to the answer, please give me the general information so I can apply the answer to machines with different hardware configurations.

Answer 1

1 Gigabyte a day is not that high of a write load. Spread out throughout the day, that comes out to about 50kbytes a second. A slow USB thumb drive could handle that. I'm assuming it's more bursty though. As a_horse_with_no_name suggests, increase checkpoint segments. 100 or so is not out of the ordinary.

Then increase your checkpoint_timeout to 1 hour, as well as look at increasing your checkpoint_completion_target to something closer to 1.0 (100%). The completion target tells PostgreSQL how aggressively to write in the background so that it's x% complete before running a checkpoint, which force all the data to be written out at once from the WAL and will slow the system to a crawl while it's happening.

The reason you don't usually set it to 100% is that it's pretty common to write to the same block more than once, and by delaying WAL writes out to the main store, you prevent the same block being written twice for no reason.

If it's unlikely you'll be writing to the same block more than once before your timeout occurs, i.e. all you do is insert then setting it pretty high makes sense to raise it to 0.9 or so. The worst that'll happen is you'll write a little more often than you might otherwise need to, but checkpoints impact will be greatly reduced.

Answer 2

In a very 'write heavy' system, you are likely to be limited by the rate WAL can be written during peak activity.

If you really can "accept losing some recent data in a failure" you can turn off synchronous commit which:

can be a useful alternative when performance is more important than exact certainty about the durability of a transaction

If you are able to change your hardware, you could consider any of these for optimizing writes:

• RAID10 over RAID5
• Lots of spindles (might mean 2.5" instead of 3.5" for example)
• SAS over SATA
• 15K over 10K drives
• SSD

--edit

Based on your comment on @Scott's excellent answer: "The write volume is actually almost completely uniform", and the implied data rate of "50kbytes a second", I doubt you need to do anything that risks data loss. Perhaps it would help to know what some of your other configuration parameter are set to.

Answer 3

You might also check the frequency / size of your commits: I ran into an issue recently in which I was trying to update > 1 million records in a single transaction. I got log messages similar to those described by OP, but the transaction could not complete even after several hours. When I broke the write into several smaller transactions (10,000 records or so), the total time required went down to about 15 minutes.

What I think happened was that Postgres spent so much time writing the logs that checkpoint_timeout elapsed before it could make substantial progress saving the records. I'm not sure if that explanation holds up. I still get the warnings, but all the writes are eventually processed. However, I needed (and found) a programmatic workaround rather than one requiring database reconfiguration.

See also http://www.postgresql.org/docs/9.3/static/wal-configuration.html