Horton
Menu

Re: allow changing autovacuum_max_workers without restarting

Sami Imseih <simseih@amazon.com>

View thread
From: "Imseih (AWS), Sami" <simseih@amazon.com>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2024-04-12T17:27:40Z
Lists: pgsql-hackers 
I spent sometime reviewing/testing the POC. It is relatively simple with a lot
of obvious value. 

I tested with 16 tables that constantly reach the autovac threashold and the
patch did the right thing. I observed concurrent autovacuum workers matching
the setting as I was adjusting it dynamically.

As you mention above, If there are more autovacs in progress and a new lower setting 
is applied, we should not take any special action on those autovacuums, and eventually 
the max number of autovacuum workers will match the setting.

I also tested by allowing user connections to reach max_connections, and observed the 
expected number of autovacuums spinning up and correctly adjusted.

Having autovacuum tests ( unless I missed something ) like the above is a good 
general improvement, but it should not be tied to this. 

A few comments on  the POC patch:

1/ We should emit a log when autovacuum_workers is set higher than the max.

2/ should the name of the restart limit be "reserved_autovacuum_workers"?

Regards,

Sami Imseih
AWS (Amazon Web Services)

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.