From: Dmitry Koterov <dmitry@koterov.ru>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bgwriter and pg_stat_bgwriter.buffers_clean aspects
Date: Tue, 6 Jan 2009 14:23:16 +0300
Message-ID: <d7df81620901060323v1cd89b9bm2714c0d522b987e2@mail.gmail.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0812281652170.14465@westnet.com>
References: <d7df81620812251337s41fcbefdyf7089dd8c57e6214@mail.gmail.com>
<d7df81620812251343h12072e11id0fae9d4d4b54ff4@mail.gmail.com>
<d7df81620812251508r3ba4a736g13a237778c238aa3@mail.gmail.com>
<Pine.GSO.4.64.0812281652170.14465@westnet.com>
Thank you.
But why buffers_backend is so high? As I understood from your article,
buffers_backend shows the number of writes immediately caused by any write
operations, e.g. when an INSERT has to flush something on disk, because it
has no space left for a new data in shared buffers. I suppose these flushes
slow down operating greatly, and I realy see this: in my environment INSERT
is usually performed in 1-2 ms, but sometimes it is executed in 5-6 seconds
or even more (10 seconds), which touches statement_timeout barrier and fails
the whole transaction.
The main purpose is to minimize INSERT/UPDATE time or, at least, make it
more predictable.
Could you please give an advice how to achieve this?
On Mon, Dec 29, 2008 at 1:04 AM, Greg Smith <gsmith@gregsmith.com> wrote:
> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
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: dmitry@koterov.ru, gsmith@gregsmith.com
Subject: Re: Bgwriter and pg_stat_bgwriter.buffers_clean aspects
In-Reply-To: <d7df81620901060323v1cd89b9bm2714c0d522b987e2@mail.gmail.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