From: Richard Huxton <dev@archonet.com>
To: Ho Fat Tsang <namiwf@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 8.0 occasionally slow down
Date: Thu, 28 Jun 2007 10:16:00 +0100
Message-ID: <46837C50.20603@archonet.com> (raw)
In-Reply-To: <97e6e20f0706280117h20ab2e03n770abc588f17742@mail.gmail.com>
References: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>
<468361A9.1040308@archonet.com>
<97e6e20f0706280101y543b871ao4cbf85e57f07f7e9@mail.gmail.com>
<46836B66.7050109@archonet.com>
<97e6e20f0706280117h20ab2e03n770abc588f17742@mail.gmail.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: dev@archonet.com, namiwf@gmail.com
Subject: Re: PostgreSQL 8.0 occasionally slow down
In-Reply-To: <46837C50.20603@archonet.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