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
