From: Ho Fat Tsang <namiwf@gmail.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 8.0 occasionally slow down
Date: Fri, 29 Jun 2007 10:04:41 +0800
Message-ID: <97e6e20f0706281904v4b7c4a97p570deda85e590ee4@mail.gmail.com> (raw)
In-Reply-To: <46837C50.20603@archonet.com>
References: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>
<468361A9.1040308@archonet.com>
<97e6e20f0706280101y543b871ao4cbf85e57f07f7e9@mail.gmail.com>
<46836B66.7050109@archonet.com>
<97e6e20f0706280117h20ab2e03n770abc588f17742@mail.gmail.com>
<46837C50.20603@archonet.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: namiwf@gmail.com, dev@archonet.com
Subject: Re: PostgreSQL 8.0 occasionally slow down
In-Reply-To: <97e6e20f0706281904v4b7c4a97p570deda85e590ee4@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox