From: Nathan Bossart <nathandbossart@gmail.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: allow changing autovacuum_max_workers without restarting
Date: Mon, 15 Apr 2024 11:28:33 -0500
Message-ID: <20240415162833.GA2857238@nathanxps13> (raw)
In-Reply-To: <Zh0srZSCSfcK_oY9@pryzbyj2023>
References: <20240410212344.GA1824549@nathanxps13>
<Zh0srZSCSfcK_oY9@pryzbyj2023>
On Mon, Apr 15, 2024 at 08:33:33AM -0500, Justin Pryzby wrote:
> On Wed, Apr 10, 2024 at 04:23:44PM -0500, Nathan Bossart wrote:
>> The proof-of-concept patch keeps autovacuum_max_workers as the maximum
>> number of slots to reserve for workers, but I think we should instead
>> rename this parameter to something else and then reintroduce
>> autovacuum_max_workers as the new parameter that can be adjusted without
>> restarting. That way, autovacuum_max_workers continues to work much the
>> same way as in previous versions.
>
> When I thought about this, I considered proposing to add a new GUC for
> "autovacuum_policy_workers".
>
> autovacuum_max_workers would be the same as before, requiring a restart
> to change. The policy GUC would be the soft limit, changable at runtime
> up to the hard limit of autovacuum_max_workers (or maybe any policy
> value exceeding autovacuum_max_workers would be ignored).
>
> We'd probably change autovacuum_max_workers to default to a higher value
> (8, or 32 as in your patch), and have autovacuum_max_workers default to
> 3, for consistency with historic behavior. Maybe
> autovacuum_policy_workers=-1 would mean to use all workers.
This sounds like roughly the same idea, although it is backwards from what
I'm proposing in the v1 patch set. My thinking is that by making a new
restart-only GUC that would by default be set higher than the vast majority
of systems should ever need, we could simplify migrating to these
parameters. The autovacuum_max_workers parameter would effectively retain
it's original meaning, and existing settings would continue to work
normally on v18, but users could now adjust it without restarting. If we
did it the other way, users would need to bump up autovacuum_max_workers
and restart prior to being able to raise autovacuum_policy_workers beyond
what they previously had set for autovacuum_max_workers. That being said,
I'm open to doing it this way if folks prefer this approach, as I think it
is still an improvement.
> There's the existing idea to change autovacuum thresholds during the
> busy period of the day vs. off hours. This would allow something
> similar with nworkers rather than thresholds: if the goal were to reduce
> the resource use of vacuum, the admin could set max_workers=8, with
> policy_workers=2 during the busy period.
Precisely.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, pryzby@telsasoft.com
Subject: Re: allow changing autovacuum_max_workers without restarting
In-Reply-To: <20240415162833.GA2857238@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox