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@archonet.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
