agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: 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, 6 Jan 2025 16:15:25 -0600
Message-ID: <Z3xV_b3wp-XQR3Rm@nathan> (raw)
In-Reply-To: <Z3xQIPX3aeri4DiT@nathan>
References: <Zow-DBaDY2IzAzA2@nathan>
<Zpq9dx37tdUkV42U@nathan>
<ZqVZSQlgpmy6MM5X@nathan>
<ZsY96PvqkoJaC2Y-@nathan>
<ZzzMcv0lAdY4OzGi@nathan>
<Z2XJjI8fO8ksOv4E@nathan>
<Z3v8UFk0sOgFgWeZ@nathan>
<Z3xFE7DQTSRJw2FM@nathan>
<1346002.1736198977@sss.pgh.pa.us>
<Z3xQIPX3aeri4DiT@nathan>
On Mon, Jan 06, 2025 at 03:50:24PM -0600, Nathan Bossart wrote:
> I'm obviously biased, but I think it would be unfortunate to block features
> like this one because of low settings that would otherwise be unsuitable
> for any reasonable production workload. If we do want to at least support
> check-world on these systems, another option could be to simply lower the
> default of autovacuum_worker_slots to 7 (or maybe lower). Of course, that
> only helps until the next time more semaphores are required, but that's not
> a new problem.
I've attached a patch to lower the default to 5. That at least gives a
little bit of wiggle room for autovacuum_max_workers (and for a couple of
new auxiliary processes). FWIW the reason I originally set the default to
16 was to prevent most users from ever needing to think about adjusting
autovacuum_worker_slots (which requires a restart and is a completely new
parameter that most will be unfamiliar with).
--
nathan
From 3f3c783c025e2a931bb766dc6d13c7186c957ebc Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 6 Jan 2025 16:09:49 -0600
Subject: [PATCH v1 1/1] lower default of autovacuum_worker_slots to 5
---
doc/src/sgml/config.sgml | 2 +-
src/backend/utils/misc/guc_tables.c | 2 +-
src/backend/utils/misc/postgresql.conf.sample | 2 +-
3 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 740ff5d5044..4fda5e6ae79 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8639,7 +8639,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
<listitem>
<para>
Specifies the number of backend slots to reserve for autovacuum worker
- processes. The default is 16. This parameter can only be set at server
+ processes. The default is 5. This parameter can only be set at server
start.
</para>
<para>
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c9d8cd796a8..c75359b6bc4 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3472,7 +3472,7 @@ struct config_int ConfigureNamesInt[] =
NULL
},
&autovacuum_worker_slots,
- 16, 1, MAX_BACKENDS,
+ 5, 1, MAX_BACKENDS,
NULL, NULL, NULL
},
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b2bc43383db..c7d3852c040 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -661,7 +661,7 @@
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
-autovacuum_worker_slots = 16 # autovacuum worker slots to allocate
+autovacuum_worker_slots = 5 # autovacuum worker slots to allocate
# (change requires restart)
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
--
2.39.5 (Apple Git-154)
Attachments:
[text/plain] v1-0001-lower-default-of-autovacuum_worker_slots-to-5.patch (2.1K, ../Z3xV_b3wp-XQR3Rm@nathan/2-v1-0001-lower-default-of-autovacuum_worker_slots-to-5.patch)
download | inline diff:
From 3f3c783c025e2a931bb766dc6d13c7186c957ebc Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 6 Jan 2025 16:09:49 -0600
Subject: [PATCH v1 1/1] lower default of autovacuum_worker_slots to 5
---
doc/src/sgml/config.sgml | 2 +-
src/backend/utils/misc/guc_tables.c | 2 +-
src/backend/utils/misc/postgresql.conf.sample | 2 +-
3 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 740ff5d5044..4fda5e6ae79 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8639,7 +8639,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
<listitem>
<para>
Specifies the number of backend slots to reserve for autovacuum worker
- processes. The default is 16. This parameter can only be set at server
+ processes. The default is 5. This parameter can only be set at server
start.
</para>
<para>
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c9d8cd796a8..c75359b6bc4 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3472,7 +3472,7 @@ struct config_int ConfigureNamesInt[] =
NULL
},
&autovacuum_worker_slots,
- 16, 1, MAX_BACKENDS,
+ 5, 1, MAX_BACKENDS,
NULL, NULL, NULL
},
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b2bc43383db..c7d3852c040 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -661,7 +661,7 @@
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
-autovacuum_worker_slots = 16 # autovacuum worker slots to allocate
+autovacuum_worker_slots = 5 # autovacuum worker slots to allocate
# (change requires restart)
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
--
2.39.5 (Apple Git-154)
view thread (71+ messages) latest in thread
Message-ID: <Z3xV_b3wp-XQR3Rm@nathan>
Permalink: ../Z3xV_b3wp-XQR3Rm@nathan/
Also on: postgresql.org/message-id/Z3xV_b3wp-XQR3Rm@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, tgl@sss.pgh.pa.us, andres@anarazel.de, simseih@amazon.com, pryzby@telsasoft.com
Subject: Re: allow changing autovacuum_max_workers without restarting
In-Reply-To: <Z3xV_b3wp-XQR3Rm@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