Re: Checkpoint Tuning Question

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> 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.

Hm, I'm not sure I believe any of that except the last bit, seeing that
he's got plenty of excess CPU capability. But the last bit fits with
the wimpy-I/O problem, and it also offers something we could test.
Dan, please see what happens when you vary the wal_buffers setting.
(Note you need a postmaster restart to change that.)

regards, tom lane