Re: Blocking excessively in FOR UPDATE

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(at)peak6(dot)com

______________________________________________

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