Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 823CB9FB39A for ; Tue, 3 Jul 2007 14:08:05 -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 86478-08 for ; Tue, 3 Jul 2007 14:07:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.229]) by postgresql.org (Postfix) with ESMTP id E3FC39FB57B for ; Tue, 3 Jul 2007 14:07:56 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 68so1223592wri for ; Tue, 03 Jul 2007 10:07:56 -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=cwHq/+RRETLzbBq7kOMp+lYGgZKU0DngfouSm7/1MqL5ISjt0Cw+7XIMG2qbIkT9fiyg/RJDc44oz/V0a2N6W10Evp6AlQowxT0BmePYs1jkPCmCKNrLXBZSmez82P6v3lnx4vdmOZA1cnN+i3bIIPw1iyItTkCmR2yo4Zf1r4U= 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=t4Rnzp8gVW79w/NdxiqaGO72kYcnzl/g80w/CeEIKhcst2bzHXQei/9poxJEeYBxszbGuo45+CB9Ktm95c9OLpI271yriglytVMVZawT4h4l+GZ5RsUCmwybvimqjkpWj2/Ti/3yw0nmG+RvYibJV3e5TC22qgDQwwyU+kK5A40= Received: by 10.142.77.11 with SMTP id z11mr448125wfa.1183482475581; Tue, 03 Jul 2007 10:07:55 -0700 (PDT) Received: by 10.143.30.13 with HTTP; Tue, 3 Jul 2007 10:07:55 -0700 (PDT) Message-ID: <97e6e20f0707031007p20dc6c4bl4a90bcaab493ffb@mail.gmail.com> Date: Wed, 4 Jul 2007 01:07:55 +0800 From: "Ho Fat Tsang" To: "Kevin Grittner" Subject: Re: PostgreSQL 8.0 occasionally slow down Cc: pgsql-performance@postgresql.org In-Reply-To: <46837D0E.EE98.0025.0@wicourts.gov> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_93737_18994858.1183482475543" References: <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com> <46837D0E.EE98.0025.0@wicourts.gov> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/14 X-Sequence-Number: 25698 ------=_Part_93737_18994858.1183482475543 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Kevin, Thank for your configuration. I have tested with this configuration (amended a bit) and it helps a bit. But i have found the root cause is related to the application that using PG. But yet i can learn much to tune the PG in my restricted environment !. Regards, Twinsen 2007/6/28, Kevin Grittner : > > >>> On Thu, Jun 28, 2007 at 1:54 AM, in message > <97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>, "Ho Fat > Tsang" > wrote: > > > > I don't know why the server occasionally > > slow down a bit for every 3 minutes. > > If the problem is checkpoints, try making your background writer more > aggressive. This allows more of the pages to be written to disk before the > checkpoint starts. I'll show the settings which have eliminated similar > problems for us, but your best settings will depend on hardware and are > almost certainly going to be different. In particular, we have a battery > backed caching RAID controller, which seems to change the dynamics of these > sorts of issues quite a bit. > > #bgwriter_delay = 200ms # 10-10000ms between rounds > bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers > scanned/round > bgwriter_lru_maxpages = 200 # 0-1000 buffers max written/round > bgwriter_all_percent = 10.0 # 0-100% of all buffers > scanned/round > bgwriter_all_maxpages = 600 # 0-1000 buffers max written/round > > We also adjust a couple other WAL-related settings: > > wal_buffers = 160kB # min 32kB > # (change requires restart) > checkpoint_segments = 10 # in logfile segments, min 1, 16MB > each > > Since you're on 8.0 I think you'll need to specify wal-buffers as a number > of 8KB pages. > > -Kevin > > > > ------=_Part_93737_18994858.1183482475543 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Kevin,

    Thank for your configuration. I have tested with this configuration (amended a bit) and it helps a bit. But i have found the root cause is related to the application that using PG.
    But yet i can learn much to tune the PG in my restricted environment !.

Regards,
Twinsen

2007/6/28, Kevin Grittner <Kevin.Grittner@wicourts.gov>:
>>> On Thu, Jun 28, 2007 at  1:54 AM, in message
<97e6e20f0706272354m5e42c91em177c8b80abb2ea52@mail.gmail.com>, "Ho Fat Tsang"
<namiwf@gmail.com> wrote:
>
> I don't know why the server occasionally
> slow down a bit for every 3 minutes.

If the problem is checkpoints, try making your background writer more aggressive.  This allows more of the pages to be written to disk before the checkpoint starts.  I'll show the settings which have eliminated similar problems for us, but your best settings will depend on hardware and are almost certainly going to be different.  In particular, we have a battery backed caching RAID controller, which seems to change the dynamics of these sorts of issues quite a bit.

#bgwriter_delay = 200ms                 # 10-10000ms between rounds
bgwriter_lru_percent = 20.0             # 0-100% of LRU buffers scanned/round
bgwriter_lru_maxpages = 200             # 0-1000 buffers max written/round
bgwriter_all_percent = 10.0             # 0-100% of all buffers scanned/round
bgwriter_all_maxpages = 600             # 0-1000 buffers max written/round

We also adjust a couple other WAL-related settings:

wal_buffers = 160kB                     # min 32kB
                                        # (change requires restart)
checkpoint_segments = 10                # in logfile segments, min 1, 16MB each

Since you're on 8.0 I think you'll need to specify wal-buffers as a number of 8KB pages.

-Kevin




------=_Part_93737_18994858.1183482475543--