Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0F1A29FC27A for ; Thu, 28 Jun 2007 23:04:50 -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 39216-03 for ; Thu, 28 Jun 2007 23:04:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ik-out-1112.google.com (ik-out-1112.google.com [66.249.90.180]) by postgresql.org (Postfix) with ESMTP id 6A4859FC276 for ; Thu, 28 Jun 2007 23:04:45 -0300 (ADT) Received: by ik-out-1112.google.com with SMTP id c21so738309ika for ; Thu, 28 Jun 2007 19:04:43 -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=M5BcIhzhZKK50YKHiSLYMrEsSjn/dvutjTfmjCOMyKVBswxig4Is7f6VwJflWfe5XkwPEBb/8VU1i1agQUJMa/rXhE4wvnu0HDQfdVpvtKNUdOBSZe5D6UuD6DF44v1q3rRDjxXi6hV5yRFeiooU3Ya4r1Q3E68QVKMRPUrSGr8= 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=GLsGaPdd9DjlwegbHXCS+lUksAfkB5fKV5VXl603wFqXpzrtJPQbJ6jrkL+Bp/j4Y0HsnK+e41+G/bECYaFivTiN2V2zHae8NvdxL8UQnMgb5MF+3A00VNXcSI0FCPyshFvb0vQC6PdmdIGt4tfYbQyNHewbUocMuxFKoTjVJDY= Received: by 10.142.107.1 with SMTP id f1mr152138wfc.1183082681914; Thu, 28 Jun 2007 19:04:41 -0700 (PDT) Received: by 10.143.30.13 with HTTP; Thu, 28 Jun 2007 19:04:41 -0700 (PDT) Message-ID: <97e6e20f0706281904v4b7c4a97p570deda85e590ee4@mail.gmail.com> Date: Fri, 29 Jun 2007 10:04:41 +0800 From: "Ho Fat Tsang" To: "Richard Huxton" Subject: Re: PostgreSQL 8.0 occasionally slow down Cc: pgsql-performance@postgresql.org In-Reply-To: <46837C50.20603@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_51889_21779489.1183082681888" 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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/639 X-Sequence-Number: 25672 ------=_Part_51889_21779489.1183082681888 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 : > > 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 > ------=_Part_51889_21779489.1183082681888 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_51889_21779489.1183082681888--