Обсуждение: wal_buffers = -1

On 17 January 2014 13:01, Magnus Hagander <magnus@hagander.net> wrote:
> Is there any real use-case for not setting wal_buffers to -1 these days?
>
> Or should we just remove it and use the -1 behaviour at all times?
>
> IIRC we discussed not keeping it at all when the autotune behavior was
> introduced, but said we wanted to keep it "just in case". If we're not ready
> to remove it, then does that just mean that we need to fix it so we can?

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

-- 
Thom

Hi,

On 2014-01-17 14:01:56 +0100, Magnus Hagander wrote:
> Is there any real use-case for not setting wal_buffers to -1 these days?
> 
> Or should we just remove it and use the -1 behaviour at all times?

I have seen improvements by setting it larger than the max -1 one
value. Also, for some workloads (low latency) it can be beneficial to
have a small s_b but still have a larger wal_buffers setting.

So -1 for removing it from me.

Greetings,

Andres Freund

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

On 17 January 2014 13:20, Magnus Hagander <magnus@hagander.net> wrote:
> On Fri, Jan 17, 2014 at 2:07 PM, Thom Brown <thom@linux.com> wrote:
>>
>> On 17 January 2014 13:01, Magnus Hagander <magnus@hagander.net> wrote:
>> > Is there any real use-case for not setting wal_buffers to -1 these days?
>> >
>> > Or should we just remove it and use the -1 behaviour at all times?
>> >
>> > IIRC we discussed not keeping it at all when the autotune behavior was
>> > introduced, but said we wanted to keep it "just in case". If we're not
>> > ready
>> > to remove it, then does that just mean that we need to fix it so we can?
>>
>> 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? :)

Well, that's the question.  Do we have a heuristic sweet-spot that
folk would agree on?

-- 
Thom

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