Re: WAL insert delay settings

Hi

13.02.2019 17:16, Peter Eisentraut пишет:
> 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?
>
I think it's better to have more general cost-based settings which allow
to control performance. Something like what have been already done for
autovacuum.

For example, introduce vacuum-similar mechanism with the following
controlables:
maintenance_cost_page_hit
maintenance_cost_page_miss
maintenance_cost_page_dirty
maintenance_cost_delay
maintenance_cost_limit

maintenance_cost_delay=0 (default) means feature is disabled, but if
user wants to limit performance he can define such parameters in
per-session, or per-user manner. Especially it can be useful for
limiting an already running sessions, such as mass deletion, or pg_dump.

Of course, it's just an idea, because I can't imagine how many things
should be touched in order to implement this.

Regards, Alexey Lesovsky