From: Shaun Thomas <sthomas@peak6.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Blocking excessively in FOR UPDATE
Date: Fri, 4 Nov 2011 13:26:39 -0500
Message-ID: <4EB42E5F.5080502@peak6.com> (raw)
In-Reply-To: <CAGTBQpYU069d+WWw5GUgBD_oAYiONR-BXC_YOeUVwBm+wD-BVg@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@peak6.com, klaussfreire@gmail.com, Kevin.Grittner@wicourts.gov, tgl@sss.pgh.pa.us
Subject: Re: Blocking excessively in FOR UPDATE
In-Reply-To: <4EB42E5F.5080502@peak6.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox