Re: Bgwriter and pg_stat_bgwriter.buffers_clean aspects

On Fri, 26 Dec 2008, Dmitry Koterov wrote:

> checkpoint_timeout = 1min

Your system is having a checkpoint every minute. You can't do that and
expect the background writer to do anything useful. As shown in your
stats, all the dirty buffers are getting written out by those constant
checkpoints.

> What I am trying to achieve is that all writing operation are performed
> asynchronously and mostly flushed to the disk before a CHECKPOINT
> occurred, so CHECKPOINT is cheap thanks to bgwiter work.

The background writer only tries to write out things that haven't been
accessed recently, because the tests we did suggested the duplicated
writes from any other approach negated the benefits from writing them
earlier. So it's not possible to get all the buffers clean before the
checkpoint starts, the ones that have been recently used can't get written
except during a checkpoint.

What does work instead is to spread the checkpoint writes over a long
period, such that they are an asynchronous trickle of smaller writes.
For that to work, you need to set checkpoint_timeout to a fairly long
period (at least the default of 5 minutes if not longer) and
checkpoint_segments to something fairly large. You can know the segments
are large enough when most of the checkpoints show up in the
checkpoints_timed count.

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