Re: [BUG] Autovacuum not dynamically decreasing cost_limit and cost_delay

Thanks for the patch, Mead.

For 'MAXVACUUMCOSTLIMIT", it would be nice to follow the current GUC
pattern to do define a constant.

For example, the constant "MAX_KILOBYTES" is defined in guc.h, with a
pattern like, "MAX_" to make it easy to read.

Best regards,

David

On 2021-02-08 6:48 a.m., Mead, Scott wrote:
> 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
>
--
David

Software Engineer
Highgo Software Inc. (Canada)
www.highgo.ca