Re: Checkpoint Tuning Question

On Fri, 2009-07-10 at 10:27 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > ISTM more likely to be a problem with checkpointing clog or subtrans.
> > That would block everybody and the scale of the problem is about right.
>
> That's what I had been thinking too, but the log_checkpoint output
> conclusively disproves it: those steps are taking less than 20msec.

OK, I was looking at total -write, not total - write - sync.

I think its a traffic jam.

After checkpoint in XLogInsert(), we discover that we now have to backup
a block that we didn't think so previously. So we have to drop the lock
and then re-access WALInsertLock. So every backend has to go through the
queue twice the first time it tries to write WAL immediately after a
checkpoint. Also, suddenly, every block needs to be copied to WAL, so
the CRC checks make each lock holder take longer than normal, so the
whole queue begins to backup. Then, because of wal_buffers being small
we find that the increased volume of WAL being written causes
WALInsertLock to be held across I/O.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support