Running autovacuum dynamic update to cost_limit and delay
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
Attachments:
vacuum_dynamic_increase_cost_limit.20210201.diffapplication/octet-stream; name=vacuum_dynamic_increase_cost_limit.20210201.diffDownload
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 462f9a0f82..ee058b2b36 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -2069,6 +2069,9 @@ vacuum_delay_point(void)
if (msec > VacuumCostDelay * 4)
msec = VacuumCostDelay * 4;
+ elog(DEBUG2,"VACUUM Sleep: Delay: %f, CostBalance: %d, CostLimit: %i, msec: %f",
+ VacuumCostDelay,VacuumCostBalance,VacuumCostLimit,msec);
+
pgstat_report_wait_start(WAIT_EVENT_VACUUM_DELAY);
pg_usleep((long) (msec * 1000));
pgstat_report_wait_end();
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 47e60ca561..b9bc81fe99 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -1854,7 +1854,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);
}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index eafdb1118e..b012dffaf6 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2433,7 +2433,7 @@ static struct config_int ConfigureNamesInt[] =
NULL
},
&VacuumCostLimit,
- 200, 1, 10000,
+ 200, 1, MAXVACUUMCOSTLIMIT,
NULL, NULL, NULL
},
@@ -2443,7 +2443,7 @@ static struct config_int ConfigureNamesInt[] =
NULL
},
&autovacuum_vac_cost_limit,
- -1, -1, 10000,
+ -1, -1, MAXVACUUMCOSTLIMIT,
NULL, NULL, NULL
},
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 1bdc97e308..a3949823b4 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -254,6 +254,8 @@ extern int VacuumCostPageDirty;
extern int VacuumCostLimit;
extern double VacuumCostDelay;
+#define MAXVACUUMCOSTLIMIT 10000
+
extern int64 VacuumPageHit;
extern int64 VacuumPageMiss;
extern int64 VacuumPageDirty;