Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 04CB337C709 for ; Thu, 9 Oct 2008 12:03:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 91971-10 for ; Thu, 9 Oct 2008 12:03:00 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from gv-out-0910.google.com (gv-out-0910.google.com [216.239.58.186]) by postgresql.org (Postfix) with ESMTP id 816F537BFA6 for ; Thu, 9 Oct 2008 12:02:59 -0300 (ADT) Received: by gv-out-0910.google.com with SMTP id i36so31748gve.7 for ; Thu, 09 Oct 2008 08:02:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=r+gpf7J1DAROLnF1xvPX0wKTq+qUmD6d3QCO759YsBA=; b=OKE70dafLb/CUm8LKd8ugd1CXLmOSufZQjjeUJYgmOVBsxzFi8DERXOnxMGOOhTG3n gYh0amKiwMJgJ8FGzRiFvQhowynCL7SovOFQmwr/WphiGqe+p9kvFndtgRnFeqAzxcRP RRUQOZxphBgr+3H6TGHK9u5g+E9NtqQ28eGGw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=H6v0XhJmKvGH+WugpUsKAiD7UOP5QmBuRh47u6T52lWzeSzWSjf+RvW6cb+2JtC12v vGsd+xcs0XE+GZwEb+IDz0VTTmCkrRm10YWxXl0s7QlXhWSGv7wC3b3BjTZtc/+iAsOH CdtafRLgRHhF3JFVKrjwo/I6AjxFqfJkM8yPg= Received: by 10.86.82.6 with SMTP id f6mr299573fgb.78.1223564578039; Thu, 09 Oct 2008 08:02:58 -0700 (PDT) Received: by 10.86.71.17 with HTTP; Thu, 9 Oct 2008 08:02:57 -0700 (PDT) Message-ID: <3a0028490810090802t9294abeod6e5ece092a487e4@mail.gmail.com> Date: Thu, 9 Oct 2008 13:02:57 -0200 From: "Rafael Domiciano" To: "Scott Marlowe" Subject: Re: checkpoint_timeout Cc: "Kevin Grittner" , "Julio Leyva" , "pgsql-admin@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11461_2508224.1223564578035" References: <3a0028490810071321j32b4f450m95123d02b9fe6f49@mail.gmail.com> <3a0028490810080734r26912c72n94d6f509dfb55b6e@mail.gmail.com> <3a0028490810080919v3762f924je688a56210558e95@mail.gmail.com> <3a0028490810080934l211d137uc9dde69cad7879d9@mail.gmail.com> <48EC9D9F.EE98.0025.0@wicourts.gov> <3a0028490810081054o2100b2bci7bde6b7213712c58@mail.gmail.com> <48ECB0DD.EE98.0025.0@wicourts.gov> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200810/68 X-Sequence-Number: 30524 ------=_Part_11461_2508224.1223564578035 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi there. I increased the wal_buffers and no change made. I'm beggining to think that my problem is not related to "checkpoint & cia" as I said early. I have decreased the checkpoint_timeout to 3 min and the slow down continuin' ocorring after each 5 min. In this "slowdown" the postgres does nothing, it's simply stop doing the operations. The softwares still waiting for the response. This is a dedicated server, the only program another postgres that runs is Slony-I 2008/10/8 Scott Marlowe > On Wed, Oct 8, 2008 at 12:08 PM, Kevin Grittner > wrote: > > > > If the problem is checkpoints (which seems likely but far from a sure > > thing based on the information provided), aggressive background writer > > setting might be your best bet under 8.2.X. To solve similar problems > > we had to go to the following, although many on these lists feel that > > settings this aggressive are rarely needed, so use at your own risk. > > > > #bgwriter_delay = 200ms > > bgwriter_lru_percent = 20.0 > > bgwriter_lru_maxpages = 200 > > bgwriter_all_percent = 10.0 > > bgwriter_all_maxpages = 600 > > I've tuned an 8.2 server with settings similar to this, and it made a > world of difference on smoothing out performance. Note that bgwriter > tends to use cpu and memory bandwidth up, so avoid going crazy on it. > > > Even better would be to go to the latest revision of the 8.3 release, > > which at this writing is 8.3.4. In that release PostgreSQL spreads > > out the work of a checkpoint to minimize this problem. > > Seconded. A lot of hard work went into making the bgwriter much > easier to adjust, and much less likely to even need adjusting in 8.3. > ------=_Part_11461_2508224.1223564578035 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi there.

I increased the wal_buffers and no change made.
I'm beggining to think that my problem is not related to "checkpoint & cia" as I said early.
I have decreased the checkpoint_timeout to 3 min and the slow down continuin' ocorring after each 5 min.

In this "slowdown" the postgres does nothing, it's simply stop doing the operations. The softwares still waiting for the response.
This is a dedicated server, the only program another postgres that runs is Slony-I


2008/10/8 Scott Marlowe <scott.marlowe@gmail.com>
On Wed, Oct 8, 2008 at 12:08 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>
> If the problem is checkpoints (which seems likely but far from a sure
> thing based on the information provided), aggressive background writer
> setting might be your best bet under 8.2.X.  To solve similar problems
> we had to go to the following, although many on these lists feel that
> settings this aggressive are rarely needed, so use at your own risk.
>
> #bgwriter_delay = 200ms
> bgwriter_lru_percent = 20.0
> bgwriter_lru_maxpages = 200
> bgwriter_all_percent = 10.0
> bgwriter_all_maxpages = 600

I've tuned an 8.2 server with settings similar to this, and it made a
world of difference on smoothing out performance.  Note that bgwriter
tends to use cpu and memory bandwidth up, so avoid going crazy on it.

> Even better would be to go to the latest revision of the 8.3 release,
> which at this writing is 8.3.4.  In that release PostgreSQL spreads
> out the work of a checkpoint to minimize this problem.

Seconded.  A lot of hard work went into making the bgwriter much
easier to adjust, and much less likely to even need adjusting in 8.3.

------=_Part_11461_2508224.1223564578035--