From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Ho Fat Tsang <namiwf@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 8.0 occasionally slow down
Date: Thu, 28 Jun 2007 09:19:11 -0500
Message-ID: <46837D0E.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>
References: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>
>>> On Thu, Jun 28, 2007 at 1:54 AM, in message
<97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>, "Ho Fat Tsang"
<namiwf@gmail.com> wrote:
>
> I don't know why the server occasionally
> slow down a bit for every 3 minutes.
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.
#bgwriter_delay = 200ms # 10-10000ms between rounds
bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers scanned/round
bgwriter_lru_maxpages = 200 # 0-1000 buffers max written/round
bgwriter_all_percent = 10.0 # 0-100% of all buffers scanned/round
bgwriter_all_maxpages = 600 # 0-1000 buffers max written/round
We also adjust a couple other WAL-related settings:
wal_buffers = 160kB # min 32kB
# (change requires restart)
checkpoint_segments = 10 # in logfile segments, min 1, 16MB each
Since you're on 8.0 I think you'll need to specify wal-buffers as a number of 8KB pages.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, namiwf@gmail.com
Subject: Re: PostgreSQL 8.0 occasionally slow down
In-Reply-To: <46837D0E.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox