Horton
Menu

Re: allow changing autovacuum_max_workers without restarting

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: "Imseih (AWS), Sami" <simseih@amazon.com>, Justin Pryzby <pryzby@telsasoft.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2024-06-03T19:08:52Z
Lists: pgsql-hackers 
Hi,

On 2024-06-03 13:52:29 -0500, Nathan Bossart wrote:
> Here is an updated patch that uses 256 as the upper limit.

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.

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.

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.

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.

Greetings,

Andres Freund

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Give up on running with NetBSD/OpenBSD's default semaphore settings.

  2. initdb: Do not report default autovacuum_worker_slots.

  3. Lower default value of autovacuum_worker_slots in initdb as needed.

  4. Allow changing autovacuum_max_workers without restarting.

  5. Reserve a PGPROC slot and semaphore for the slotsync worker process.

  6. Make archiver process an auxiliary process.

  7. Correct the formulas for System V IPC parameters SEMMNI and SEMMNS in docs.