Re: background writer being lazy?

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us