Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 482249F9BDD for ; Fri, 14 Sep 2007 04:43:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 64182-02-3 for ; Fri, 14 Sep 2007 04:42:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id 0DCAF9F9C0B for ; Fri, 14 Sep 2007 04:43:00 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id f5so545179nfh for ; Fri, 14 Sep 2007 00:42:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; bh=8oi6jFOPU56OI+Pn5PO6M+vydtW8HbYlOzZW1rvaicE=; b=fhIM4G8RCQ8bbgelGwZO5OFUnSA5HFUQxqeKuOfpez4V3mLGLIBJgEUCLfZsCKJPgy7J95CuZVVK289SfLWGc4v00wqmzFw13QxKEqCgJ6qTchaskr0DBrRCL9gsHAV3tkpIEHgZS+f1OtHBdMpO8jDlLTintF0DTGyPTKfUI18= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=El3DU5yqlxiJOh9KiGHGyRz1IujqncFIltvYPlEt4jW3GTHA6Kim0Sxbkh5j1KEBE3a3R5GdUNDqEbf2CQpPS8jO7hoWjlU4g1VIpg4SAjSzMB4v1CAlA6VvSOQ1pxQ9pEygzI85gp9pgKGkqyipEeH3uE2zLlN5EYWEPGjcuYI= Received: by 10.78.168.1 with SMTP id q1mr792862hue.1189755779432; Fri, 14 Sep 2007 00:42:59 -0700 (PDT) Received: by 10.78.146.6 with HTTP; Fri, 14 Sep 2007 00:42:59 -0700 (PDT) Message-ID: Date: Fri, 14 Sep 2007 08:42:59 +0100 From: "Peter Childs" Subject: Re: Long Running Commits - Not Checkpoints Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_842_1530027.1189755779423" References: <1189692915.8624.22.camel@bnicholson-desktop> <1189700385.8624.32.camel@bnicholson-desktop> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/270 X-Sequence-Number: 26815 ------=_Part_842_1530027.1189755779423 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 14/09/2007, Peter Childs wrote: > > > > On 13/09/2007, Greg Smith wrote: > > > > > > Every time the all scan writes a buffer that is frequently used, that > > write has a good chance that it was wasted because the block will be > > modified again before checkpoint time. Your settings are beyond regular > > > > aggressive and into the hyperactive terrority where I'd expect such > > redundant writes are happening often. I'd suggest you try to move > > toward > > dropping bgwriter_all_percent dramatically from its current setting and > > see how far down you can go before it starts to introduce blocks at > > checkpoint time. With bgwriter_delay set to 1/4 the default, I would > > expect that even 5% would be a high setting for you. That may be a more > > dramatic change than you want to make at once though, so lowering it in > > that direction more slowly (perhaps drop 5% each day) and seeing whether > > things improve as that happens may make more sense. > > > > > Are you suggesting that reducing bgwriter_delay and bg_writer_percent > would reduce the time spent doing commits? > > I get quite a few commits that take over 500ms (the point when i start > logging queries). I always thought oh just one of those things but if they > can be reduced by changing a few config variables that would be great. I'm > just trying to workout what figures are worth trying to see if I can reduce > them. > > From time to time I get commits that take 6 or 7 seconds but not all the > time. > > I'm currently working with the defaults. > > Peter Childs > Hmm Always read the manual, Increase them from the defaults....... Peter. ------=_Part_842_1530027.1189755779423 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 14/09/2007, Peter Childs <peterachilds@gmail.com> wrote:


On 13/09/2007, Greg Smith < gsmith@gregsmith.com> wrote:

Every time the all scan writes a buffer that is frequently used, that
write has a good chance that it was wasted because the block will be
modified again before checkpoint time.  Your settings are beyond regular
aggressive and into the hyperactive terrority where I'd expect such
redundant writes are happening often.  I'd suggest you try to move toward
dropping bgwriter_all_percent dramatically from its current setting and
see how far down you can go before it starts to introduce blocks at
checkpoint time.  With bgwriter_delay set to 1/4 the default, I would
expect that even 5% would be a high setting for you.  That may be a more
dramatic change than you want to make at once though, so lowering it in
that direction more slowly (perhaps drop 5% each day) and seeing whether
things improve as that happens may make more sense.

Are you suggesting that reducing bgwriter_delay and bg_writer_percent would reduce the time spent doing commits?

I get quite a few commits that take over 500ms (the point when i start logging queries). I always thought oh just one of those things but if they can be reduced by changing a few config variables that would be great. I'm just trying to workout what figures are worth trying to see if I can reduce them.

From time to time I get commits that take 6 or 7 seconds but not all the time.

I'm currently working with the defaults.

Peter Childs

Hmm Always read the manual, Increase them from the defaults.......

Peter.
------=_Part_842_1530027.1189755779423--