Message-Id: <46837D0E.EE98.0025.0@wicourts.gov>
Date: Thu, 28 Jun 2007 09:19:11 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Ho Fat Tsang" <namiwf@gmail.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL 8.0 occasionally slow down
References: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>
In-Reply-To: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Thu, Jun 28, 2007 at  1:54 AM, in message
<97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>, "Ho Fat =
Tsang"
<namiwf@gmail.com> wrote:=20
>=20
> I don't know why the server occasionally
> slow down a bit for every 3 minutes.
=20
If the problem is checkpoints, try making your background writer more =
aggressive.  This allows more of the pages to be written to disk before =
the checkpoint starts.  I'll show the settings which have eliminated =
similar problems for us, but your best settings will depend on hardware =
and are almost certainly going to be different.  In particular, we have a =
battery backed caching RAID controller, which seems to change the dynamics =
of these sorts of issues quite a bit.
=20
#bgwriter_delay =3D 200ms                 # 10-10000ms between rounds
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
We also adjust a couple other WAL-related settings:=20
=20
wal_buffers =3D 160kB                     # min 32kB
                                        # (change requires restart)
checkpoint_segments =3D 10                # in logfile segments, min 1, =
16MB each
=20
Since you're on 8.0 I think you'll need to specify wal-buffers as a number =
of 8KB pages.
=20
-Kevin
=20