Re: Bgwriter and pg_stat_bgwriter.buffers_clean aspects

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Dmitry Koterov <dmitry(at)koterov(dot)ru>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Bgwriter and pg_stat_bgwriter.buffers_clean aspects
Date: 2008-12-28 22:04:47
Message-ID: Pine.GSO.4.64.0812281652170.14465@westnet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message plu 12 2008-12-29 00:32:29 Re: Finding foreign keys that are missing indexes
Previous Message Adrian Klaver 2008-12-28 21:59:05 Re: timestamp differences

Browse pgsql-performance by date

  From Date Subject
Next Message Ted Allen 2008-12-29 03:11:47 Re: Troubles dumping a very large table.
Previous Message Tom Lane 2008-12-26 20:18:50 Re: Troubles dumping a very large table.