Hello,
I recently looked at what it would take to make a running autovacuum pick-up a change to either cost_delay or cost_limit. Users frequently will have a conservative value set, and then wish to change it when autovacuum initiates a freeze on a relation. Most users end up finding out they are in ‘to prevent wraparound’ after it has happened, this means that if they want the vacuum to take advantage of more I/O, they need to stop and then restart the currently running vacuum (after reloading the GUCs).
Initially, my goal was to determine feasibility for making this dynamic. I added debug code to vacuum.c:vacuum_delay_point(void) and found that changes to cost_delay and cost_limit are already processed by a running vacuum. There was a bug preventing the cost_delay or cost_limit from being configured to allow higher throughput however.
The current behavior is for vacuum to limit the maximum throughput of currently running vacuum processes to the cost_limit that was set when the vacuum process began.
I changed this (see attached) to allow the cost_limit to be re-calculated up to the maximum allowable (currently 10,000). This has the effect of allowing users to reload a configuration change and an in-progress vacuum can be ‘sped-up’ by setting either the cost_limit or cost_delay.
The problematic piece is:
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index c6ec657a93..d3c6b0d805 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -1834,7 +1834,7 @@ autovac_balance_cost(void)
* cost_limit to more than the base value.
*/
worker->wi_cost_limit = Max(Min(limit,
- worker->wi_cost_limit_base),
+ MAXVACUUMCOSTLIMIT),
1);
}
We limit the worker to the max cost_limit that was set at the beginning of the vacuum. I introduced the MAXVACUUMCOSTLIMIT constant (currently defined to 10000, 10000 is the currently max limit already defined) in miscadmin.h so that vacuum will now be able to adjust the cost_limit up to 10000 as the upper limit in a currently running vacuum.
The test’s that I’ve run show that the performance of an existing vacuum can be increased commensurate with the parameter change. Interestingly, autovac_balance_cost(void) is only updating the cost_limit, even if the cost_delay is modified. This is done correctly, it was just a surprise to see the behavior. A restart of autovacuum will pick up the new settings.
2021-02-01 13:36:52.346 EST [37891] DEBUG: VACUUM Sleep: Delay: 20.000000, CostBalance: 207, CostLimit: 200, msec: 20.700000
2021-02-01 13:36:52.346 EST [37891] CONTEXT: while scanning block 1824 of relation "public.blah"
2021-02-01 13:36:52.362 EST [36460] LOG: received SIGHUP, reloading configuration files
2021-02-01 13:36:52.364 EST [36460] LOG: parameter "autovacuum_vacuum_cost_delay" changed to "2"
\
2021-02-01 13:36:52.365 EST [36463] DEBUG: checkpointer updated shared memory configuration values
2021-02-01 13:36:52.366 EST [36466] DEBUG: autovac_balance_cost(pid=37891 db=13207, rel=16384, dobalance=yes cost_limit=2000, cost_limit_base=200, cost_delay=20)
2021-02-01 13:36:52.366 EST [36467] DEBUG: received inquiry for database 0
2021-02-01 13:36:52.366 EST [36467] DEBUG: writing stats file "pg_stat_tmp/global.stat"
2021-02-01 13:36:52.366 EST [36467] DEBUG: writing stats file "pg_stat_tmp/db_0.stat"
2021-02-01 13:36:52.388 EST [37891] DEBUG: VACUUM Sleep: Delay: 20.000000, CostBalance: 2001, CostLimit: 2000, msec: 20.010000