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

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

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

> On Mar 1, 2021, at 8:43 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> 
> CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you
canconfirm the sender and know the content is safe.
 
> 
> 
> 
> On Mon, Feb 8, 2021 at 11:49 PM Mead, Scott <meads@amazon.com> 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
freezeon a relation.  Most users end up finding out they are in ‘to prevent wraparound’ after it has happened, this
meansthat if they want the vacuum to take advantage of more I/O, they need to stop and then restart the currently
runningvacuum (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
forvacuum to limit the maximum throughput of currently running vacuum processes to the cost_limit that was set when the
vacuumprocess began.
 
> 
> Thanks for your report.
> 
> I've not looked at the patch yet but I agree that the calculation for
> autovacuum cost delay seems not to work fine if vacuum-delay-related
> parameters (e.g., autovacuum_vacuum_cost_delay etc) are changed during
> vacuuming a table to speed up running autovacuums. Here is my
> analysis:


I appreciate your in-depth analysis and will comment in-line.  That said, I still think it’s important that the
attachedpath is applied.  As it is today, a simple few lines of code prevent users from being able to increase the
throughputon vacuums that are running without having to cancel them first.
 

The patch that I’ve provided allows users to decrease their vacuum_cost_delay and get an immediate boost in performance
totheir running vacuum jobs.
 


> 
> Suppose we have the following parameters and 3 autovacuum workers are
> running on different tables:
> 
> autovacuum_vacuum_cost_delay = 100
> autovacuum_vacuum_cost_limit = 100
> 
> Vacuum cost-based delay parameters for each workers are follows:
> 
> worker->wi_cost_limit_base = 100
> worker->wi_cost_limit = 66
> worker->wi_cost_delay = 100
> 
> Each running autovacuum has "wi_cost_limit = 66" because the total
> limit (100) is equally rationed. And another point is that the total
> wi_cost_limit (198 = 66*3) is less than autovacuum_vacuum_cost_limit,
> 100. Which are fine.
> 
> Here let's change autovacuum_vacuum_cost_delay/limit value to speed up
> running autovacuums.
> 
> Case 1 : increasing autovacuum_vacuum_cost_limit to 1000.
> 
> After reloading the configuration file, vacuum cost-based delay
> parameters for each worker become as follows:
> 
> worker->wi_cost_limit_base = 100
> worker->wi_cost_limit = 100
> worker->wi_cost_delay = 100
> 
> If we rationed autovacuum_vacuum_cost_limit, 1000, to 3 workers, it
> would be 333. But since we cap it by wi_cost_limit_base, the
> wi_cost_limit is 100. I think this is what Mead reported here.


Yes, this is exactly correct.  The cost_limit is capped at the cost_limit that was set during the start of a running
vacuum. My patch changes this cap to be the max allowed cost_limit (10,000).
 



> 
> Case 2 : decreasing autovacuum_vacuum_cost_delay to 10.
> 
> After reloading the configuration file, vacuum cost-based delay
> parameters for each workers become as follows:
> 
> worker->wi_cost_limit_base = 100
> worker->wi_cost_limit = 100
> worker->wi_cost_delay = 100
> 
> Actually, the result is the same as case 1. But In this case, the
> total cost among the three workers is 300, which is greater than
> autovacuum_vacuum_cost_limit, 100. This behavior violates what the
> documentation explains in the description of
> autovacuum_vacuum_cost_limit:
> 
> ---
> Note that the value is distributed proportionally among the running
> autovacuum workers, if there is more than one, so that the sum of the
> limits for each worker does not exceed the value of this variable.
> ---
> 
> It seems to me that those problems come from the fact that we don't
> change both wi_cost_limit_base and wi_cost_delay during auto-vacuuming
> a table in spite of using autovacuum_vac_cost_limit/delay to calculate
> cost_avail. Such a wrong calculation happens until all running
> autovacuum workers finish the current vacuums. When a worker starts to
> process a new table, it resets both wi_cost_limit_base and
> wi_cost_delay.


Exactly.  The tests I ran with extra debugging show exactly this behavior.

> 
> Looking at autovac_balance_cost(), it considers worker's
> wi_cost_limit_base to calculate the total base cost limit of
> participating active workers as follows:
> 
> cost_total +=
>    (double) worker->wi_cost_limit_base / worker->wi_cost_delay;
> 
> But what is the point of calculating it while assuming each worker
> having a different cost limit? Since workers vacuuming on a table
> whose cost parameters are set individually doesn't participate in this
> calculation (by commit 1021bd6a8 in 2014), having at_dobalance true, I
> wonder if we can just assume all workers have the same cost_limit and
> cost_delay except for workers setting at_dobalance true. If we can do
> that, I guess we no longer need wi_cost_limit_base.

This is where I wasn’t sure the exact reason for maintaining the wi_cost_limit_base.  It wasn’t immediately clear if
therewas a reason other than just tracking what it was at the start of the vacuum.
 


> 
> Also, we don't change wi_cost_delay during vacuuming a table, which
> seems wrong to me. autovac_balance_cost() can change workers'
> wi_cost_delay, eventually applying to VacuumCostDelay.
> 
> What do you think?

Yeah, I think updates to any of these throttles dynamically make sense, especially instead of changing other parameters
whena user sets a different one (delay vs. limit).
 





> 
> Regards,
> 
> --
> Masahiko Sawada
> EDB:  https://www.enterprisedb.com/
> 
> 

On Wed, Apr 14, 2021 at 11:17 PM Mead, Scott <meads@amazon.com> wrote:
>
>
>
> > On Mar 1, 2021, at 8:43 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you
canconfirm the sender and know the content is safe. 
> >
> >
> >
> > On Mon, Feb 8, 2021 at 11:49 PM Mead, Scott <meads@amazon.com> 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
freezeon a relation.  Most users end up finding out they are in ‘to prevent wraparound’ after it has happened, this
meansthat if they want the vacuum to take advantage of more I/O, they need to stop and then restart the currently
runningvacuum (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
forvacuum to limit the maximum throughput of currently running vacuum processes to the cost_limit that was set when the
vacuumprocess began. 
> >
> > Thanks for your report.
> >
> > I've not looked at the patch yet but I agree that the calculation for
> > autovacuum cost delay seems not to work fine if vacuum-delay-related
> > parameters (e.g., autovacuum_vacuum_cost_delay etc) are changed during
> > vacuuming a table to speed up running autovacuums. Here is my
> > analysis:
>
>
> I appreciate your in-depth analysis and will comment in-line.  That said, I still think it’s important that the
attachedpath is applied.  As it is today, a simple few lines of code prevent users from being able to increase the
throughputon vacuums that are running without having to cancel them first. 
>
> The patch that I’ve provided allows users to decrease their vacuum_cost_delay and get an immediate boost in
performanceto their running vacuum jobs. 
>
>
> >
> > Suppose we have the following parameters and 3 autovacuum workers are
> > running on different tables:
> >
> > autovacuum_vacuum_cost_delay = 100
> > autovacuum_vacuum_cost_limit = 100
> >
> > Vacuum cost-based delay parameters for each workers are follows:
> >
> > worker->wi_cost_limit_base = 100
> > worker->wi_cost_limit = 66
> > worker->wi_cost_delay = 100

Sorry, worker->wi_cost_limit should be 33.

> >
> > Each running autovacuum has "wi_cost_limit = 66" because the total
> > limit (100) is equally rationed. And another point is that the total
> > wi_cost_limit (198 = 66*3) is less than autovacuum_vacuum_cost_limit,
> > 100. Which are fine.

So the total wi_cost_limit, 99, is less than autovacuum_vacuum_cost_limit, 100.

> >
> > Here let's change autovacuum_vacuum_cost_delay/limit value to speed up
> > running autovacuums.
> >
> > Case 1 : increasing autovacuum_vacuum_cost_limit to 1000.
> >
> > After reloading the configuration file, vacuum cost-based delay
> > parameters for each worker become as follows:
> >
> > worker->wi_cost_limit_base = 100
> > worker->wi_cost_limit = 100
> > worker->wi_cost_delay = 100
> >
> > If we rationed autovacuum_vacuum_cost_limit, 1000, to 3 workers, it
> > would be 333. But since we cap it by wi_cost_limit_base, the
> > wi_cost_limit is 100. I think this is what Mead reported here.
>
>
> Yes, this is exactly correct.  The cost_limit is capped at the cost_limit that was set during the start of a running
vacuum. My patch changes this cap to be the max allowed cost_limit (10,000). 

The comment of worker's limit calculation says:

        /*
         * We put a lower bound of 1 on the cost_limit, to avoid division-
         * by-zero in the vacuum code.  Also, in case of roundoff trouble
         * in these calculations, let's be sure we don't ever set
         * cost_limit to more than the base value.
         */
        worker->wi_cost_limit = Max(Min(limit,
                                        worker->wi_cost_limit_base),
                                    1);

If we use the max cost_limit as the upper bound here, the worker's
limit could unnecessarily be higher than the base value in case of
roundoff trouble? I think that the problem here is rather that we
don't update wi_cost_limit_base and wi_cost_delay when rebalancing the
cost.

Regards,

--
Masahiko Sawada
EDB:  https://www.enterprisedb.com/

On 2021-Feb-08, 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).

Hello, I think this has been overlooked, right?  I can't find a relevant
commit, but maybe I just didn't look hard enough.  I have a feeling that
this is something that we should address.  If you still have the cycles,
please consider posting an updated patch and creating a commitfest
entry.

Thanks

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Someone said that it is at least an order of magnitude more work to do
production software than a prototype. I think he is wrong by at least
an order of magnitude."                              (Brian Kernighan)

On Mon, Feb 8, 2021 at 9:49 AM Mead, Scott <meads@amazon.com> wrote:
>   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
forvacuum to limit the maximum throughput of currently running vacuum processes to the cost_limit that was set when the
vacuumprocess 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.

So, in do_autovacuum() in the loop through all relations we will be
vacuuming (around line 2308) (comment says "perform operations on
collected tables"), we will reload the config file first before
operating on that table [1]. Any changes you have made to
autovacuum_vacuum_cost_limit or other GUCs will be read and changed
here.

Later in this same loop, table_recheck_autovac() will set
tab->at_vacuum_cost_limit from vac_cost_limit which is set from the
autovacuum_vacuum_cost_limit or vacuum_cost_limit and will pick up your
refreshed value.

Then a bit further down, (before autovac_balance_cost()),
MyWorkerInfo->wi_cost_limit_base is set from tab->at_vacuum_cost_limit.

In autovac_balance_cost(), when we loop through the running workers to
calculate the worker->wi_cost_limit, workers who have reloaded the
config file in the do_autovacuum() loop prior to our taking the
AutovacuumLock will have the new version of autovacuum_vacuum_cost_limit
in their wi_cost_limit_base.

If you saw an old value in the DEBUG log output, that could be
because it was for a worker who has not yet reloaded the config file.
(the launcher also calls autovac_balance_cost(), but I will
assume we are just talking about the workers here).

Note that this will only pick up changes between tables being
autovacuumed. If you want to see updates to the value in the middle of
autovacuum vacuuming a table, then we would need to reload the
configuration file more often than just between tables.

I have started a discussion about doing this in [2]. I made it a
separate thread because my proposed changes would have effects outside
of autovacuum. Processing the config file reload in vacuum_delay_point()
would affect vacuum and analyze (i.e. not just autovacuum). Explicit
vacuum and analyze rely on the per statement config reload in
PostgresMain().

> Interestingly, autovac_balance_cost(void) is only updating the cost_limit, even if the cost_delay is modified.  This
isdone correctly, it was just a surprise to see the behavior. 

If this was during vacuuming of a single table, this is expected for the
same reason described above.

- Melanie

[1] https://github.com/postgres/postgres/blob/master/src/backend/postmaster/autovacuum.c#L2324
[2] https://www.postgresql.org/message-id/CAAKRu_ZngzqnEODc7LmS1NH04Kt6Y9huSjz5pp7%2BDXhrjDA0gw%40mail.gmail.com