Message-ID: <4EB42E5F.5080502@peak6.com>
Date: Fri, 4 Nov 2011 13:26:39 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: Claudio Freire <klaussfreire@gmail.com>
CC: Kevin Grittner <Kevin.Grittner@wicourts.gov>, postgres performance list
	<pgsql-performance@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Blocking excessively in FOR UPDATE
References: 
 <CAGTBQpY7zd9h+xv4Gvh0+-G1UgiWg1zd7zUsXPDFvQoGa4JScA@mail.gmail.com>
	<6734.1320345936@sss.pgh.pa.us>
	<CAGTBQpY+O4m9Tyqo=6En7+r-1qdMws5O_rk9MbEo-HRxAJsyMw@mail.gmail.com>
	<CAGTBQpYK6-ycWr0WXRwz39Bv30sCbDe68CC3MbhUp9-jwu_V=g@mail.gmail.com>
	<19908.1320363946@sss.pgh.pa.us>
	<CAGTBQpa__c1djJ6FE0k03944tBOkyz7OvAZU3Z6JagEB=PmbjA@mail.gmail.com>
	<4EB3CC010200002500042A53@gw.wicourts.gov>
	<CAGTBQpZHpTbvic4M=+mVUphoJuLTwzDVNM7NQjO_10oNOEwiXg@mail.gmail.com>
	<4EB3D5950200002500042A60@gw.wicourts.gov>
	<CAGTBQpYU069d+WWw5GUgBD_oAYiONR-BXC_YOeUVwBm+wD-BVg@mail.gmail.com>
In-Reply-To: 
 <CAGTBQpYU069d+WWw5GUgBD_oAYiONR-BXC_YOeUVwBm+wD-BVg@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 11/04/2011 12:22 PM, Claudio Freire wrote:

> bgwriter_delay = 1000ms
> wal_writer_delay=2000ms
> commit_delay=10000

!?

Maybe someone can back me up on this, but my interpretation of these 
settings suggests they're *way* too high. That commit_delay especially 
makes me want to cry. From the manual:

"Setting commit_delay can only help when there are many concurrently 
committing transactions, and it is difficult to tune it to a value that 
actually helps rather than hurt throughput."

Meaning it may halt all of your commits up to *ten seconds* if it 
doesn't think there was enough activity to warrant a write. Ouch.

Your bgwriter_delay and wal_writer_delay settings are equally odd. 
You've made the background writer so passive that when it does finally 
run, it's going to have a ton of work to do, causing giant write spikes. 
I'm not sure whether or not this also causes compounding problems with 
locks and backend write delays.

> checkpoint complete: wrote 589 buffers (3.6%); 0 transaction log
> file(s) added, 0 removed, 8 recycled; write=590.325 s, sync=0.055 s,
> total=590.417 s
>
> 590s seems an awful lot for 589 buffers.

You're misinterpreting this. The checkpoint completion target is 
multiplied against your checkpoint timeout. 590 seconds is roughly ten 
minutes, and for 589 buffers, it wrote one per second. That's about as 
slow as it can possibly write that many buffers. It had *up to* 24 
minutes, and if it had more buffers available to write, it would have 
written them. The number you really care about is "sync=0.055 s" which 
is how much time the controller spent syncing that data to disk.

If you're having real problems writing or lock delays due to IO stalls, 
you'll see that sync parameter shoot way up. This can also be elevated 
in certain NVRAM-based solutions. Once you start seeing whole seconds, 
or minutes, it might actually matter.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email