Date: Wed, 29 Mar 2023 15:00:47 +0900 (JST)
Message-Id: <20230329.150047.1496807418504041265.horikyota.ntt@gmail.com>
To: melanieplageman@gmail.com
Cc: sawada.mshk@gmail.com, daniel@yesql.se, pgsql-hackers@postgresql.org,
 andres@anarazel.de, amit.kapila16@gmail.com
Subject: Re: Should vacuum process config file reload more often
From: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
In-Reply-To: <20230329.132155.629765142788133576.horikyota.ntt@gmail.com>
References: <CAAKRu_ZnG2nYcvzb4a-CGwg33_4RX83qitGGVmjhHZZtqeSrRA@mail.gmail.com>
	<20230329.120908.350115307125624430.horikyota.ntt@gmail.com>
	<20230329.132155.629765142788133576.horikyota.ntt@gmail.com>
User-Agent: Mew version 6.8 on Emacs 26.1
Mime-Version: 1.0
Content-Type: Text/Plain; charset=iso-2022-jp
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20230329.150047.1496807418504041265.horikyota.ntt%40gmail.com>
Precedence: bulk

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