Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 336609FB2DB for ; Fri, 14 Sep 2007 13:22:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 28806-01-5 for ; Fri, 14 Sep 2007 13:22:05 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id 0CA9E9FB2D6 for ; Fri, 14 Sep 2007 13:22:04 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id l8EGM3K6023964; Fri, 14 Sep 2007 12:22:03 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id l8EGM3KB023955; Fri, 14 Sep 2007 12:22:03 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Fri, 14 Sep 2007 12:22:03 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Peter Childs cc: pgsql-performance@postgresql.org Subject: Re: Long Running Commits - Not Checkpoints In-Reply-To: Message-ID: References: <1189692915.8624.22.camel@bnicholson-desktop> <1189700385.8624.32.camel@bnicholson-desktop> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/277 X-Sequence-Number: 26822 On Fri, 14 Sep 2007, Peter Childs wrote: > 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). One very common cause for transactions blocking for as much as several seconds is hitting a checkpoint, which in current versions causes a large amount of data to be written and synchronized to the physical disk. If you're already tracking long transactions and trying to figure out what's causing them, you should set checkpoint_warning to a high value (the maximum of 3600 is generally fine). That will give you a harmless warning every time a checkpoint occurs. If the slow transactions line up with checkpoints, then you might consider tuning or re-tuning the background writer to elimite the delay. In this particular case, their system had already been tuned so aggressively that I wondered if the background writer was being a problem rather than a solution. Reducing the percentage would turn it down a bit; so would *increasing* the delay--they had already decreased it considerably, making it 4X as active as the default. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD