On 10/31/2011 07:28 PM, Brian Fehrle wrote:
The main thing I am currently seeing is that there are 300X or more buffers written by checkpoints rather than background writer.
Writing buffers at checkpoint time is more efficient than having the background writer handle them. I think your efforts to space checkpoints out may have backfired a bit on you. You're letting 40 minutes of dirty buffers accumulate before they're written out. Putting checkpoint_timeout closer to its default of 5 minutes again may reduce the spikes you're seeing.
The changes you've made to the background writer configuration are also counterproductive, given that it's not really going to trigger anyway. I would only recommend decreasing bgwriter_delay or increasing bgwriter_lru_maxpages or you see the total_clean_max_written value get incremented regularly. If that's not happening, making the background writer run more often and try to do more work just adds overhead.
Also: you've set shared_buffers to 16GB. That's beyond where most people find increases to stop being useful. I'd wager you'll get less spiky performance just by lowering that a lot. The 256MB to 1GB range is where I normally end up on servers where lower latency is prioritized instead of maximum throughput.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
