Re: Should vacuum process config file reload more often

On Wed, Apr 5, 2023 at 11:56 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> To be honest, I think that the whole system where we divide the cost
> limit across the workers is the wrong idea. Does anyone actually like
> that behavior? This patch probably shouldn't touch that, just in the
> interest of getting something done that is an improvement over where
> we are now, but I think this behavior is really counterintuitive.
> People expect that they can increase autovacuum_max_workers to get
> more vacuuming done, and actually in most cases that does not work.

I disagree. Increasing autovacuum_max_workers as a method of
increasing the overall aggressiveness of autovacuum seems like the
wrong idea. I'm sure that users do that at times, but they really
ought to have a better way of getting the same result.

ISTM that autovacuum_max_workers confuses the question of what the
maximum possible number of workers should ever be (in extreme cases)
with the question of how many workers might be a good idea given
present conditions.

> And if that behavior didn't exist, this patch would also be a whole
> lot simpler.

Probably, but the fact remains that the system level view of things is
mostly what matters. The competition between the amount of vacuuming
that we can afford to do right now and the amount of vacuuming that
we'd ideally be able to do really matters. In fact, I'd argue that the
amount of vacuuming that we'd ideally be able to do isn't a
particularly meaningful concept on its own. It's just too hard to
model what we need to do accurately -- emphasizing what we can afford
to do seems much more promising.

> Again, I don't think this is something we should try to
> address right now under time pressure, but in the future, I think we
> should consider ripping this behavior out.

-1. The delay stuff might not work as well as it should, but it at
least seems like roughly the right idea. The bigger problem seems to
be everything else -- the way that tuning autovacuum_max_workers kinda
makes sense (it shouldn't be an interesting tunable), and the problems
with the autovacuum.c scheduling being so primitive.

--
Peter Geoghegan