Re: allow changing autovacuum_max_workers without restarting

On Mon, Jun 03, 2024 at 12:08:52PM -0700, Andres Freund wrote:
> I don't have time to read through the entire thread right now - it'd be good
> for the commit message of a patch like this to include justification for why
> it's ok to make such a change. Even before actually committing it, so
> reviewers have an easier time catching up.

Sorry about that.  I think the main question (besides "should we do this?")
is whether we ought to make the upper limit configurable.  My initial idea
was to split autovacuum_max_workers into two GUCs: one for the upper limit
that only be changed at server start and another for the effective limit
that can be changed up to the upper limit without restarting the server.
If we can just set a sufficiently high upper limit and avoid the extra GUC
without causing problems, that might be preferable, but I sense that you
are about to tell me that it will indeed cause problems.  :)

> Why do we think that increasing the number of PGPROC slots, heavyweight locks
> etc by 256 isn't going to cause issues?  That's not an insubstantial amount of
> memory to dedicate to something that will practically never be used.

I personally have not observed problems with these kinds of bumps in
resource usage, although I may be biased towards larger systems where it
doesn't matter as much.

> ISTM that at the very least we ought to exclude the reserved slots from the
> computation of things like the number of locks resulting from
> max_locks_per_transaction.  It's very common to increase
> max_locks_per_transaction substantially, adding ~250 to the multiplier can be
> a good amount of memory. And AV workers should never need a meaningful number.

This is an interesting idea.

> Increasing e.g. the size of the heavyweight lock table has consequences
> besides the increase in memory usage, the size increase can make it less
> likely for the table to fit largely into L3, thus decreasing performance.

IMHO this might be a good argument for making the upper limit configurable
and setting it relatively low by default.  That's not quite as nice from a
user experience perspective, but weird, hard-to-diagnose performance issues
are certainly not nice, either.

-- 
nathan