Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3E8549FC0A4 for ; Thu, 28 Jun 2007 05:17:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 91573-01 for ; Thu, 28 Jun 2007 05:17:27 -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.235]) by postgresql.org (Postfix) with ESMTP id 638599FC066 for ; Thu, 28 Jun 2007 05:17:30 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 68so371235wri for ; Thu, 28 Jun 2007 01:17:29 -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=l9QWIl6TBEvDQf/VgrQ9kiq6i+LS+FMD3rGe38wkOvhSqA2HTW+7Ncd31Co+Eo5fw3Do7RjXyaqg1SwUfFtz1uXGdE2pn8L2y7+y6q3BCs+6h2C5gde0+GPmK/jG/gtVxzm8N3OcqioFdGdG5UwbS5vUkvoWd25f2FzRN0hUHzo= 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=R7uYMPKeVXl4hlSnhqbqNvV9N0UbNYoHz41Z7s56fsICEroah/OWA2jn0GX3h3jUXrvT/Kj4VDod2WBRWEmczg66V9FvqpF17eY5dxzca4rHZ3srlUT5MFhgGu/Gn2hMKulJNy6kue3x+SRS+uyEqL7CHjL5cdOH4NydtFPRK9U= Received: by 10.142.255.14 with SMTP id c14mr91906wfi.1183018648933; Thu, 28 Jun 2007 01:17:28 -0700 (PDT) Received: by 10.143.30.13 with HTTP; Thu, 28 Jun 2007 01:17:28 -0700 (PDT) Message-ID: <97e6e20f0706280117h20ab2e03n770abc588f17742@mail.gmail.com> Date: Thu, 28 Jun 2007 16:17:28 +0800 From: "Ho Fat Tsang" To: "Richard Huxton" Subject: Re: PostgreSQL 8.0 occasionally slow down Cc: pgsql-performance@postgresql.org In-Reply-To: <46836B66.7050109@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_38685_11098885.1183018648902" References: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com> <468361A9.1040308@archonet.com> <97e6e20f0706280101y543b871ao4cbf85e57f07f7e9@mail.gmail.com> <46836B66.7050109@archonet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/626 X-Sequence-Number: 25659 ------=_Part_38685_11098885.1183018648902 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 1 0 26848 8024 2128 596324 0 0 1595 620 2006 3489 45 7 39 9 2 0 26848 8432 2024 595988 0 0 1399 163 1953 3830 38 8 47 7 2 0 26936 8488 2008 596092 0 0 1696 636 1973 7423 52 8 31 9 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. 0 0 26936 8544 2088 594964 0 0 1191 8295 680 1038 30 4 13 53 2 0 26936 8368 2124 595032 0 0 517 935 866 985 14 3 79 4 0 0 26936 8368 2064 595228 0 0 1706 190 1979 2356 45 7 38 9 0 0 26936 8196 2132 595452 0 0 1713 642 1913 2238 44 8 37 11 1 1 26936 8164 2168 595512 0 0 1652 666 2011 2542 45 7 38 10 0 1 26936 8840 2160 594592 0 0 1524 228 1846 2116 42 8 43 7 0 0 26936 7384 2200 596304 0 0 1584 604 1972 2137 41 7 40 11 As you said, it seems for each 3~4 minutes, there is a I/O peak. But what is the problem indicating by it ? Thanks for help. Twinsen 2007/6/28, Richard Huxton : > > Ho Fat Tsang wrote: > > Hi Richard, > > > > I have tuned the checkpoint_timeout to 30 second which is ten times > less > > than default and the issue is still reproduced. Do you have any > recommended > > configuration for WAL ? > > If you look at the output of "vmstat 10" and "iostat -m 10" (I'm > assuming you're on Linux) does it show your I/O peaking every three > minutes? I might have been wrong about the cause. > > -- > Richard Huxton > Archonet Ltd > ------=_Part_38685_11098885.1183018648902 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
 1  0  26848   8024   2128 596324    0    0  1595   620 2006  3489 45  7 39  9
 2  0  26848   8432   2024 595988    0    0  1399   163 1953  3830 38  8 47  7
 2  0  26936   8488   2008 596092    0    0  1696   636 1973  7423 52  8 31  9
 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.
 0  0  26936   8544   2088 594964    0    0  1191  8295  680  1038 30  4 13 53
 2  0  26936   8368   2124 595032    0    0   517   935  866   985 14  3 79  4
 0  0  26936   8368   2064 595228    0    0  1706   190 1979  2356 45  7 38  9
 0  0  26936   8196   2132 595452    0    0  1713   642 1913  2238 44  8 37 11
 1  1  26936   8164   2168 595512    0    0  1652   666 2011  2542 45  7 38 10
 0  1  26936   8840   2160 594592    0    0  1524   228 1846  2116 42  8 43  7
 0  0  26936   7384   2200 596304    0    0  1584   604 1972  2137 41  7 40 11

As you said, it seems for each 3~4 minutes, there is a I/O peak. But what is the problem indicating by it ?

Thanks for help.
Twinsen

2007/6/28, Richard Huxton < dev@archonet.com>:
Ho Fat Tsang wrote:
> Hi Richard,
>
>   I have tuned the checkpoint_timeout to 30 second which is ten times less
> than default and the issue is still reproduced. Do you have any recommended
> configuration for WAL ?

If you look at the output of "vmstat 10" and "iostat -m 10" (I'm
assuming you're on Linux) does it show your I/O peaking every three
minutes? I might have been wrong about the cause.

--
   Richard Huxton
   Archonet Ltd

------=_Part_38685_11098885.1183018648902--