Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 14432632194 for ; Tue, 6 Jan 2009 07:23:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 33041-05 for ; Tue, 6 Jan 2009 07:23:21 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.153]) by mail.postgresql.org (Postfix) with ESMTP id F36D963219F for ; Tue, 6 Jan 2009 07:23:20 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 16so2622333fgg.23 for ; Tue, 06 Jan 2009 03:23:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:reply-to :sender:to:subject:cc:in-reply-to:mime-version:content-type :references:x-google-sender-auth; bh=56eBHpw9T4sN/wqNs3blcUBF3GG6HIBU9eJ0/v8tbN8=; b=uhDefUxNISjQuQsrcfDuJSuHF+mzq7fODDl6rIXi8Bzr5aTcMW6ub/bcikcwVxS0o1 3Nro+XnAivLdizMhvg+dHOPD7mTXOLsD73vdcDuLjhDciRM3lzvGSDMN3KSaKCDfADOX 7BciMaP1y4LEicwbwWDGyN0nrPZsUaH1kas+0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:reply-to:sender:to:subject:cc:in-reply-to :mime-version:content-type:references:x-google-sender-auth; b=YOEiZT2yuDfXRlKVb2573PIw1ExmJFFjGcWNK4GC9SDiRtLFo/RGV1NCCB7c2u2YCv iPcmYfhOOyx8sx3wvjmsSyhn4nO6Ci/TSdQXwzjQz4jHnKrwdTizI/86McpVSysXMNgL GPprwS51uaV1DXy9T+QuKHad4oM1+mKAHh3kY= Received: by 10.86.54.3 with SMTP id c3mr12753262fga.60.1231240997018; Tue, 06 Jan 2009 03:23:17 -0800 (PST) Received: by 10.86.84.6 with HTTP; Tue, 6 Jan 2009 03:23:16 -0800 (PST) Message-ID: Date: Tue, 6 Jan 2009 14:23:16 +0300 From: "Dmitry Koterov" Reply-To: dmitry@koterov.ru To: "Greg Smith" Subject: Re: Bgwriter and pg_stat_bgwriter.buffers_clean aspects Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_100576_15254363.1231240996998" References: X-Google-Sender-Auth: c7d03f29e279515d X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200901/9 X-Sequence-Number: 32288 ------=_Part_100576_15254363.1231240996998 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 > ------=_Part_100576_15254363.1231240996998 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_100576_15254363.1231240996998--