Re: PostgreSQL 8.0 occasionally slow down

>>> 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
writtento disk before the checkpoint starts.  I'll show the settings which have eliminated similar problems for us, but
yourbest settings will depend on hardware and are almost certainly going to be different.  In particular, we have a
batterybacked 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