Re: Should vacuum process config file reload more often

At Wed, 29 Mar 2023 13:21:55 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in 
> autovacuum.c:2893
> 		/*
> 		 * If any of the cost delay parameters has been set individually for
> 		 * this table, disable the balancing algorithm.
> 		 */
> 		tab->at_dobalance =
> 			!(avopts && (avopts->vacuum_cost_limit > 0 ||
> 						 avopts->vacuum_cost_delay > 0));
> 
> So, sorry for the noise. I'll review it while this into cnosideration.

Then I found that the code is quite confusing as it is.

For the tables that don't have cost_delay and cost_limit specified
indificually, at_vacuum_cost_limit and _delay store the system global
values detemined by GUCs. wi_cost_delay, _limit and _limit_base stores
the same values with them. As the result I concluded tha
autovac_balance_cost() does exactly what Melanie's patch does, except
that nworkers_for_balance is not stored in shared memory.

I discovered that commit 1021bd6a89 brought in do_balance.

>    Since the mechanism is already complicated, just disable it for those
>    cases rather than trying to make it cope.  There are undesirable

After reading this, I get why the code is so complex.  It is a remnant
of when balancing was done with tables that had individually specified
cost parameters. And I found the following description in the doc.

https://www.postgresql.org/docs/devel/routine-vacuuming.html
> When multiple workers are running, the autovacuum cost delay
> parameters (see Section 20.4.4) are “balanced” among all the running
> workers, so that the total I/O impact on the system is the same
> regardless of the number of workers actually running. However, any
> workers processing tables whose per-table
> autovacuum_vacuum_cost_delay or autovacuum_vacuum_cost_limit storage
> parameters have been set are not considered in the balancing
> algorithm.

The initial balancing mechanism was brought in by e2a186b03c back in
2007. The balancing code has had that unnecessarily complexity ever
since.

Since I can't think of a better idea than Melanie's proposal for
handling this code, I'll keep reviewing it with that approach in mind.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center