Re: Checkpoint Tuning Question

On Fri, 2009-07-10 at 14:25 -0500, Dan Armbrust wrote:
> > 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.)
> >

> Ok, I tried a few different values - 32kb, 64kb, 512kb, 2MB and 10MB.
>
> I'm not seeing any highly noticeable change in behaviour with any
> setting - it wasn't a scientific test, but I seem to have about the
> same size hiccup with each setting. The hiccup may be slightly
> shorter with the 10MB setting, but barely, if it is.

OK, so its not the "last bit". Let's look at the first idea again:

In more detail, the explanation relates to a section of code in
XLogInsert() in xlog.c that has a comment

/*
* Check to see if my RedoRecPtr is out of date. If so, may have to go
* back and recompute everything. This can only happen just after a
* checkpoint, so it's better to be slow in this case and fast
otherwise.
*
* If we aren't doing full-page writes then RedoRecPtr doesn't actually
* affect the contents of the XLOG record, so we'll update our local
copy
* but not force a recomputation.
*/

This causes us to queue for the WALInsertLock twice at exactly the time
when every caller needs to calculate the CRC for complete blocks. So we
queue twice when the lock-hold-time is consistently high, causing queue
lengths to go ballistic. That also means that XLogFlush never achieves a
piggy-back flush, so WALWriteLock hold times go up also. I bet that
takes a significant amount of time to settle down once initiated,
probably a function of the write transaction ratio and number of
frequently accessed blocks.

We haven't got *any* measurements that show how long traffic jams take
to clear and I think we need them.

Also think piggy-back writes works OK, but stops working when work
begins to queue, which will cause a "stall" catastrophe, like in
aerodynamics.

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