Message-ID: <46837C50.20603@archonet.com>
Date: Thu, 28 Jun 2007 10:16:00 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Ho Fat Tsang <namiwf@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 8.0 occasionally slow down
References: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>	
	<468361A9.1040308@archonet.com>	
	<97e6e20f0706280101y543b871ao4cbf85e57f07f7e9@mail.gmail.com>	
	<46836B66.7050109@archonet.com>
	<97e6e20f0706280117h20ab2e03n770abc588f17742@mail.gmail.com>
In-Reply-To: <97e6e20f0706280117h20ab2e03n770abc588f17742@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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