From: Ho Fat Tsang <namiwf@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 8.0 occasionally slow down
Date: Wed, 4 Jul 2007 01:07:55 +0800
Message-ID: <97e6e20f0707031007p20dc6c4bl4a90bcaab493ffb@mail.gmail.com> (raw)
In-Reply-To: <46837D0E.EE98.0025.0@wicourts.gov>
References: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>
<46837D0E.EE98.0025.0@wicourts.gov>
Hi Kevin,
Thank for your configuration. I have tested with this configuration
(amended a bit) and it helps a bit. But i have found the root cause is
related to the application that using PG.
But yet i can learn much to tune the PG in my restricted environment !.
Regards,
Twinsen
2007/6/28, Kevin Grittner <Kevin.Grittner@wicourts.gov>:
>
> >>> 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: namiwf@gmail.com, Kevin.Grittner@wicourts.gov
Subject: Re: PostgreSQL 8.0 occasionally slow down
In-Reply-To: <97e6e20f0707031007p20dc6c4bl4a90bcaab493ffb@mail.gmail.com>
* 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