Re: wal_buffers

On Wed, Aug 29, 2012 at 10:25 PM, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
> On 19 February 2012 05:24, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> I have attached tps scatterplots. The obvious conclusion appears to
>> be that, with only 16MB of wal_buffers, the buffer "wraps around" with
>> some regularity: we can't insert more WAL because the buffer we need
>> to use still contains WAL that hasn't yet been fsync'd, leading to
>> long stalls. More buffer space ameliorates the problem.
>
> Incidentally, I wondered if we could further improve group commit
> performance by implementing commit_delay with a WaitLatch call, and
> setting the latch in the event of WAL buffers wraparound (or rather, a
> queued wraparound request - a segment switch needs WALWriteLock, which
> the group commit leader holds for a relatively long time during the
> delay). I'm not really sure how significant a win this might be,
> though. There could be other types of contention, which could be
> considerably more significant. I'll try and take a look at it next
> week.

I have a feeling that one of the big bottlenecks here is that we force
an immediate fsync when we reach the end of a segment. I think it was
originally done that way to keep the code simple, and it does
accomplish that, but it's not so hot for performance. More generally,
I think we really need to split WALWriteLock into two locks, one to
protect the write position and the other to protect the flush
position. I think we're often ending up with a write (which is
usually fast) waiting for a flush (which is often much slower) when in
fact those things ought to be able to happen in parallel.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company