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 <nat...@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)
