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

> On Mar 1, 2021, at 8:43 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe.
>
>
>
> On Mon, Feb 8, 2021 at 11:49 PM Mead, Scott <meads(at)amazon(dot)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 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.
>
> 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 attached path is applied. As it is today, a simple few lines of code prevent users from being able to increase the throughput on 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 to 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
>
> 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 there was 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 when a user sets a different one (delay vs. limit).

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