agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nathan Bossart <nathandbossart@gmail.com>
To: 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: Fri, 21 Jun 2024 15:44:07 -0500
Message-ID: <ZnXmF2FVugUVjSxN@nathan> (raw)
In-Reply-To: <ZnIpuKIW4RO70XeJ@nathan>
References: <20240517021646.GA1745636@nathanxps13>
<Zl4Q7YGslvdL0eYj@nathan>
<20240603190852.jcibstxflb33hjvu@awork3.anarazel.de>
<Zl4ZTRdx38sDktRs@nathan>
<20240603232427.pjiyqggsr7duccr5@awork3.anarazel.de>
<ZnHZMIJM6rgMVvvM@nathan>
<20240618204334.i5ar2fie4vbb3fhm@awork3.anarazel.de>
<ZnH3dQHtE_k6S2sX@nathan>
<20240618213331.ef2spg3nasksisbi@awork3.anarazel.de>
<ZnIpuKIW4RO70XeJ@nathan>
On Tue, Jun 18, 2024 at 07:43:36PM -0500, Nathan Bossart wrote:
> On Tue, Jun 18, 2024 at 02:33:31PM -0700, Andres Freund wrote:
>> Another one:
>>
>> Have a general cap of 64, but additionally limit it to something like
>> max(1, min(WORKER_CAP, max_connections / 4))
>>
>> so that cases like tap tests don't end up allocating vastly more worker slots
>> than actual connection slots.
>
> That's a clever idea. My only concern would be that we are tethering two
> parameters that aren't super closely related, but I'm unsure whether it
> would cause any problems in practice.
Here is an attempt at doing this. I've added 0001 [0] and 0002 [1] as
prerequisite patches, which helps simplify 0003 a bit. It probably doesn't
work correctly for EXEC_BACKEND builds yet.
I'm still not sure about this approach. At the moment, I'm leaning towards
something more like v2 [2] where the upper limit is a PGC_POSTMASTER GUC
(that we would set very low for TAP tests).
[0] https://commitfest.postgresql.org/48/4998/
[1] https://commitfest.postgresql.org/48/5059/
[2] https://postgr.es/m/20240419154322.GA3988554%40nathanxps13
--
nathan
From 69245426585052af1317c1bf3e564cae0c019f52 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 21 May 2024 14:02:22 -0500
Subject: [PATCH v5 1/3] add num_os_semaphores GUC
---
doc/src/sgml/config.sgml | 14 +++++++++++
doc/src/sgml/runtime.sgml | 39 ++++++++++++++++-------------
src/backend/storage/ipc/ipci.c | 6 ++++-
src/backend/utils/misc/guc_tables.c | 12 +++++++++
4 files changed, 53 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0c7a9082c5..087385cb4e 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -11234,6 +11234,20 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
</listitem>
</varlistentry>
+ <varlistentry id="guc-num-os-semaphores" xreflabel="num_os_semaphores">
+ <term><varname>num_os_semaphores</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>num_os_semaphores</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Reports the number of semaphores that are needed for the server based
+ on the number of allowed connections, worker processes, etc.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-ssl-library" xreflabel="ssl_library">
<term><varname>ssl_library</varname> (<type>string</type>)
<indexterm>
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 2f7c618886..26f3cbe555 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -781,13 +781,13 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such
<row>
<entry><varname>SEMMNI</varname></entry>
<entry>Maximum number of semaphore identifiers (i.e., sets)</entry>
- <entry>at least <literal>ceil((max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes + 7) / 16)</literal> plus room for other applications</entry>
+ <entry>at least <literal>ceil(num_os_semaphores / 16)</literal> plus room for other applications</entry>
</row>
<row>
<entry><varname>SEMMNS</varname></entry>
<entry>Maximum number of semaphores system-wide</entry>
- <entry><literal>ceil((max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes + 7) / 16) * 17</literal> plus room for other applications</entry>
+ <entry><literal>ceil(num_os_semaphores / 16) * 17</literal> plus room for other applications</entry>
</row>
<row>
@@ -836,30 +836,38 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such
<para>
When using System V semaphores,
- <productname>PostgreSQL</productname> uses one semaphore per allowed connection
- (<xref linkend="guc-max-connections"/>), allowed autovacuum worker process
- (<xref linkend="guc-autovacuum-max-workers"/>), allowed WAL sender process
- (<xref linkend="guc-max-wal-senders"/>), and allowed background
- process (<xref linkend="guc-max-worker-processes"/>), in sets of 16.
+ <productname>PostgreSQL</productname> uses one semaphore per allowed connection,
+ worker process, etc., in sets of 16.
Each such set will
also contain a 17th semaphore which contains a <quote>magic
number</quote>, to detect collision with semaphore sets used by
other applications. The maximum number of semaphores in the system
is set by <varname>SEMMNS</varname>, which consequently must be at least
- as high as <varname>max_connections</varname> plus
- <varname>autovacuum_max_workers</varname> plus <varname>max_wal_senders</varname>,
- plus <varname>max_worker_processes</varname>, plus one extra for each 16
- allowed connections plus workers (see the formula in <xref
+ as high as <xref linkend="guc-num-os-semaphores"/> plus one extra for
+ each set of 16 required semaphores (see the formula in <xref
linkend="sysvipc-parameters"/>). The parameter <varname>SEMMNI</varname>
determines the limit on the number of semaphore sets that can
exist on the system at one time. Hence this parameter must be at
- least <literal>ceil((max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes + 7) / 16)</literal>.
+ least <literal>ceil(num_os_semaphores / 16)</literal>.
Lowering the number
of allowed connections is a temporary workaround for failures,
which are usually confusingly worded <quote>No space
left on device</quote>, from the function <function>semget</function>.
</para>
+ <para>
+ The number of semaphores required by <productname>PostgreSQL</productname>
+ is provided by the runtime-computed parameter
+ <varname>num_os_semaphores</varname>, which can be determined before
+ starting the server with a <command>postgres</command> command like:
+<programlisting>
+$ <userinput>postgres -D $PGDATA -C num_os_semaphores</userinput>
+</programlisting>
+ The value of <varname>num_os_semaphores</varname> should be input into
+ the aforementioned formulas to determine appropriate values for
+ <varname>SEMMNI</varname> and <varname>SEMMNS</varname>.
+ </para>
+
<para>
In some cases it might also be necessary to increase
<varname>SEMMAP</varname> to be at least on the order of
@@ -882,11 +890,8 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such
<para>
When using POSIX semaphores, the number of semaphores needed is the
- same as for System V, that is one semaphore per allowed connection
- (<xref linkend="guc-max-connections"/>), allowed autovacuum worker process
- (<xref linkend="guc-autovacuum-max-workers"/>), allowed WAL sender process
- (<xref linkend="guc-max-wal-senders"/>), and allowed background
- process (<xref linkend="guc-max-worker-processes"/>).
+ same as for System V, that is one semaphore per allowed connection,
+ worker process, etc.
On the platforms where this option is preferred, there is no specific
kernel limit on the number of POSIX semaphores.
</para>
diff --git a/src/backend/storage/ipc/ipci.c b/src/backend/storage/ipc/ipci.c
index 521ed5418c..0314513aa6 100644
--- a/src/backend/storage/ipc/ipci.c
+++ b/src/backend/storage/ipc/ipci.c
@@ -372,11 +372,12 @@ InitializeShmemGUCs(void)
Size size_b;
Size size_mb;
Size hp_size;
+ int num_semas;
/*
* Calculate the shared memory size and round up to the nearest megabyte.
*/
- size_b = CalculateShmemSize(NULL);
+ size_b = CalculateShmemSize(&num_semas);
size_mb = add_size(size_b, (1024 * 1024) - 1) / (1024 * 1024);
sprintf(buf, "%zu", size_mb);
SetConfigOption("shared_memory_size", buf,
@@ -395,4 +396,7 @@ InitializeShmemGUCs(void)
SetConfigOption("shared_memory_size_in_huge_pages", buf,
PGC_INTERNAL, PGC_S_DYNAMIC_DEFAULT);
}
+
+ sprintf(buf, "%d", num_semas);
+ SetConfigOption("num_os_semaphores", buf, PGC_INTERNAL, PGC_S_DYNAMIC_DEFAULT);
}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 46c258be28..80e77cbac9 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -599,6 +599,7 @@ static int segment_size;
static int shared_memory_size_mb;
static int shared_memory_size_in_huge_pages;
static int wal_block_size;
+static int num_os_semaphores;
static bool data_checksums;
static bool integer_datetimes;
@@ -2291,6 +2292,17 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"num_os_semaphores", PGC_INTERNAL, PRESET_OPTIONS,
+ gettext_noop("Shows the number of semaphores required for the server."),
+ NULL,
+ GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_RUNTIME_COMPUTED
+ },
+ &num_os_semaphores,
+ 0, 0, INT_MAX,
+ NULL, NULL, NULL
+ },
+
{
{"commit_timestamp_buffers", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Sets the size of the dedicated buffer pool used for the commit timestamp cache."),
--
2.39.3 (Apple Git-146)
From b65ae84fc36c440451c42ef61b383bcd98a825bd Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 19 Jun 2024 13:39:18 -0500
Subject: [PATCH v5 2/3] remove check hooks for GUCs that contribute to
MaxBackends
---
src/backend/utils/init/postinit.c | 57 ++++-------------------------
src/backend/utils/misc/guc_tables.c | 8 ++--
src/include/utils/guc_hooks.h | 6 ---
3 files changed, 11 insertions(+), 60 deletions(-)
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 0805398e24..8a629982c4 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -580,57 +580,14 @@ InitializeMaxBackends(void)
MaxBackends = MaxConnections + autovacuum_max_workers + 1 +
max_worker_processes + max_wal_senders;
- /* internal error because the values were all checked previously */
if (MaxBackends > MAX_BACKENDS)
- elog(ERROR, "too many backends configured");
-}
-
-/*
- * GUC check_hook for max_connections
- */
-bool
-check_max_connections(int *newval, void **extra, GucSource source)
-{
- if (*newval + autovacuum_max_workers + 1 +
- max_worker_processes + max_wal_senders > MAX_BACKENDS)
- return false;
- return true;
-}
-
-/*
- * GUC check_hook for autovacuum_max_workers
- */
-bool
-check_autovacuum_max_workers(int *newval, void **extra, GucSource source)
-{
- if (MaxConnections + *newval + 1 +
- max_worker_processes + max_wal_senders > MAX_BACKENDS)
- return false;
- return true;
-}
-
-/*
- * GUC check_hook for max_worker_processes
- */
-bool
-check_max_worker_processes(int *newval, void **extra, GucSource source)
-{
- if (MaxConnections + autovacuum_max_workers + 1 +
- *newval + max_wal_senders > MAX_BACKENDS)
- return false;
- return true;
-}
-
-/*
- * GUC check_hook for max_wal_senders
- */
-bool
-check_max_wal_senders(int *newval, void **extra, GucSource source)
-{
- if (MaxConnections + autovacuum_max_workers + 1 +
- max_worker_processes + *newval > MAX_BACKENDS)
- return false;
- return true;
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("too many backends configured"),
+ errdetail("\"max_connections\" (%d) plus \"autovacuum_max_workers\" (%d) plus \"max_worker_processes\" (%d) plus \"max_wal_senders\" (%d) must be less than %d.",
+ MaxConnections, autovacuum_max_workers,
+ max_worker_processes, max_wal_senders,
+ MAX_BACKENDS - 1)));
}
/*
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 80e77cbac9..57df7767ad 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2208,7 +2208,7 @@ struct config_int ConfigureNamesInt[] =
},
&MaxConnections,
100, 1, MAX_BACKENDS,
- check_max_connections, NULL, NULL
+ NULL, NULL, NULL
},
{
@@ -2935,7 +2935,7 @@ struct config_int ConfigureNamesInt[] =
},
&max_wal_senders,
10, 0, MAX_BACKENDS,
- check_max_wal_senders, NULL, NULL
+ NULL, NULL, NULL
},
{
@@ -3165,7 +3165,7 @@ struct config_int ConfigureNamesInt[] =
},
&max_worker_processes,
8, 0, MAX_BACKENDS,
- check_max_worker_processes, NULL, NULL
+ NULL, NULL, NULL
},
{
@@ -3399,7 +3399,7 @@ struct config_int ConfigureNamesInt[] =
},
&autovacuum_max_workers,
3, 1, MAX_BACKENDS,
- check_autovacuum_max_workers, NULL, NULL
+ NULL, NULL, NULL
},
{
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index d64dc5fcdb..6304f0679b 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -29,8 +29,6 @@ extern bool check_application_name(char **newval, void **extra,
GucSource source);
extern void assign_application_name(const char *newval, void *extra);
extern const char *show_archive_command(void);
-extern bool check_autovacuum_max_workers(int *newval, void **extra,
- GucSource source);
extern bool check_autovacuum_work_mem(int *newval, void **extra,
GucSource source);
extern bool check_vacuum_buffer_usage_limit(int *newval, void **extra,
@@ -84,13 +82,9 @@ extern const char *show_log_timezone(void);
extern bool check_maintenance_io_concurrency(int *newval, void **extra,
GucSource source);
extern void assign_maintenance_io_concurrency(int newval, void *extra);
-extern bool check_max_connections(int *newval, void **extra, GucSource source);
-extern bool check_max_wal_senders(int *newval, void **extra, GucSource source);
extern bool check_max_slot_wal_keep_size(int *newval, void **extra,
GucSource source);
extern void assign_max_wal_size(int newval, void *extra);
-extern bool check_max_worker_processes(int *newval, void **extra,
- GucSource source);
extern bool check_max_stack_depth(int *newval, void **extra, GucSource source);
extern void assign_max_stack_depth(int newval, void *extra);
extern bool check_multixact_member_buffers(int *newval, void **extra,
--
2.39.3 (Apple Git-146)
From 48f7d56b99f2d98533c8767d5891a78c8411872d Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 21 Jun 2024 15:29:28 -0500
Subject: [PATCH v5 3/3] allow changing autovacuum_max_workers without
restarting
---
doc/src/sgml/config.sgml | 8 +++-
src/backend/postmaster/autovacuum.c | 25 ++++++++++---
src/backend/postmaster/postmaster.c | 2 +-
src/backend/storage/lmgr/proc.c | 4 +-
src/backend/utils/init/postinit.c | 37 +++++++++++++++++--
src/backend/utils/misc/guc_tables.c | 4 +-
src/backend/utils/misc/postgresql.conf.sample | 1 -
src/include/postmaster/autovacuum.h | 1 +
src/include/utils/guc_hooks.h | 2 +
9 files changed, 69 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 087385cb4e..289c2b63c4 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8554,7 +8554,13 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
<para>
Specifies the maximum number of autovacuum processes (other than the
autovacuum launcher) that may be running at any one time. The default
- is three. This parameter can only be set at server start.
+ is three. This parameter can only be set in the
+ <filename>postgresql.conf</filename> file or on the server command line.
+ </para>
+ <para>
+ This parameter is limited to 50% of
+ <xref linkend="guc-max-connections"/> (but no less than 1 or greater
+ than 64).
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 9a925a10cd..8bf95981a0 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -130,6 +130,7 @@ double autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
int Log_autovacuum_min_duration = 600000;
+int autovacuum_worker_slots = -1;
/* the minimum allowed time between two awakenings of the launcher */
#define MIN_AUTOVAC_SLEEPTIME 100.0 /* milliseconds */
@@ -347,6 +348,7 @@ static void autovac_report_activity(autovac_table *tab);
static void autovac_report_workitem(AutoVacuumWorkItem *workitem,
const char *nspname, const char *relname);
static void avl_sigusr2_handler(SIGNAL_ARGS);
+static bool av_worker_available(void);
@@ -575,7 +577,7 @@ AutoVacLauncherMain(char *startup_data, size_t startup_data_len)
* wakening conditions.
*/
- launcher_determine_sleep(!dlist_is_empty(&AutoVacuumShmem->av_freeWorkers),
+ launcher_determine_sleep(av_worker_available(),
false, &nap);
/*
@@ -636,7 +638,7 @@ AutoVacLauncherMain(char *startup_data, size_t startup_data_len)
current_time = GetCurrentTimestamp();
LWLockAcquire(AutovacuumLock, LW_SHARED);
- can_launch = !dlist_is_empty(&AutoVacuumShmem->av_freeWorkers);
+ can_launch = av_worker_available();
if (AutoVacuumShmem->av_startingWorker != NULL)
{
@@ -1087,7 +1089,7 @@ do_start_worker(void)
/* return quickly when there are no free workers */
LWLockAcquire(AutovacuumLock, LW_SHARED);
- if (dlist_is_empty(&AutoVacuumShmem->av_freeWorkers))
+ if (!av_worker_available())
{
LWLockRelease(AutovacuumLock);
return InvalidOid;
@@ -3265,7 +3267,7 @@ AutoVacuumShmemSize(void)
*/
size = sizeof(AutoVacuumShmemStruct);
size = MAXALIGN(size);
- size = add_size(size, mul_size(autovacuum_max_workers,
+ size = add_size(size, mul_size(autovacuum_worker_slots,
sizeof(WorkerInfoData)));
return size;
}
@@ -3302,7 +3304,7 @@ AutoVacuumShmemInit(void)
MAXALIGN(sizeof(AutoVacuumShmemStruct)));
/* initialize the WorkerInfo free list */
- for (i = 0; i < autovacuum_max_workers; i++)
+ for (i = 0; i < autovacuum_worker_slots; i++)
{
dlist_push_head(&AutoVacuumShmem->av_freeWorkers,
&worker[i].wi_links);
@@ -3341,3 +3343,16 @@ check_autovacuum_work_mem(int *newval, void **extra, GucSource source)
return true;
}
+
+static bool
+av_worker_available(void)
+{
+ int reserved = autovacuum_worker_slots - autovacuum_max_workers;
+ int count = 0;
+ dlist_iter iter;
+
+ dlist_foreach(iter, &AutoVacuumShmem->av_freeWorkers)
+ count++;
+
+ return count > reserved;
+}
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index bf0241aed0..2588a68fbc 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -4144,7 +4144,7 @@ CreateOptsFile(int argc, char *argv[], char *fullprogname)
int
MaxLivePostmasterChildren(void)
{
- return 2 * (MaxConnections + autovacuum_max_workers + 1 +
+ return 2 * (MaxConnections + autovacuum_worker_slots + 1 +
max_wal_senders + max_worker_processes);
}
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index ce29da9012..10e0b681cf 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -242,13 +242,13 @@ InitProcGlobal(void)
dlist_push_tail(&ProcGlobal->freeProcs, &proc->links);
proc->procgloballist = &ProcGlobal->freeProcs;
}
- else if (i < MaxConnections + autovacuum_max_workers + 1)
+ else if (i < MaxConnections + autovacuum_worker_slots + 1)
{
/* PGPROC for AV launcher/worker, add to autovacFreeProcs list */
dlist_push_tail(&ProcGlobal->autovacFreeProcs, &proc->links);
proc->procgloballist = &ProcGlobal->autovacFreeProcs;
}
- else if (i < MaxConnections + autovacuum_max_workers + 1 + max_worker_processes)
+ else if (i < MaxConnections + autovacuum_worker_slots + 1 + max_worker_processes)
{
/* PGPROC for bgworker, add to bgworkerFreeProcs list */
dlist_push_tail(&ProcGlobal->bgworkerFreeProcs, &proc->links);
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 8a629982c4..d206dd1533 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -576,20 +576,51 @@ InitializeMaxBackends(void)
{
Assert(MaxBackends == 0);
+ autovacuum_worker_slots = Max(1, Min(64, MaxConnections / 2));
+ if (autovacuum_max_workers > autovacuum_worker_slots)
+ {
+ /* keep in sync with check_autovacuum_max_workers() */
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid setting for \"autovacuum_max_workers\""),
+ errdetail("\"autovacuum_max_workers\" (%d) is limited to 50%% of \"max_connections\" (but no less than 1 or greater than 64) (%d)",
+ autovacuum_max_workers, autovacuum_worker_slots)));
+ }
+
/* the extra unit accounts for the autovacuum launcher */
- MaxBackends = MaxConnections + autovacuum_max_workers + 1 +
+ MaxBackends = MaxConnections + autovacuum_worker_slots + 1 +
max_worker_processes + max_wal_senders;
if (MaxBackends > MAX_BACKENDS)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("too many backends configured"),
- errdetail("\"max_connections\" (%d) plus \"autovacuum_max_workers\" (%d) plus \"max_worker_processes\" (%d) plus \"max_wal_senders\" (%d) must be less than %d.",
- MaxConnections, autovacuum_max_workers,
+ errdetail("\"max_connections\" (%d) plus autovacuum worker slots (%d) plus \"max_worker_processes\" (%d) plus \"max_wal_senders\" (%d) must be less than %d.",
+ MaxConnections, autovacuum_worker_slots,
max_worker_processes, max_wal_senders,
MAX_BACKENDS - 1)));
}
+/*
+ * GUC check_hook for autovacuum_max_workers
+ */
+bool
+check_autovacuum_max_workers(int *newval, void **extra, GucSource source)
+{
+ /*
+ * If autovacuum_worker_slots has not yet been initialized, just return
+ * true. We'll fail startup in PostmasterMain() if needed.
+ */
+ if (autovacuum_worker_slots == -1 || *newval <= autovacuum_worker_slots)
+ return true;
+
+ /* keep in sync with matching error message in InitializeMaxBackends() */
+ GUC_check_errmsg("invalid setting for \"autovacuum_max_workers\"");
+ GUC_check_errdetail("\"autovacuum_max_workers\" (%d) is limited to 50%% of \"max_connections\" (but no less than 1 or greater than 64) (%d)",
+ autovacuum_max_workers, autovacuum_worker_slots);
+ return false;
+}
+
/*
* Early initialization of a backend (either standalone or under postmaster).
* This happens even before InitPostgres.
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 57df7767ad..f1babff58e 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3393,13 +3393,13 @@ struct config_int ConfigureNamesInt[] =
},
{
/* see max_connections */
- {"autovacuum_max_workers", PGC_POSTMASTER, AUTOVACUUM,
+ {"autovacuum_max_workers", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Sets the maximum number of simultaneously running autovacuum worker processes."),
NULL
},
&autovacuum_max_workers,
3, 1, MAX_BACKENDS,
- NULL, NULL, NULL
+ check_autovacuum_max_workers, NULL, NULL
},
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e0567de219..fec719fe79 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -659,7 +659,6 @@
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
- # (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index cae1e8b329..2b2aa1b713 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -46,6 +46,7 @@ extern PGDLLIMPORT int autovacuum_vac_cost_limit;
extern PGDLLIMPORT int AutovacuumLauncherPid;
extern PGDLLIMPORT int Log_autovacuum_min_duration;
+extern PGDLLIMPORT int autovacuum_worker_slots;
/* Status inquiry functions */
extern bool AutoVacuumingActive(void);
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index 6304f0679b..b3e1f9f2cb 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -29,6 +29,8 @@ extern bool check_application_name(char **newval, void **extra,
GucSource source);
extern void assign_application_name(const char *newval, void *extra);
extern const char *show_archive_command(void);
+extern bool check_autovacuum_max_workers(int *newval, void **extra,
+ GucSource source);
extern bool check_autovacuum_work_mem(int *newval, void **extra,
GucSource source);
extern bool check_vacuum_buffer_usage_limit(int *newval, void **extra,
--
2.39.3 (Apple Git-146)
Attachments:
[text/plain] v5-0001-add-num_os_semaphores-GUC.patch (8.0K, ../ZnXmF2FVugUVjSxN@nathan/2-v5-0001-add-num_os_semaphores-GUC.patch)
download | inline diff:
From 69245426585052af1317c1bf3e564cae0c019f52 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 21 May 2024 14:02:22 -0500
Subject: [PATCH v5 1/3] add num_os_semaphores GUC
---
doc/src/sgml/config.sgml | 14 +++++++++++
doc/src/sgml/runtime.sgml | 39 ++++++++++++++++-------------
src/backend/storage/ipc/ipci.c | 6 ++++-
src/backend/utils/misc/guc_tables.c | 12 +++++++++
4 files changed, 53 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0c7a9082c5..087385cb4e 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -11234,6 +11234,20 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
</listitem>
</varlistentry>
+ <varlistentry id="guc-num-os-semaphores" xreflabel="num_os_semaphores">
+ <term><varname>num_os_semaphores</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>num_os_semaphores</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Reports the number of semaphores that are needed for the server based
+ on the number of allowed connections, worker processes, etc.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-ssl-library" xreflabel="ssl_library">
<term><varname>ssl_library</varname> (<type>string</type>)
<indexterm>
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 2f7c618886..26f3cbe555 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -781,13 +781,13 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such
<row>
<entry><varname>SEMMNI</varname></entry>
<entry>Maximum number of semaphore identifiers (i.e., sets)</entry>
- <entry>at least <literal>ceil((max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes + 7) / 16)</literal> plus room for other applications</entry>
+ <entry>at least <literal>ceil(num_os_semaphores / 16)</literal> plus room for other applications</entry>
</row>
<row>
<entry><varname>SEMMNS</varname></entry>
<entry>Maximum number of semaphores system-wide</entry>
- <entry><literal>ceil((max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes + 7) / 16) * 17</literal> plus room for other applications</entry>
+ <entry><literal>ceil(num_os_semaphores / 16) * 17</literal> plus room for other applications</entry>
</row>
<row>
@@ -836,30 +836,38 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such
<para>
When using System V semaphores,
- <productname>PostgreSQL</productname> uses one semaphore per allowed connection
- (<xref linkend="guc-max-connections"/>), allowed autovacuum worker process
- (<xref linkend="guc-autovacuum-max-workers"/>), allowed WAL sender process
- (<xref linkend="guc-max-wal-senders"/>), and allowed background
- process (<xref linkend="guc-max-worker-processes"/>), in sets of 16.
+ <productname>PostgreSQL</productname> uses one semaphore per allowed connection,
+ worker process, etc., in sets of 16.
Each such set will
also contain a 17th semaphore which contains a <quote>magic
number</quote>, to detect collision with semaphore sets used by
other applications. The maximum number of semaphores in the system
is set by <varname>SEMMNS</varname>, which consequently must be at least
- as high as <varname>max_connections</varname> plus
- <varname>autovacuum_max_workers</varname> plus <varname>max_wal_senders</varname>,
- plus <varname>max_worker_processes</varname>, plus one extra for each 16
- allowed connections plus workers (see the formula in <xref
+ as high as <xref linkend="guc-num-os-semaphores"/> plus one extra for
+ each set of 16 required semaphores (see the formula in <xref
linkend="sysvipc-parameters"/>). The parameter <varname>SEMMNI</varname>
determines the limit on the number of semaphore sets that can
exist on the system at one time. Hence this parameter must be at
- least <literal>ceil((max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes + 7) / 16)</literal>.
+ least <literal>ceil(num_os_semaphores / 16)</literal>.
Lowering the number
of allowed connections is a temporary workaround for failures,
which are usually confusingly worded <quote>No space
left on device</quote>, from the function <function>semget</function>.
</para>
+ <para>
+ The number of semaphores required by <productname>PostgreSQL</productname>
+ is provided by the runtime-computed parameter
+ <varname>num_os_semaphores</varname>, which can be determined before
+ starting the server with a <command>postgres</command> command like:
+<programlisting>
+$ <userinput>postgres -D $PGDATA -C num_os_semaphores</userinput>
+</programlisting>
+ The value of <varname>num_os_semaphores</varname> should be input into
+ the aforementioned formulas to determine appropriate values for
+ <varname>SEMMNI</varname> and <varname>SEMMNS</varname>.
+ </para>
+
<para>
In some cases it might also be necessary to increase
<varname>SEMMAP</varname> to be at least on the order of
@@ -882,11 +890,8 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such
<para>
When using POSIX semaphores, the number of semaphores needed is the
- same as for System V, that is one semaphore per allowed connection
- (<xref linkend="guc-max-connections"/>), allowed autovacuum worker process
- (<xref linkend="guc-autovacuum-max-workers"/>), allowed WAL sender process
- (<xref linkend="guc-max-wal-senders"/>), and allowed background
- process (<xref linkend="guc-max-worker-processes"/>).
+ same as for System V, that is one semaphore per allowed connection,
+ worker process, etc.
On the platforms where this option is preferred, there is no specific
kernel limit on the number of POSIX semaphores.
</para>
diff --git a/src/backend/storage/ipc/ipci.c b/src/backend/storage/ipc/ipci.c
index 521ed5418c..0314513aa6 100644
--- a/src/backend/storage/ipc/ipci.c
+++ b/src/backend/storage/ipc/ipci.c
@@ -372,11 +372,12 @@ InitializeShmemGUCs(void)
Size size_b;
Size size_mb;
Size hp_size;
+ int num_semas;
/*
* Calculate the shared memory size and round up to the nearest megabyte.
*/
- size_b = CalculateShmemSize(NULL);
+ size_b = CalculateShmemSize(&num_semas);
size_mb = add_size(size_b, (1024 * 1024) - 1) / (1024 * 1024);
sprintf(buf, "%zu", size_mb);
SetConfigOption("shared_memory_size", buf,
@@ -395,4 +396,7 @@ InitializeShmemGUCs(void)
SetConfigOption("shared_memory_size_in_huge_pages", buf,
PGC_INTERNAL, PGC_S_DYNAMIC_DEFAULT);
}
+
+ sprintf(buf, "%d", num_semas);
+ SetConfigOption("num_os_semaphores", buf, PGC_INTERNAL, PGC_S_DYNAMIC_DEFAULT);
}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 46c258be28..80e77cbac9 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -599,6 +599,7 @@ static int segment_size;
static int shared_memory_size_mb;
static int shared_memory_size_in_huge_pages;
static int wal_block_size;
+static int num_os_semaphores;
static bool data_checksums;
static bool integer_datetimes;
@@ -2291,6 +2292,17 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"num_os_semaphores", PGC_INTERNAL, PRESET_OPTIONS,
+ gettext_noop("Shows the number of semaphores required for the server."),
+ NULL,
+ GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_RUNTIME_COMPUTED
+ },
+ &num_os_semaphores,
+ 0, 0, INT_MAX,
+ NULL, NULL, NULL
+ },
+
{
{"commit_timestamp_buffers", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Sets the size of the dedicated buffer pool used for the commit timestamp cache."),
--
2.39.3 (Apple Git-146)
[text/plain] v5-0002-remove-check-hooks-for-GUCs-that-contribute-to-Ma.patch (4.9K, ../ZnXmF2FVugUVjSxN@nathan/3-v5-0002-remove-check-hooks-for-GUCs-that-contribute-to-Ma.patch)
download | inline diff:
From b65ae84fc36c440451c42ef61b383bcd98a825bd Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 19 Jun 2024 13:39:18 -0500
Subject: [PATCH v5 2/3] remove check hooks for GUCs that contribute to
MaxBackends
---
src/backend/utils/init/postinit.c | 57 ++++-------------------------
src/backend/utils/misc/guc_tables.c | 8 ++--
src/include/utils/guc_hooks.h | 6 ---
3 files changed, 11 insertions(+), 60 deletions(-)
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 0805398e24..8a629982c4 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -580,57 +580,14 @@ InitializeMaxBackends(void)
MaxBackends = MaxConnections + autovacuum_max_workers + 1 +
max_worker_processes + max_wal_senders;
- /* internal error because the values were all checked previously */
if (MaxBackends > MAX_BACKENDS)
- elog(ERROR, "too many backends configured");
-}
-
-/*
- * GUC check_hook for max_connections
- */
-bool
-check_max_connections(int *newval, void **extra, GucSource source)
-{
- if (*newval + autovacuum_max_workers + 1 +
- max_worker_processes + max_wal_senders > MAX_BACKENDS)
- return false;
- return true;
-}
-
-/*
- * GUC check_hook for autovacuum_max_workers
- */
-bool
-check_autovacuum_max_workers(int *newval, void **extra, GucSource source)
-{
- if (MaxConnections + *newval + 1 +
- max_worker_processes + max_wal_senders > MAX_BACKENDS)
- return false;
- return true;
-}
-
-/*
- * GUC check_hook for max_worker_processes
- */
-bool
-check_max_worker_processes(int *newval, void **extra, GucSource source)
-{
- if (MaxConnections + autovacuum_max_workers + 1 +
- *newval + max_wal_senders > MAX_BACKENDS)
- return false;
- return true;
-}
-
-/*
- * GUC check_hook for max_wal_senders
- */
-bool
-check_max_wal_senders(int *newval, void **extra, GucSource source)
-{
- if (MaxConnections + autovacuum_max_workers + 1 +
- max_worker_processes + *newval > MAX_BACKENDS)
- return false;
- return true;
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("too many backends configured"),
+ errdetail("\"max_connections\" (%d) plus \"autovacuum_max_workers\" (%d) plus \"max_worker_processes\" (%d) plus \"max_wal_senders\" (%d) must be less than %d.",
+ MaxConnections, autovacuum_max_workers,
+ max_worker_processes, max_wal_senders,
+ MAX_BACKENDS - 1)));
}
/*
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 80e77cbac9..57df7767ad 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2208,7 +2208,7 @@ struct config_int ConfigureNamesInt[] =
},
&MaxConnections,
100, 1, MAX_BACKENDS,
- check_max_connections, NULL, NULL
+ NULL, NULL, NULL
},
{
@@ -2935,7 +2935,7 @@ struct config_int ConfigureNamesInt[] =
},
&max_wal_senders,
10, 0, MAX_BACKENDS,
- check_max_wal_senders, NULL, NULL
+ NULL, NULL, NULL
},
{
@@ -3165,7 +3165,7 @@ struct config_int ConfigureNamesInt[] =
},
&max_worker_processes,
8, 0, MAX_BACKENDS,
- check_max_worker_processes, NULL, NULL
+ NULL, NULL, NULL
},
{
@@ -3399,7 +3399,7 @@ struct config_int ConfigureNamesInt[] =
},
&autovacuum_max_workers,
3, 1, MAX_BACKENDS,
- check_autovacuum_max_workers, NULL, NULL
+ NULL, NULL, NULL
},
{
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index d64dc5fcdb..6304f0679b 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -29,8 +29,6 @@ extern bool check_application_name(char **newval, void **extra,
GucSource source);
extern void assign_application_name(const char *newval, void *extra);
extern const char *show_archive_command(void);
-extern bool check_autovacuum_max_workers(int *newval, void **extra,
- GucSource source);
extern bool check_autovacuum_work_mem(int *newval, void **extra,
GucSource source);
extern bool check_vacuum_buffer_usage_limit(int *newval, void **extra,
@@ -84,13 +82,9 @@ extern const char *show_log_timezone(void);
extern bool check_maintenance_io_concurrency(int *newval, void **extra,
GucSource source);
extern void assign_maintenance_io_concurrency(int newval, void *extra);
-extern bool check_max_connections(int *newval, void **extra, GucSource source);
-extern bool check_max_wal_senders(int *newval, void **extra, GucSource source);
extern bool check_max_slot_wal_keep_size(int *newval, void **extra,
GucSource source);
extern void assign_max_wal_size(int newval, void *extra);
-extern bool check_max_worker_processes(int *newval, void **extra,
- GucSource source);
extern bool check_max_stack_depth(int *newval, void **extra, GucSource source);
extern void assign_max_stack_depth(int newval, void *extra);
extern bool check_multixact_member_buffers(int *newval, void **extra,
--
2.39.3 (Apple Git-146)
[text/plain] v5-0003-allow-changing-autovacuum_max_workers-without-res.patch (10.7K, ../ZnXmF2FVugUVjSxN@nathan/4-v5-0003-allow-changing-autovacuum_max_workers-without-res.patch)
download | inline diff:
From 48f7d56b99f2d98533c8767d5891a78c8411872d Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 21 Jun 2024 15:29:28 -0500
Subject: [PATCH v5 3/3] allow changing autovacuum_max_workers without
restarting
---
doc/src/sgml/config.sgml | 8 +++-
src/backend/postmaster/autovacuum.c | 25 ++++++++++---
src/backend/postmaster/postmaster.c | 2 +-
src/backend/storage/lmgr/proc.c | 4 +-
src/backend/utils/init/postinit.c | 37 +++++++++++++++++--
src/backend/utils/misc/guc_tables.c | 4 +-
src/backend/utils/misc/postgresql.conf.sample | 1 -
src/include/postmaster/autovacuum.h | 1 +
src/include/utils/guc_hooks.h | 2 +
9 files changed, 69 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 087385cb4e..289c2b63c4 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8554,7 +8554,13 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
<para>
Specifies the maximum number of autovacuum processes (other than the
autovacuum launcher) that may be running at any one time. The default
- is three. This parameter can only be set at server start.
+ is three. This parameter can only be set in the
+ <filename>postgresql.conf</filename> file or on the server command line.
+ </para>
+ <para>
+ This parameter is limited to 50% of
+ <xref linkend="guc-max-connections"/> (but no less than 1 or greater
+ than 64).
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 9a925a10cd..8bf95981a0 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -130,6 +130,7 @@ double autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
int Log_autovacuum_min_duration = 600000;
+int autovacuum_worker_slots = -1;
/* the minimum allowed time between two awakenings of the launcher */
#define MIN_AUTOVAC_SLEEPTIME 100.0 /* milliseconds */
@@ -347,6 +348,7 @@ static void autovac_report_activity(autovac_table *tab);
static void autovac_report_workitem(AutoVacuumWorkItem *workitem,
const char *nspname, const char *relname);
static void avl_sigusr2_handler(SIGNAL_ARGS);
+static bool av_worker_available(void);
@@ -575,7 +577,7 @@ AutoVacLauncherMain(char *startup_data, size_t startup_data_len)
* wakening conditions.
*/
- launcher_determine_sleep(!dlist_is_empty(&AutoVacuumShmem->av_freeWorkers),
+ launcher_determine_sleep(av_worker_available(),
false, &nap);
/*
@@ -636,7 +638,7 @@ AutoVacLauncherMain(char *startup_data, size_t startup_data_len)
current_time = GetCurrentTimestamp();
LWLockAcquire(AutovacuumLock, LW_SHARED);
- can_launch = !dlist_is_empty(&AutoVacuumShmem->av_freeWorkers);
+ can_launch = av_worker_available();
if (AutoVacuumShmem->av_startingWorker != NULL)
{
@@ -1087,7 +1089,7 @@ do_start_worker(void)
/* return quickly when there are no free workers */
LWLockAcquire(AutovacuumLock, LW_SHARED);
- if (dlist_is_empty(&AutoVacuumShmem->av_freeWorkers))
+ if (!av_worker_available())
{
LWLockRelease(AutovacuumLock);
return InvalidOid;
@@ -3265,7 +3267,7 @@ AutoVacuumShmemSize(void)
*/
size = sizeof(AutoVacuumShmemStruct);
size = MAXALIGN(size);
- size = add_size(size, mul_size(autovacuum_max_workers,
+ size = add_size(size, mul_size(autovacuum_worker_slots,
sizeof(WorkerInfoData)));
return size;
}
@@ -3302,7 +3304,7 @@ AutoVacuumShmemInit(void)
MAXALIGN(sizeof(AutoVacuumShmemStruct)));
/* initialize the WorkerInfo free list */
- for (i = 0; i < autovacuum_max_workers; i++)
+ for (i = 0; i < autovacuum_worker_slots; i++)
{
dlist_push_head(&AutoVacuumShmem->av_freeWorkers,
&worker[i].wi_links);
@@ -3341,3 +3343,16 @@ check_autovacuum_work_mem(int *newval, void **extra, GucSource source)
return true;
}
+
+static bool
+av_worker_available(void)
+{
+ int reserved = autovacuum_worker_slots - autovacuum_max_workers;
+ int count = 0;
+ dlist_iter iter;
+
+ dlist_foreach(iter, &AutoVacuumShmem->av_freeWorkers)
+ count++;
+
+ return count > reserved;
+}
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index bf0241aed0..2588a68fbc 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -4144,7 +4144,7 @@ CreateOptsFile(int argc, char *argv[], char *fullprogname)
int
MaxLivePostmasterChildren(void)
{
- return 2 * (MaxConnections + autovacuum_max_workers + 1 +
+ return 2 * (MaxConnections + autovacuum_worker_slots + 1 +
max_wal_senders + max_worker_processes);
}
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index ce29da9012..10e0b681cf 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -242,13 +242,13 @@ InitProcGlobal(void)
dlist_push_tail(&ProcGlobal->freeProcs, &proc->links);
proc->procgloballist = &ProcGlobal->freeProcs;
}
- else if (i < MaxConnections + autovacuum_max_workers + 1)
+ else if (i < MaxConnections + autovacuum_worker_slots + 1)
{
/* PGPROC for AV launcher/worker, add to autovacFreeProcs list */
dlist_push_tail(&ProcGlobal->autovacFreeProcs, &proc->links);
proc->procgloballist = &ProcGlobal->autovacFreeProcs;
}
- else if (i < MaxConnections + autovacuum_max_workers + 1 + max_worker_processes)
+ else if (i < MaxConnections + autovacuum_worker_slots + 1 + max_worker_processes)
{
/* PGPROC for bgworker, add to bgworkerFreeProcs list */
dlist_push_tail(&ProcGlobal->bgworkerFreeProcs, &proc->links);
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 8a629982c4..d206dd1533 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -576,20 +576,51 @@ InitializeMaxBackends(void)
{
Assert(MaxBackends == 0);
+ autovacuum_worker_slots = Max(1, Min(64, MaxConnections / 2));
+ if (autovacuum_max_workers > autovacuum_worker_slots)
+ {
+ /* keep in sync with check_autovacuum_max_workers() */
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid setting for \"autovacuum_max_workers\""),
+ errdetail("\"autovacuum_max_workers\" (%d) is limited to 50%% of \"max_connections\" (but no less than 1 or greater than 64) (%d)",
+ autovacuum_max_workers, autovacuum_worker_slots)));
+ }
+
/* the extra unit accounts for the autovacuum launcher */
- MaxBackends = MaxConnections + autovacuum_max_workers + 1 +
+ MaxBackends = MaxConnections + autovacuum_worker_slots + 1 +
max_worker_processes + max_wal_senders;
if (MaxBackends > MAX_BACKENDS)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("too many backends configured"),
- errdetail("\"max_connections\" (%d) plus \"autovacuum_max_workers\" (%d) plus \"max_worker_processes\" (%d) plus \"max_wal_senders\" (%d) must be less than %d.",
- MaxConnections, autovacuum_max_workers,
+ errdetail("\"max_connections\" (%d) plus autovacuum worker slots (%d) plus \"max_worker_processes\" (%d) plus \"max_wal_senders\" (%d) must be less than %d.",
+ MaxConnections, autovacuum_worker_slots,
max_worker_processes, max_wal_senders,
MAX_BACKENDS - 1)));
}
+/*
+ * GUC check_hook for autovacuum_max_workers
+ */
+bool
+check_autovacuum_max_workers(int *newval, void **extra, GucSource source)
+{
+ /*
+ * If autovacuum_worker_slots has not yet been initialized, just return
+ * true. We'll fail startup in PostmasterMain() if needed.
+ */
+ if (autovacuum_worker_slots == -1 || *newval <= autovacuum_worker_slots)
+ return true;
+
+ /* keep in sync with matching error message in InitializeMaxBackends() */
+ GUC_check_errmsg("invalid setting for \"autovacuum_max_workers\"");
+ GUC_check_errdetail("\"autovacuum_max_workers\" (%d) is limited to 50%% of \"max_connections\" (but no less than 1 or greater than 64) (%d)",
+ autovacuum_max_workers, autovacuum_worker_slots);
+ return false;
+}
+
/*
* Early initialization of a backend (either standalone or under postmaster).
* This happens even before InitPostgres.
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 57df7767ad..f1babff58e 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3393,13 +3393,13 @@ struct config_int ConfigureNamesInt[] =
},
{
/* see max_connections */
- {"autovacuum_max_workers", PGC_POSTMASTER, AUTOVACUUM,
+ {"autovacuum_max_workers", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Sets the maximum number of simultaneously running autovacuum worker processes."),
NULL
},
&autovacuum_max_workers,
3, 1, MAX_BACKENDS,
- NULL, NULL, NULL
+ check_autovacuum_max_workers, NULL, NULL
},
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e0567de219..fec719fe79 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -659,7 +659,6 @@
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
- # (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index cae1e8b329..2b2aa1b713 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -46,6 +46,7 @@ extern PGDLLIMPORT int autovacuum_vac_cost_limit;
extern PGDLLIMPORT int AutovacuumLauncherPid;
extern PGDLLIMPORT int Log_autovacuum_min_duration;
+extern PGDLLIMPORT int autovacuum_worker_slots;
/* Status inquiry functions */
extern bool AutoVacuumingActive(void);
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index 6304f0679b..b3e1f9f2cb 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -29,6 +29,8 @@ extern bool check_application_name(char **newval, void **extra,
GucSource source);
extern void assign_application_name(const char *newval, void *extra);
extern const char *show_archive_command(void);
+extern bool check_autovacuum_max_workers(int *newval, void **extra,
+ GucSource source);
extern bool check_autovacuum_work_mem(int *newval, void **extra,
GucSource source);
extern bool check_vacuum_buffer_usage_limit(int *newval, void **extra,
--
2.39.3 (Apple Git-146)
view thread (71+ messages) latest in thread
Message-ID: <ZnXmF2FVugUVjSxN@nathan>
Permalink: ../ZnXmF2FVugUVjSxN@nathan/
Also on: postgresql.org/message-id/ZnXmF2FVugUVjSxN@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, andres@anarazel.de, simseih@amazon.com, pryzby@telsasoft.com
Subject: Re: allow changing autovacuum_max_workers without restarting
In-Reply-To: <ZnXmF2FVugUVjSxN@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