Re: Blocking excessively in FOR UPDATE

On Fri, Nov 4, 2011 at 2:07 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Before anything else, you might want to make sure you've spread your
> checkpoint activity as much as possible by setting
> checkpoint_completion_target = 0.9.

We have

shared_buffers = 2G
bgwriter_delay = 1000ms
effective_io_concurrency=8
synchronous_commit=off
wal_buffers=16M
wal_writer_delay=2000ms
commit_delay=10000
checkpoint_segments=72
checkpoint_timeout=60min
checkpoint_completion_target=0.8

I'm thinking bgwriter_delay and wal_writer_delay might not be working
as I expected, and that maybe checkpoint_segments=72 is a bit too
high, but we were having much worse I/O storms before I pushed it that
high. Looking at checkpoint logging for the last few days, it goes
almost always like:

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.