Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id CE7E59F9B7F for ; Fri, 14 Sep 2007 04:02:28 -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 63467-09 for ; Fri, 14 Sep 2007 04:02:23 -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.184]) by postgresql.org (Postfix) with ESMTP id 398D89F9AA8 for ; Fri, 14 Sep 2007 04:02:25 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id f5so539350nfh for ; Fri, 14 Sep 2007 00:02:24 -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=yBFxLn6Cj7lc6dvr7JkKRI5M3vLA3fZqz1SgT8uoXAc=; b=cGzmJ5VeV9/1nwtPyYUTowwvNcewnhyRy/L3Lj0fG8zSfG48IRkhaC59M3ESg8pkQ5baeiqvy6JBpMMrlzYUdJzEcL5nq0NAOiq7fWYPmtdfysJoYXBJIjHJN4CdeJ2n0wvKvckxfzcNlI+qtEwh2RVOqgbDGNP2b0RC8y0T+vs= 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=MZ6vck4z8d7dYoL/JmRq7VLg3XVTKgFR62Ecdfh3rx+kDONctYkNT1AcCnzZiGg585uWreN6w7wCB+3qakelw9AM6Uvz/R24t6VA1eZykziv7PIrrlmqQ3C6XBzWfACUc+aJLwwWc7PLuU1Zqlqr3IiyjcHuKXRMLO8ksTG7AnE= Received: by 10.78.142.14 with SMTP id p14mr801812hud.1189753343723; Fri, 14 Sep 2007 00:02:23 -0700 (PDT) Received: by 10.78.146.6 with HTTP; Fri, 14 Sep 2007 00:02:23 -0700 (PDT) Message-ID: Date: Fri, 14 Sep 2007 08:02:23 +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_666_8082930.1189753343717" 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/268 X-Sequence-Number: 26813 ------=_Part_666_8082930.1189753343717 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.