[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

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 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:

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.

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.

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.

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?

Regards,

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

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 can confirm 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 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).

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 can confirm 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 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

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 Wed, May 26, 2021 at 4:01 AM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:

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 can confirm 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 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

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.

Currently, vacuum always limits you to the cost_limit_base from the time
that your vacuum started. I'm not sure why, I don't believe it's rounding
related because the rest of the rebalancing code works properly. ISTM that
looking simply allowing the updated cost_limit is a simple solution since
the rebalance code will automatically take it into account.

Regards,

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

--
--
Scott Mead
*scott@meads.us <scott@meads.us>*

Moving to bugs list.

On Tue, Oct 26, 2021 at 11:23 AM Scott Mead <scott@meads.us> wrote:

On Wed, May 26, 2021 at 4:01 AM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:

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 can confirm 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 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

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.

Currently, vacuum always limits you to the cost_limit_base from the time
that your vacuum started. I'm not sure why, I don't believe it's rounding
related because the rest of the rebalancing code works properly. ISTM that
looking simply allowing the updated cost_limit is a simple solution since
the rebalance code will automatically take it into account.

Regards,

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

--
--
Scott Mead
*scott@meads.us <scott@meads.us>*

--
--
Scott Mead
*scott@meads.us <scott@meads.us>*

+1
This is a bug/design which is causing real pain in operations.
There are cases the autovacuum worker runs for days and we are left with no
option other than cancelling it.

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, Jan 23, 2023 at 12:33 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

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! Yeah, I should be able to get this together next week.

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)

--
--
Scott Mead
*scott@meads.us <scott@meads.us>*

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 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.

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]https://github.com/postgres/postgres/blob/master/src/backend/postmaster/autovacuum.c#L2324. 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]/messages/by-id/CAAKRu_ZngzqnEODc7LmS1NH04Kt6Y9huSjz5pp7+DXhrjDA0gw@mail.gmail.com. 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 is done 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]: /messages/by-id/CAAKRu_ZngzqnEODc7LmS1NH04Kt6Y9huSjz5pp7+DXhrjDA0gw@mail.gmail.com