From: Greg Smith <gsmith@gregsmith.com>
To: Dmitry Koterov <dmitry@koterov.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bgwriter and pg_stat_bgwriter.buffers_clean aspects
Date: Sun, 28 Dec 2008 17:04:47 -0500 (EST)
Message-ID: <Pine.GSO.4.64.0812281652170.14465@westnet.com> (raw)
In-Reply-To: <d7df81620812251508r3ba4a736g13a237778c238aa3@mail.gmail.com>
References: <d7df81620812251337s41fcbefdyf7089dd8c57e6214@mail.gmail.com>
<d7df81620812251343h12072e11id0fae9d4d4b54ff4@mail.gmail.com>
<d7df81620812251508r3ba4a736g13a237778c238aa3@mail.gmail.com>
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@gregsmith.com http://www.gregsmith.com Baltimore, MD
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com, dmitry@koterov.ru
Subject: Re: Bgwriter and pg_stat_bgwriter.buffers_clean aspects
In-Reply-To: <Pine.GSO.4.64.0812281652170.14465@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox