From: 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: Mon, 6 Jan 2025 17:20:22 -0600
Message-ID: <Z3xlNmC3qtwf1lz1@nathan> (raw)
In-Reply-To: <1353350.1736202977@sss.pgh.pa.us>
References: <ZsY96PvqkoJaC2Y-@nathan>
<ZzzMcv0lAdY4OzGi@nathan>
<Z2XJjI8fO8ksOv4E@nathan>
<Z3v8UFk0sOgFgWeZ@nathan>
<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>
On Mon, Jan 06, 2025 at 05:36:17PM -0500, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
>> How about trying the higher setting first in initdb? On any sane system
>> that won't cost anything because it'll succeed with the higher value.
>
> That might be a good compromise.
+1, I like the idea.
> You'd have to think about how
> it should interact with initdb's probes for workable values of
> max_connections. My first thought about that is to have initdb
> set autovacuum_worker_slots to max_connections / 8 or thereabouts
> as it works down the list of max_connections values to try. Or
> you could do something more complicated, but I don't see a reason
> to make it too complex.
My first instinct was just to set it to the lowest default we'd consider
during the max_connections tests (which I'm assuming is 3 due to the
current default for autovacuum_max_workers). That way, the max_connections
default won't change from version to version on affected systems, but you
might get some extra autovacuum slots.
--
nathan
From e35b4c5425cbe1120d3c38619be4f1523ae1f2c9 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 6 Jan 2025 17:14:54 -0600
Subject: [PATCH v1 1/1] Lower default value of autovacuum_worker_slots in
initdb as needed.
---
doc/src/sgml/config.sgml | 5 +++--
src/bin/initdb/initdb.c | 41 +++++++++++++++++++++++++++++++++++-----
2 files changed, 39 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgmlindex 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.cindex 4e4b7ede190..1214f1b492a 100644--- a/src/bin/initdb/initdb.c+++ b/src/bin/initdb/initdb.c@@ -197,6 +197,7 @@ static char *pgdata_native;
/* defaults */
static int n_connections = 10;
static int n_buffers = 50;
+static int n_av_slots = 16;
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_buffs,+ int test_av_slots);
static void setup_config(void);
static void bootstrap_template1(void);
static void setup_auth(FILE *cmdfd);
@@ -1118,6 +1120,13 @@ test_config_settings(void)
*/
#define MIN_BUFS_FOR_CONNS(nconns) ((nconns) * 10)
+ /*+ * This macro defines the minimum default autovacuum_worker_slots we are+ * willing to consider. It should be kept >= the default for+ * autovacuum_max_workers.+ */+#define MIN_AV_WORKER_SLOTS (3)+
static const int trial_conns[] = {
100, 50, 40, 30, 25
};
@@ -1155,7 +1164,9 @@ test_config_settings(void)
test_conns = trial_conns[i];
test_buffs = MIN_BUFS_FOR_CONNS(test_conns);
- if (test_specific_config_settings(test_conns, test_buffs))+ if (test_specific_config_settings(test_conns,+ test_buffs,+ MIN_AV_WORKER_SLOTS))
{
ok_buffers = test_buffs;
break;
@@ -1180,7 +1191,9 @@ test_config_settings(void)
break;
}
- if (test_specific_config_settings(n_connections, test_buffs))+ if (test_specific_config_settings(n_connections,+ test_buffs,+ MIN_AV_WORKER_SLOTS))
break;
}
n_buffers = test_buffs;
@@ -1190,6 +1203,19 @@ test_config_settings(void)
else
printf("%dkB\n", n_buffers * (BLCKSZ / 1024));
+ printf(_("selecting default \"autovacuum_worker_slots\" ... "));+ fflush(stdout);++ for (; n_av_slots > MIN_AV_WORKER_SLOTS; n_av_slots--)+ {+ if (test_specific_config_settings(n_connections,+ n_buffers,+ n_av_slots))+ break;+ }++ printf("%d\n", n_av_slots);+
printf(_("selecting default time zone ... "));
fflush(stdout);
default_timezone = select_default_timezone(share_path);
@@ -1200,7 +1226,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_buffs, int test_av_slots)
{
PQExpBufferData cmd;
_stringlist *gnames,
@@ -1214,9 +1240,10 @@ test_specific_config_settings(int test_conns, int test_buffs)
"\"%s\" --check %s %s "
"-c max_connections=%d "
"-c shared_buffers=%d "
+ "-c autovacuum_worker_slots=%d "
"-c dynamic_shared_memory_type=%s",
backend_exec, boot_options, extra_options,
- test_conns, test_buffs,+ test_conns, test_buffs, test_av_slots,
dynamic_shared_memory_type);
/* Add any user-given setting overrides */
@@ -1289,6 +1316,10 @@ setup_config(void)
conflines = replace_guc_value(conflines, "shared_buffers",
repltok, false);
+ snprintf(repltok, sizeof(repltok), "%d", n_av_slots);+ conflines = replace_guc_value(conflines, "autovacuum_worker_slots",+ repltok, false);+
conflines = replace_guc_value(conflines, "lc_messages",
lc_messages, false);
--
2.39.5 (Apple Git-154)
Attachments:
[text/plain] v1-0001-Lower-default-value-of-autovacuum_worker_slots-in.patch (4.7K, ../Z3xlNmC3qtwf1lz1@nathan/2-v1-0001-Lower-default-value-of-autovacuum_worker_slots-in.patch)
download | inline diff:
From e35b4c5425cbe1120d3c38619be4f1523ae1f2c9 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 6 Jan 2025 17:14:54 -0600
Subject: [PATCH v1 1/1] Lower default value of autovacuum_worker_slots in
initdb as needed.
---
doc/src/sgml/config.sgml | 5 +++--
src/bin/initdb/initdb.c | 41 +++++++++++++++++++++++++++++++++++-----
2 files changed, 39 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgmlindex 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.cindex 4e4b7ede190..1214f1b492a 100644--- a/src/bin/initdb/initdb.c+++ b/src/bin/initdb/initdb.c@@ -197,6 +197,7 @@ static char *pgdata_native;
/* defaults */
static int n_connections = 10;
static int n_buffers = 50;
+static int n_av_slots = 16;
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_buffs,+ int test_av_slots);
static void setup_config(void);
static void bootstrap_template1(void);
static void setup_auth(FILE *cmdfd);
@@ -1118,6 +1120,13 @@ test_config_settings(void)
*/
#define MIN_BUFS_FOR_CONNS(nconns) ((nconns) * 10)
+ /*+ * This macro defines the minimum default autovacuum_worker_slots we are+ * willing to consider. It should be kept >= the default for+ * autovacuum_max_workers.+ */+#define MIN_AV_WORKER_SLOTS (3)+
static const int trial_conns[] = {
100, 50, 40, 30, 25
};
@@ -1155,7 +1164,9 @@ test_config_settings(void)
test_conns = trial_conns[i];
test_buffs = MIN_BUFS_FOR_CONNS(test_conns);
- if (test_specific_config_settings(test_conns, test_buffs))+ if (test_specific_config_settings(test_conns,+ test_buffs,+ MIN_AV_WORKER_SLOTS))
{
ok_buffers = test_buffs;
break;
@@ -1180,7 +1191,9 @@ test_config_settings(void)
break;
}
- if (test_specific_config_settings(n_connections, test_buffs))+ if (test_specific_config_settings(n_connections,+ test_buffs,+ MIN_AV_WORKER_SLOTS))
break;
}
n_buffers = test_buffs;
@@ -1190,6 +1203,19 @@ test_config_settings(void)
else
printf("%dkB\n", n_buffers * (BLCKSZ / 1024));
+ printf(_("selecting default \"autovacuum_worker_slots\" ... "));+ fflush(stdout);++ for (; n_av_slots > MIN_AV_WORKER_SLOTS; n_av_slots--)+ {+ if (test_specific_config_settings(n_connections,+ n_buffers,+ n_av_slots))+ break;+ }++ printf("%d\n", n_av_slots);+
printf(_("selecting default time zone ... "));
fflush(stdout);
default_timezone = select_default_timezone(share_path);
@@ -1200,7 +1226,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_buffs, int test_av_slots)
{
PQExpBufferData cmd;
_stringlist *gnames,
@@ -1214,9 +1240,10 @@ test_specific_config_settings(int test_conns, int test_buffs)
"\"%s\" --check %s %s "
"-c max_connections=%d "
"-c shared_buffers=%d "
+ "-c autovacuum_worker_slots=%d "
"-c dynamic_shared_memory_type=%s",
backend_exec, boot_options, extra_options,
- test_conns, test_buffs,+ test_conns, test_buffs, test_av_slots,
dynamic_shared_memory_type);
/* Add any user-given setting overrides */
@@ -1289,6 +1316,10 @@ setup_config(void)
conflines = replace_guc_value(conflines, "shared_buffers",
repltok, false);
+ snprintf(repltok, sizeof(repltok), "%d", n_av_slots);+ conflines = replace_guc_value(conflines, "autovacuum_worker_slots",+ repltok, false);+
conflines = replace_guc_value(conflines, "lc_messages",
lc_messages, false);
--
2.39.5 (Apple Git-154)
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: <Z3xlNmC3qtwf1lz1@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox