Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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

pgsql-performance by date

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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group