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: pgsql-hackers@lists.postgresql.org, "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: Tue, 7 Jan 2025 11:23:47 -0600
Message-ID: <Z31jI10tLLd6thfu@nathan> (raw)
In-Reply-To: <1386858.1736220547@sss.pgh.pa.us>
References: <Z3xFE7DQTSRJw2FM@nathan>
<1346002.1736198977@sss.pgh.pa.us>
<Z3xQIPX3aeri4DiT@nathan>
<Z3xV_b3wp-XQR3Rm@nathan>
<7B8590B5-3A23-4CF6-B21D-4AA8C0177C2C@anarazel.de>
<1353350.1736202977@sss.pgh.pa.us>
<Z3xlNmC3qtwf1lz1@nathan>
<1359669.1736206603@sss.pgh.pa.us>
<Z3yVFfKG_RAh8FgP@nathan>
<1386858.1736220547@sss.pgh.pa.us>
On Mon, Jan 06, 2025 at 10:29:07PM -0500, Tom Lane wrote:
> +1 for simplicity ... but on reflection, what do you think about
> using max_connections / 6? That would keep autovacuum_worker_slots
> at 100 / 6 = 16 for the vast majority of systems. For the worst case
> *BSD machines, we'd select 25 / 6 = 4 which results in consuming one
> more semaphore than where we were yesterday. I'm willing to accept
> that outcome though, since we still have 3 or so to spare.
WFM. I'm kicking myself for not having thought of that...
> Other than the specific magic number, your patch LGTM.
Here's a new version of the patch with some small cosmetic changes
(including more commentary about the formula) and the constant changed to
6. I'll go commit this shortly.
--
nathan
From d8b9fe6d3c166c14d9a8d17f43418be33c4e0784 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 7 Jan 2025 11:15:22 -0600
Subject: [PATCH v3 1/1] Lower default value of autovacuum_worker_slots in
initdb as needed.
TODO
Reported-by: Tom Lane
Suggested-by: Andres Freund
Reviewed-by: Tom Lane
Discussion: https://postgr.es/m/1346002.1736198977%40sss.pgh.pa.us
---
doc/src/sgml/config.sgml | 5 +++--
src/bin/initdb/initdb.c | 40 ++++++++++++++++++++++++++++++++++------
2 files changed, 37 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 740ff5d5044..8683f0bdf53 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8639,8 +8639,9 @@ 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
- start.
+ processes. The default is typically 16 slots, but might be less if
+ your kernel settings will not support it (as determined during initdb).
+ This parameter can only be set at server start.
</para>
<para>
When changing this value, consider also adjusting
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 4e4b7ede190..f2b9d50e9b3 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -196,6 +196,7 @@ static char *pgdata_native;
/* defaults */
static int n_connections = 10;
+static int n_av_slots = 16;
static int n_buffers = 50;
static const char *dynamic_shared_memory_type = NULL;
static const char *default_timezone = NULL;
@@ -273,7 +274,8 @@ static void check_input(char *path);
static void write_version_file(const char *extrapath);
static void set_null_conf(void);
static void test_config_settings(void);
-static bool test_specific_config_settings(int test_conns, int test_buffs);
+static bool test_specific_config_settings(int test_conns, int test_av_slots,
+ int test_buffs);
static void setup_config(void);
static void bootstrap_template1(void);
static void setup_auth(FILE *cmdfd);
@@ -1118,6 +1120,18 @@ test_config_settings(void)
*/
#define MIN_BUFS_FOR_CONNS(nconns) ((nconns) * 10)
+ /*
+ * This macro defines the default value of autovacuum_worker_slots we want
+ * for a given max_connections value. Note that it has been carefully
+ * crafted to provide specific values for the associated values in
+ * trial_conns. We want it to return autovacuum_worker_slot's initial
+ * default value (16) for the maximum value in trial_conns (100), and we
+ * want it to return close to the minimum value we'd consider (3, which is
+ * the default of autovacuum_max_workers) for the minimum value in
+ * trial_conns (25).
+ */
+#define AV_SLOTS_FOR_CONNS(nconns) ((nconns) / 6)
+
static const int trial_conns[] = {
100, 50, 40, 30, 25
};
@@ -1145,7 +1159,8 @@ test_config_settings(void)
/*
* Probe for max_connections before shared_buffers, since it is subject to
- * more constraints than shared_buffers.
+ * more constraints than shared_buffers. We also choose the default
+ * autovacuum_worker_slots here.
*/
printf(_("selecting default \"max_connections\" ... "));
fflush(stdout);
@@ -1153,9 +1168,10 @@ test_config_settings(void)
for (i = 0; i < connslen; i++)
{
test_conns = trial_conns[i];
+ n_av_slots = AV_SLOTS_FOR_CONNS(test_conns);
test_buffs = MIN_BUFS_FOR_CONNS(test_conns);
- if (test_specific_config_settings(test_conns, test_buffs))
+ if (test_specific_config_settings(test_conns, n_av_slots, test_buffs))
{
ok_buffers = test_buffs;
break;
@@ -1167,6 +1183,13 @@ test_config_settings(void)
printf("%d\n", n_connections);
+ /*
+ * We chose the default for autovacuum_worker_slots during the
+ * max_connections tests above, but we print a progress message anyway.
+ */
+ printf(_("selecting default \"autovacuum_worker_slots\" ... %d\n"),
+ n_av_slots);
+
printf(_("selecting default \"shared_buffers\" ... "));
fflush(stdout);
@@ -1180,7 +1203,7 @@ test_config_settings(void)
break;
}
- if (test_specific_config_settings(n_connections, test_buffs))
+ if (test_specific_config_settings(n_connections, n_av_slots, test_buffs))
break;
}
n_buffers = test_buffs;
@@ -1200,7 +1223,7 @@ test_config_settings(void)
* Test a specific combination of configuration settings.
*/
static bool
-test_specific_config_settings(int test_conns, int test_buffs)
+test_specific_config_settings(int test_conns, int test_av_slots, int test_buffs)
{
PQExpBufferData cmd;
_stringlist *gnames,
@@ -1213,10 +1236,11 @@ test_specific_config_settings(int test_conns, int test_buffs)
printfPQExpBuffer(&cmd,
"\"%s\" --check %s %s "
"-c max_connections=%d "
+ "-c autovacuum_worker_slots=%d "
"-c shared_buffers=%d "
"-c dynamic_shared_memory_type=%s",
backend_exec, boot_options, extra_options,
- test_conns, test_buffs,
+ test_conns, test_av_slots, test_buffs,
dynamic_shared_memory_type);
/* Add any user-given setting overrides */
@@ -1280,6 +1304,10 @@ setup_config(void)
conflines = replace_guc_value(conflines, "max_connections",
repltok, false);
+ snprintf(repltok, sizeof(repltok), "%d", n_av_slots);
+ conflines = replace_guc_value(conflines, "autovacuum_worker_slots",
+ repltok, false);
+
if ((n_buffers * (BLCKSZ / 1024)) % 1024 == 0)
snprintf(repltok, sizeof(repltok), "%dMB",
(n_buffers * (BLCKSZ / 1024)) / 1024);
--
2.39.5 (Apple Git-154)
Attachments:
[text/plain] v3-0001-Lower-default-value-of-autovacuum_worker_slots-in.patch (5.6K, ../Z31jI10tLLd6thfu@nathan/2-v3-0001-Lower-default-value-of-autovacuum_worker_slots-in.patch)
download | inline diff:
From d8b9fe6d3c166c14d9a8d17f43418be33c4e0784 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 7 Jan 2025 11:15:22 -0600
Subject: [PATCH v3 1/1] Lower default value of autovacuum_worker_slots in
initdb as needed.
TODO
Reported-by: Tom Lane
Suggested-by: Andres Freund
Reviewed-by: Tom Lane
Discussion: https://postgr.es/m/1346002.1736198977%40sss.pgh.pa.us
---
doc/src/sgml/config.sgml | 5 +++--
src/bin/initdb/initdb.c | 40 ++++++++++++++++++++++++++++++++++------
2 files changed, 37 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 740ff5d5044..8683f0bdf53 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8639,8 +8639,9 @@ 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
- start.
+ processes. The default is typically 16 slots, but might be less if
+ your kernel settings will not support it (as determined during initdb).
+ This parameter can only be set at server start.
</para>
<para>
When changing this value, consider also adjusting
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 4e4b7ede190..f2b9d50e9b3 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -196,6 +196,7 @@ static char *pgdata_native;
/* defaults */
static int n_connections = 10;
+static int n_av_slots = 16;
static int n_buffers = 50;
static const char *dynamic_shared_memory_type = NULL;
static const char *default_timezone = NULL;
@@ -273,7 +274,8 @@ static void check_input(char *path);
static void write_version_file(const char *extrapath);
static void set_null_conf(void);
static void test_config_settings(void);
-static bool test_specific_config_settings(int test_conns, int test_buffs);
+static bool test_specific_config_settings(int test_conns, int test_av_slots,
+ int test_buffs);
static void setup_config(void);
static void bootstrap_template1(void);
static void setup_auth(FILE *cmdfd);
@@ -1118,6 +1120,18 @@ test_config_settings(void)
*/
#define MIN_BUFS_FOR_CONNS(nconns) ((nconns) * 10)
+ /*
+ * This macro defines the default value of autovacuum_worker_slots we want
+ * for a given max_connections value. Note that it has been carefully
+ * crafted to provide specific values for the associated values in
+ * trial_conns. We want it to return autovacuum_worker_slot's initial
+ * default value (16) for the maximum value in trial_conns (100), and we
+ * want it to return close to the minimum value we'd consider (3, which is
+ * the default of autovacuum_max_workers) for the minimum value in
+ * trial_conns (25).
+ */
+#define AV_SLOTS_FOR_CONNS(nconns) ((nconns) / 6)
+
static const int trial_conns[] = {
100, 50, 40, 30, 25
};
@@ -1145,7 +1159,8 @@ test_config_settings(void)
/*
* Probe for max_connections before shared_buffers, since it is subject to
- * more constraints than shared_buffers.
+ * more constraints than shared_buffers. We also choose the default
+ * autovacuum_worker_slots here.
*/
printf(_("selecting default \"max_connections\" ... "));
fflush(stdout);
@@ -1153,9 +1168,10 @@ test_config_settings(void)
for (i = 0; i < connslen; i++)
{
test_conns = trial_conns[i];
+ n_av_slots = AV_SLOTS_FOR_CONNS(test_conns);
test_buffs = MIN_BUFS_FOR_CONNS(test_conns);
- if (test_specific_config_settings(test_conns, test_buffs))
+ if (test_specific_config_settings(test_conns, n_av_slots, test_buffs))
{
ok_buffers = test_buffs;
break;
@@ -1167,6 +1183,13 @@ test_config_settings(void)
printf("%d\n", n_connections);
+ /*
+ * We chose the default for autovacuum_worker_slots during the
+ * max_connections tests above, but we print a progress message anyway.
+ */
+ printf(_("selecting default \"autovacuum_worker_slots\" ... %d\n"),
+ n_av_slots);
+
printf(_("selecting default \"shared_buffers\" ... "));
fflush(stdout);
@@ -1180,7 +1203,7 @@ test_config_settings(void)
break;
}
- if (test_specific_config_settings(n_connections, test_buffs))
+ if (test_specific_config_settings(n_connections, n_av_slots, test_buffs))
break;
}
n_buffers = test_buffs;
@@ -1200,7 +1223,7 @@ test_config_settings(void)
* Test a specific combination of configuration settings.
*/
static bool
-test_specific_config_settings(int test_conns, int test_buffs)
+test_specific_config_settings(int test_conns, int test_av_slots, int test_buffs)
{
PQExpBufferData cmd;
_stringlist *gnames,
@@ -1213,10 +1236,11 @@ test_specific_config_settings(int test_conns, int test_buffs)
printfPQExpBuffer(&cmd,
"\"%s\" --check %s %s "
"-c max_connections=%d "
+ "-c autovacuum_worker_slots=%d "
"-c shared_buffers=%d "
"-c dynamic_shared_memory_type=%s",
backend_exec, boot_options, extra_options,
- test_conns, test_buffs,
+ test_conns, test_av_slots, test_buffs,
dynamic_shared_memory_type);
/* Add any user-given setting overrides */
@@ -1280,6 +1304,10 @@ setup_config(void)
conflines = replace_guc_value(conflines, "max_connections",
repltok, false);
+ snprintf(repltok, sizeof(repltok), "%d", n_av_slots);
+ conflines = replace_guc_value(conflines, "autovacuum_worker_slots",
+ repltok, false);
+
if ((n_buffers * (BLCKSZ / 1024)) % 1024 == 0)
snprintf(repltok, sizeof(repltok), "%dMB",
(n_buffers * (BLCKSZ / 1024)) / 1024);
--
2.39.5 (Apple Git-154)
view thread (71+ messages) latest in thread
Message-ID: <Z31jI10tLLd6thfu@nathan>
Permalink: ../Z31jI10tLLd6thfu@nathan/
Also on: postgresql.org/message-id/Z31jI10tLLd6thfu@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: <Z31jI10tLLd6thfu@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