Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D5E209FA37A for ; Tue, 3 Jul 2007 14:14:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 29283-08 for ; Tue, 3 Jul 2007 14:14:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.234]) by postgresql.org (Postfix) with ESMTP id 7F92A9FB324 for ; Tue, 3 Jul 2007 14:14:07 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 68so1225387wri for ; Tue, 03 Jul 2007 10:14:06 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=fj0xUxjCGhTNC7+4vMkIUPkUIAbNhKNsN0YYj45g/u+WdFVH19iOHxc7mFV/vcf9AU2qsOvkhJJMcm1T3NX1+HgPn9OAdBh27LKPWbuvMfshvrgVJ7AJAgJgZCUwqf1epXsr6ysVmZUg34a3liOBi8hDLDkKywnCoRB87l/6GZo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Th2Y1x3Ffvcbhyadh6U/UQeyU1tX1Mtf/kb0WnkG3M/Git4WX+f3kSS4gTPHBiHdYqDux5Vv1IgbOuxLQbnCTjGcUZpTCzlC+D3fa6sSzZ1UNVM8MggCYFjjfjn9qIIX2GtCkW/YGgNR0rdH3M7xX6L29cZudh9CiCVW4+FJbG0= Received: by 10.142.80.7 with SMTP id d7mr449092wfb.1183482845678; Tue, 03 Jul 2007 10:14:05 -0700 (PDT) Received: by 10.143.30.13 with HTTP; Tue, 3 Jul 2007 10:14:05 -0700 (PDT) Message-ID: <97e6e20f0707031014y4146f7a7p4016df43210653ea@mail.gmail.com> Date: Wed, 4 Jul 2007 01:14:05 +0800 From: "Ho Fat Tsang" To: "Richard Huxton" Subject: Re: PostgreSQL 8.0 occasionally slow down Cc: pgsql-performance@postgresql.org In-Reply-To: <4684CF12.5010003@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_93853_3342123.1183482845595" 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> <4684CF12.5010003@archonet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/15 X-Sequence-Number: 25699 ------=_Part_93853_3342123.1183482845595 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 2007/6/29, Richard Huxton : > > 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. Exactly ! you are right, finally i have found that the root cause for this is the application that use PG. There is memory leak using MappedByteBuffer (writing in java), it leads high I/O loading and finally reaches the ratio that pdflush is being kicked start in the kernel. Thank you for helping a lot in digging out this issue ! learn much for you guys ! > 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 > ------=_Part_93853_3342123.1183482845595 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 2007/6/29, Richard Huxton <dev@archonet.com>:
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.

Exactly ! you are right, finally i have found that the root cause for this is the application that use PG. There is memory leak using MappedByteBuffer (writing in java), it leads high I/O loading and finally reaches the ratio that pdflush is being kicked start in the kernel.

Thank you for helping a lot in digging out this issue ! learn much for you guys !

>   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

------=_Part_93853_3342123.1183482845595--