Re: Reworking WAL locking - Mailing list pgsql-hackers

On Thu, 2008-02-14 at 13:52 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Paul van den Bogaard (Sun) suggested to me that we could use more than
> > two WAL locks to improve concurrency.
> 
> After looking over the code I'm unconvinced that there's much win to be
> bought this way.  I had been thinking you could push a material amount
> of CRC-calculation work out of the locked code section, but that's not
> so; only the WAL record header remains to be CRC'd when we enter the
> locked section.  The locked section is not doing much more than copying
> the data into the WAL buffers, which for the normal case of short WAL
> records (no page images) isn't going to take long.  It's not clear that
> the extra lock acquisition will be repaid by better concurrency (and
> it's certain to be a dead loss if there's not concurrent writes
> happening).
> 
> In a situation where you're forced to write dirty buffers to make room,
> there could be some win from fixing things to not hold WALInsertLock
> while you do that, but AFAICT the proposal as it stands doesn't help
> on that front.  Also, now that we have the wal writer process, this
> case really shouldn't occur often enough to be a material performance
> problem (especially not if the number of wal buffers has been kicked
> up a bit).

Will think some more.

> > This requires us to get 2 locks for an XLogInsert rather than just one.
> > However the second lock is always acquired with zero-wait time when the
> > wal_buffers are sensibly sized.
> 
> In my experience, the fact that you won't block is not a guarantee that
> lock acquisition is cheap.  Trading ownership of locked cache lines back
> and forth between multiple CPUs is *expensive*.
> 
> So this may be worth trying but I'm unconvinced that it'll really
> provide a win that's worth the added complexity.
> 
> [ still staring at the code ... ]  Something that might be interesting
> though is to try to move some of the buffer control logic overhead out
> of WALInsertLock's domain and into WALWriteLock's domain.  Right now we
> prep the page header and so forth for a new page when we are ready to
> start inserting into it, which means that that work is done inside the
> concurrency bottleneck.  What if, as soon as we have written a filled
> WAL buffer for the last time, the *writer* process is responsible for
> re-initializing that buffer as empty and ready to write into for
> whatever page number it's ultimately going to have (which we can
> certainly determine since the number of wal buffers is known)?
> Getting that page-zeroing MemSet out of the WALInsertLock domain looks
> worth doing...

That sounds like a cheaper win.

I like this one because its another example that contention is not
static, seems like its more often cyclical. The WALInsertLock is held
longer than normal when we cross page boundaries, creating a pulsing
effect thru the lock queue.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com