Thread: wal_buffers = -1
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?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
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 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:Robert Haas reported that setting it to 32MB can yield a considerable
> 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?
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? :)
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
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