Re: Reworking the writing of WAL

On Fri, Aug 12, 2011 at 7:02 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Aug 12, 2011 at 11:34 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> 1. Earlier, I suggested that the sync rep code would allow us to
>> redesign the way we write WAL, using ideas from group commit. My
>> proposal is that when when a backend needs to flush WAL to local disk
>> it will be added to a SHMQUEUE exactly the same as when we flush WAL
>> to sync standby. The WALWriter will be woken by latch and then perform
>> the actual work. When complete WALWriter will wake the queue in order,
>> so there is a natural group commit effect. The WAL queue will be
>> protected by a new lock WALFlushRequestLock, which should be much less
>> heavily contended than the way we do things now. Notably this approach
>> will mean that all waiters get woken quickly, without having to wait
>> for the queue of WALWriteLock requests to drain down, so commit will
>> be marginally quicker. On almost idle systems this will give very
>> nearly the same response time as having each backend write WAL
>> directly. On busy systems this will give optimal efficiency by having
>> WALWriter working in a very tight loop to perform the I/O instead of
>> queuing itself to get the WALWriteLock with all the other backends. It
>> will also allow piggybacking of commits even when WALInsertLock is not
>> available.
>
> I like the idea of putting all the backends that are waiting for xlog
> flush on a SHM_QUEUE, and having a single process do the flush and
> then wake them all up.  That seems like a promising approach, and
> should avoid quite a bit of context-switching and spinlocking that
> would otherwise be necessary.

OK, good.

This work builds on Peter's latch-for-walwriter patch, so this won't
appear for a while yet, since it needs to happen after that. Hopefully
for Nov CF.

> However, I think it's possible that the
> overhead in the single-client case might be pretty significant, and
> I'm wondering whether we might be able to set things up so that
> backends can flush their own WAL in the uncontended case.

I think we should measure that and see. I don't think it will be that
bad, but I accept it might be wishful thinking on my part.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services