Take the 2-minute tour ×
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.

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.

share|improve this question
    
Can you post your version and preferably some details about your storage hardware? –  Jack Douglas Jan 5 '12 at 16:24
    
Did you increase checkpoint_segments as recommended? What happened? –  a_horse_with_no_name Jan 5 '12 at 23:01
2  
Another excellent resource for these kind of questions is Gregory Smith's book PostgreSQL 9.0 High Performance. –  j.p. Jan 7 '12 at 19:52

2 Answers 2

up vote 9 down vote accepted

1 Gigabye 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 postgres 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.

share|improve this answer
    
The write volume is actually almost completely uniform: this is the data store for hardware monitoring software that polls about every second, continuously, 24x7. I could calculate the exact data rate, but it fluctuates somewhat as the programmers add and remove monitor points. –  Daniel Lyons Jan 6 '12 at 6:03
    
Well, if the rate is 1G a day and it's smooth, then almost any subsystem can handle the write load, you just want to keep it smooth, which the checkpoint completion target being set to near 1.0 and a long checkpoint timeout should get you. –  Scott Marlowe Jan 9 '12 at 17:31

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.

share|improve this answer
2  
If write performance matters, a battery backed controller between the OS and spinning hard drives can make a HUGE difference. –  Scott Marlowe Jan 6 '12 at 4:26
1  
+1 for synchronous_commit = off –  François Beausoleil Jan 6 '12 at 15:33

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.