Message-Id: <46DC0CDD.EE98.0025.0@wicourts.gov>
Date: Mon, 03 Sep 2007 13:32:13 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-performance@postgresql.org>,
	"Shawn" <postgres@xmtservices.net>
Subject: Re: Slow Query
References: <20070901102947.0c0a50a3@boffin.xmtservices.net>
	<22800.1188670194@sss.pgh.pa.us>
	<20070901131816.3f7ef91a@boffin.xmtservices.net>
	<20070901173519.0ed11401@boffin.xmtservices.net>
	<25184.1188702010@sss.pgh.pa.us>
	<20070903095749.5045211b@boffin.xmtservices.net>
In-Reply-To: <20070903095749.5045211b@boffin.xmtservices.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Mon, Sep 3, 2007 at 11:57 AM, in message
<20070903095749.5045211b@boffin.xmtservices.net>, Shawn
<postgres@xmtservices.net> wrote:=20
> Also it runs a lot faster by itself
=20
Given the context of the run, there is a possibility that a checkpoint =
tends
to fall at this point in the script because you're filling your WAL files.
There is a known issue (which people have been working on fixing in 8.3)
which causes the checkpoint to push a lot of pages to the disk and then =
wait
on physical writes.  If the VACUUM ANALYZE doesn't turn up anything =
useful,
an interesting experiment would be to run your script with these
modifications to your postgresql.conf file:
=20
bgwriter_lru_percent =3D 20.0             # 0-100% of LRU buffers =
scanned/round
bgwriter_lru_maxpages =3D 200             # 0-1000 buffers max written/roun=
d
bgwriter_all_percent =3D 10.0             # 0-100% of all buffers =
scanned/round
bgwriter_all_maxpages =3D 600             # 0-1000 buffers max written/roun=
d
=20
Don't leave these in effect permanently without close attention to the
overall impact.  These settings have worked well for us, but are likely
not to work well for everyone.
=20
-Kevin
=20