Re: WAL insert delay settings

Hi,

On February 13, 2019 1:16:07 PM GMT+01:00, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>Bulk operations like CREATE INDEX, ALTER TABLE, or bulk loads can
>create
>a lot of WAL. A lot of WAL at once can cause delays in replication.
>For synchronous replication, this can make seemingly unrelated sessions
>hang. But also for asynchronous replication, it will increase latency.
>
>One idea to address this is to slow down WAL-generating maintenance
>operations. This is similar to the vacuum delay. Where the vacuum
>delay counts notional I/O cost before sleeping, here we would count how
>much WAL has been generated and sleep after some amount.
>
>I attach an example patch for this functionality. It introduces three
>settings:
>
>wal_insert_delay_enabled
>wal_insert_delay
>wal_insert_delay_size
>
>When you turn on wal_insert_delay_enabled, then it will sleep for
>wal_insert_delay after the session has produced wal_insert_delay_size
>of
>WAL data.
>
>The idea is that you would tune wal_insert_delay and
>wal_insert_delay_size to your required performance characteristics and
>then turn on wal_insert_delay_enabled individually in maintenance jobs
>or similar.
>
>To test, for example, set up pgbench with synchronous replication and
>run an unrelated large index build in a separate session. With the
>settings, you can make it as fast or as slow as you want.
>
>Tuning these settings, however, is quite mysterious I fear. You have
>to
>play around a lot to get settings that achieve the right balance.
>
>So, some questions:
>
>Is this useful?
>
>Any other thoughts on how to configure this or do this?
>
>Should we aim for a more general delay system, possibly including
>vacuum
>delay and perhaps something else?

Interesting idea, not yet quite sure what to think. But I don't think the way you did it is acceptable - we can't just delay while holding buffer locks, in critical sections, while not interruptible.

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.