Running autovacuum dynamic update to cost_limit and delay - Mailing list pgsql-hackers

From Mead, Scott
Subject Running autovacuum dynamic update to cost_limit and delay
Date
Msg-id 13A6B954-5C21-4E60-BC06-751C8EA469A0@amazon.com
Whole thread Raw
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.
 
   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




Attachment

pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: SEARCH and CYCLE clauses
Next
From: Andres Freund
Date:
Subject: Re: Fix DROP TABLESPACE on Windows with ProcSignalBarrier?