Re: Blocking excessively in FOR UPDATE

On Fri, Nov 4, 2011 at 3:26 PM, Shaun Thomas <sthomas(at)peak6(dot)com> wrote:
> On 11/04/2011 12:22 PM, Claudio Freire wrote:
>
>> bgwriter_delay = 1000ms
>> wal_writer_delay=2000ms
>> commit_delay=10000
>
> !?
>snip
> "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.

I think you're misinterpreting the value.
It's in microseconds, that's 10 *milli*seconds

> 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.

I don't think 1 second can be such a big difference for the bgwriter,
but I might be wrong.

The wal_writer makes me doubt, though. If logged activity was higher
than 8MB/s, then that setting would block it all.
I guess I really should lower it.

> 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.

Nice to know, I thought so, now I know so.
:-)

So... I'm leaning towards lowering wal_writer_delay and see how it goes.