Re: Checkpoint Tuning Question

On Wed, 8 Jul 2009, Dan Armbrust wrote:

> With checkpoint_segments set to 10, the checkpoints appear to be
> happening due to checkpoint_timeout - which I've left at the default
> of 5 minutes.

OK, then that's as far upwards as you probably need to tweak that for your
workload, even though most systems tuned for performance need 30+ instead.

If what you're getting nailed with is the pause during the initial
checkpoint processing, the only real option you have on the database side
is to lower shared_buffers. You might get some improvement playing with
operating system tunables too, to make it buffer less data and write more
aggressively instead. The idea is that when the checkpoint starts, there
should be little else buffered already. On Linux this is best
accomplished with tuning dirty_background_ratio downward for example.

There's more on this subject than you probably ever wanted to know at
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm if you
haven't seen that already.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD