DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=ZPookXwxFTiJO1K5tPSqRxjiW885G2pTO8lLmlR8JCidaMEtvLmofs7dYoytxNCn73
	1QPVsEXoK6gQKZjXN9V2xxYCEMQQfouAJZGiXHvm/7tuSNCzOUYRsCC3m2PWjCZjRkQG
	ggV5TgMSP4Hr9fmNdvemJerZLWgT2Vyh8nxsM=
Message-ID: <dcc563d10810081116g6aece154rbadf99951ab4caa2@mail.gmail.com>
Date: Wed, 8 Oct 2008 12:16:33 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Subject: Re: checkpoint_timeout
Cc: "Rafael Domiciano" <rafael.domiciano@gmail.com>,
	"Julio Leyva" <jcleyva@hotmail.com>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <48ECB0DD.EE98.0025.0@wicourts.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <3a0028490810071321j32b4f450m95123d02b9fe6f49@mail.gmail.com>
	<1223475780.6070.10.camel@bnicholson-desktop>
	<3a0028490810080734r26912c72n94d6f509dfb55b6e@mail.gmail.com>
	<BLU102-W53021316E8139B5A348338A13B0@phx.gbl>
	<3a0028490810080919v3762f924je688a56210558e95@mail.gmail.com>
	<BLU102-W420C4D08216238B5121B32A13B0@phx.gbl>
	<3a0028490810080934l211d137uc9dde69cad7879d9@mail.gmail.com>
	<48EC9D9F.EE98.0025.0@wicourts.gov>
	<3a0028490810081054o2100b2bci7bde6b7213712c58@mail.gmail.com>
	<48ECB0DD.EE98.0025.0@wicourts.gov>

On Wed, Oct 8, 2008 at 12:08 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>
> If the problem is checkpoints (which seems likely but far from a sure
> thing based on the information provided), aggressive background writer
> setting might be your best bet under 8.2.X.  To solve similar problems
> we had to go to the following, although many on these lists feel that
> settings this aggressive are rarely needed, so use at your own risk.
>
> #bgwriter_delay = 200ms
> bgwriter_lru_percent = 20.0
> bgwriter_lru_maxpages = 200
> bgwriter_all_percent = 10.0
> bgwriter_all_maxpages = 600

I've tuned an 8.2 server with settings similar to this, and it made a
world of difference on smoothing out performance.  Note that bgwriter
tends to use cpu and memory bandwidth up, so avoid going crazy on it.

> Even better would be to go to the latest revision of the 8.3 release,
> which at this writing is 8.3.4.  In that release PostgreSQL spreads
> out the work of a checkpoint to minimize this problem.

Seconded.  A lot of hard work went into making the bgwriter much
easier to adjust, and much less likely to even need adjusting in 8.3.