[BUG] Autovacuum not dynamically decreasing cost_limit and cost_delay - Mailing list pgsql-hackers
| From | Mead, Scott |
|---|---|
| Subject | [BUG] Autovacuum not dynamically decreasing cost_limit and cost_delay |
| Date | |
| Msg-id | 0A3F8A3C-4328-4A4B-80CF-14CEBE0B695D@amazon.com Whole thread Raw |
| Responses |
Re: [BUG] Autovacuum not dynamically decreasing cost_limit and cost_delay
Re: [BUG] Autovacuum not dynamically decreasing cost_limit and cost_delay Re: [BUG] Autovacuum not dynamically decreasing cost_limit and cost_delay |
| List | pgsql-hackers |
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.
I believe this is a bug because currently, autovacuum will dynamically detect and increase the cost_limit or cost_delay, but it can never decrease those values beyond their setting when the vacuum began. 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, which 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 tests 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.
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
Attachment
pgsql-hackers by date: