Re: wal_buffers = -1

On Fri, Jan 17, 2014 at 8:20 AM, Magnus Hagander <magnus@hagander.net> wrote:
>> Robert Haas reported that setting it to 32MB can yield a considerable
>> performance benefit:
>>
>> http://www.postgresql.org/message-id/CA+TgmobgMv_aaakLoasBt=5iYfi=kdcOUz0X9TdYe0c2SZ=2Pg@mail.gmail.com
>
> In that case, sholdn't the autotuning be changed to not limit it at 16MB? :)

I'm in favor of keeping the setting; I think that the auto-tuning has
largely eliminated the pain in this area for the majority of users,
but that doesn't mean we should deny someone who really wants to
squeeze the last drop of performance out of their system the
opportunity to poke at it manually.  I doubt it's the least useful
setting we have.  The test above shows 32MB beating 16MB, but I think
I did other tests where 16MB and 64MB came out the same.

Back when I was working heavily on performance, I did a number of
tests to try to understand what events cause latency spikes.  Many of
those events are related to write-ahead logging.  It turns out that
writing a page from PostgreSQL's WAL buffers to the OS cache is
usually pretty fast, unless the OS thinks we're dirtying data too
quickly and decides to slam on the brakes.  Calling fsync() to get the
data out to disk, though, is very slow.  However, both of those
operations are protected by the same lock (WALWriteLock), so it's
frequently the case that no more WAL buffer space can be freed up by
calling write() because the guy who has the lock is busy waiting for
an fsync().  That sucks, because there's no intrinsic reason why we
can't have one backend doing a write() while another backend is doing
an fsync().  On a related note, there's no real reason why the poor
bastard who writes the WAL record that fills a segment should be
forced to synchronously flush the segment instead of letting it be
done in the background, but right now he is.

I think if we fix these problems, the optimal value for wal_buffers is
likely to change; however, I'm not certain we'll be able to to
auto-tune it perfectly on day one.  Having a setting makes it easier
for people to experiment with different values, and I think that's
good.

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