Re: PostgreSQL 8.0 occasionally slow down

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