Re: PostgreSQL 8.0 occasionally slow down

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(dot)Grittner(at)wicourts(dot)gov>:
>
> >>> On Thu, Jun 28, 2007 at 1:54 AM, in message
> <97e6e20f0706272354m5e42c91em177c8b80abb2ea52(at)mail(dot)gmail(dot)com>, "Ho Fat
> Tsang"
> <namiwf(at)gmail(dot)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
>
>
>
>