Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id BB9D62E007C for ; Mon, 3 Mar 2008 13:06:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79533-02 for ; Mon, 3 Mar 2008 13:06:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id E76C62E0088 for ; Mon, 3 Mar 2008 13:06:30 -0400 (AST) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m23H6TnJ026289 for ; Mon, 3 Mar 2008 12:06:30 -0500 (EST) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m23H6TZ1026279 for ; Mon, 3 Mar 2008 12:06:29 -0500 (EST) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Mon, 3 Mar 2008 12:06:29 -0500 (EST) From: Greg Smith X-X-Sender: gsmith@westnet.com To: pgsql-performance@postgresql.org Subject: Re: which is more important? freq of checkpoints or the duration of them? In-Reply-To: <60y78zajka.fsf@dba2.int.libertyrms.com> Message-ID: References: <200803030925.02931.doug@hunley.homeip.net> <60y78zajka.fsf@dba2.int.libertyrms.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/35 X-Sequence-Number: 28989 On Mon, 3 Mar 2008, Chris Browne wrote: > Now, if you have reasonable settings (I'm not sure how well its tuning > is documented :-(), checkpoint "flushes" should be able to be short, > however infrequent they may be. In effect, the "oops, the database got > blocked by checkpoint flushing" issue should now be gone... Ah, if only it were true. The background writer can be made to work fairly well in circa early 8.1 setups where the shared_buffers cache is small. But on more current systems where there's a lot of memory involved, you can't get a tuning aggressive enough to make checkpoint spikes small without wasting a bunch of I/O writing buffers that will just get dirty again before the checkpoint. Since the kinds of systems that have nasty checkpoint spikes are also I/O bound in general, there is no good way to resolve that struggle using the code in 8.1 and 8.2. The checkpoint_completion_target tunable and related code in 8.3 is the first approach to this issue that has a good foundation even with larger buffer caches. You can band-aid some cases well enough to improve things with the background writer in earlier versions, but it's certainly not guaranteed that it's possible even if you spend lots of time fiddling with the settings. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD