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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
