agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nathan Bossart <nathandbossart@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Imseih (AWS), Sami <simseih@amazon.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: allow changing autovacuum_max_workers without restarting
Date: Mon, 3 Jun 2024 14:28:13 -0500
Message-ID: <Zl4ZTRdx38sDktRs@nathan> (raw)
In-Reply-To: <20240603190852.jcibstxflb33hjvu@awork3.anarazel.de>
References: <20240415162833.GA2857238@nathanxps13>
<20240415163749.GA2858464@nathanxps13>
<96B4FC7B-A919-4F93-80A3-BA3EA4F8479A@amazon.com>
<20240503010415.GA1008894@nathanxps13>
<2780DB08-DCA6-4E57-B823-303A0E7E17D7@amazon.com>
<20240507160605.GA2523153@nathanxps13>
<011CA929-4933-40EA-B1D3-29131FEFBCF0@amazon.com>
<20240517021646.GA1745636@nathanxps13>
<Zl4Q7YGslvdL0eYj@nathan>
<20240603190852.jcibstxflb33hjvu@awork3.anarazel.de>
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
view thread (71+ messages) latest in thread
Message-ID: <Zl4ZTRdx38sDktRs@nathan>
Permalink: ../Zl4ZTRdx38sDktRs@nathan/
Also on: postgresql.org/message-id/Zl4ZTRdx38sDktRs@nathan
reply
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, andres@anarazel.de, simseih@amazon.com, pryzby@telsasoft.com
Subject: Re: allow changing autovacuum_max_workers without restarting
In-Reply-To: <Zl4ZTRdx38sDktRs@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox