Re: PostgreSQL 8.0 occasionally slow down

Hi Richard,

I've tested again according your suggestion. I noticed that for each
time the pgsql slow down, there is a short period a process called "pdflush"
eating up lot of I/O. I've goolgled and know it is a process for writing
dirty pages back to the disk by the Linux kernel. I will have further
investigation on this process with my limited knowledge on Linux kernel.

Correct me if i am wrong. It seems postgresql 8.0 does not bundle
auto-vacuum by default. So all vacuum and analyse are done manually ? So
what i have tested related to vaccuum is running auto-vacuum (a executeable
located in /bin) parallel under normal production load but it seems won't
help.

Thanks for help.
Twinsen

2007/6/28, Richard Huxton <dev(at)archonet(dot)com>:
>
> Ho Fat Tsang wrote:
> > Hi Richard,
> >
> > Thank for your prompt reply. I have used the command "vmstat 10" to
> > investigate the I/O issue and listed below :
> >
> > procs -----------memory---------- ---swap-- -----io---- --system--
> > ----cpu----
> > r b swpd free buff cache si so bi bo in cs us sy
> id
> > wa
> > 0 0 26848 8376 2208 595796 0 0 16 16 14 13 5 2
> 91
> > 2
> [etc]
> > 1 0 26936 8476 2008 596148 0 0 1237 660 1618 1863 34 6
> 50
> > 11 <-- The starting time when the pgsql log transaction due to long
> > execution duration.
> > 0 0 26936 8024 1980 596756 0 0 1983 228 1985 2241 52 8
> 31
> > 10
> > 0 2 26936 8312 2040 595904 0 0 405 16674 1449 1675
> 17 6 1
> > 76 <-- The intermediate time reaching I/O peak.
> [etc]
> > As you said, it seems for each 3~4 minutes, there is a I/O peak. But
> > what is
> > the problem indicating by it ?
>
> It's a burst of writing too (bo=blocks out for those who aren't familiar
> with vmstat).
>
> Well, there are four possibilities:
> 1. Something outside of PostgreSQL
> 2. An increase in update queries
> 3. Checkpoints
> 4. Vacuum
>
> If you keep an eye on "top" at the same time as vmstat, that should show
> whether it is another process.
>
> You would have mentioned if this co-incided with more queries, so we can
> probably rule that out.
>
> You've changed checkpointing timeouts and that's not affected this.
>
> We can see if it's autovacuum by disabling it in postgresql.conf and
> restarting PG. Try that and see if it alters things.
>
> It might be you need to vacuum more often (so you do less on each run)
> or it might be you need more/faster disks to keep up with your update
> activity.
>
> --
> Richard Huxton
> Archonet Ltd
>