Message-ID: <4684CF12.5010003@archonet.com>
Date: Fri, 29 Jun 2007 10:21:22 +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>	
	<46837C50.20603@archonet.com>
	<97e6e20f0706281904v4b7c4a97p570deda85e590ee4@mail.gmail.com>
In-Reply-To: <97e6e20f0706281904v4b7c4a97p570deda85e590ee4@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ho Fat Tsang wrote:
> 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.

Well, pdflush is responsible for flushing dirty pages to disk on behalf 
of all processes.

If it's doing it every 3 minutes while checkpoints are happening every 
30 seconds then I don't see how it's PG that's responsible.

There are three possibilities:
1. PG isn't actually checkpointing every 30 seconds.
2. There is a burst of query activity every 3 minutes that causes a lot 
of writing.
3. Some other process is responsible.


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

Can't remember whether 8.0 had autovacuum bundled and turned off or not 
bundled at all. If it's not running it can't be causing this problem though.

-- 
   Richard Huxton
   Archonet Ltd