agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nathan Bossart <nathandbossart@gmail.com>
To: Imseih (AWS), Sami <simseih@amazon.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: allow changing autovacuum_max_workers without restarting
Date: Fri, 19 Apr 2024 10:43:22 -0500
Message-ID: <20240419154322.GA3988554@nathanxps13> (raw)
In-Reply-To: <37F50121-2DC5-4B7D-AB8F-FC2A0CD827A5@amazon.com>
References: <20240411144240.GD1882158@nathanxps13>
<66B70E79-54C0-4E58-831D-502B4B97E33D@amazon.com>
<20240411192423.GB2005410@nathanxps13>
<DC833F51-881A-47F9-AEF6-B2E1DE46CDC8@amazon.com>
<20240412184000.GA2416270@nathanxps13>
<9CD7B92C-3330-4E79-A84E-95E99B1FD926@amazon.com>
<20240413194450.GA2537802@nathanxps13>
<25FBADC0-52E7-4DE6-B043-68E5F77D4CB1@amazon.com>
<20240414144058.GA2819458@nathanxps13>
<37F50121-2DC5-4B7D-AB8F-FC2A0CD827A5@amazon.com>
On Thu, Apr 18, 2024 at 05:05:03AM +0000, Imseih (AWS), Sami wrote:
> I looked at the patch set. With the help of DEBUG2 output, I tested to ensure
> that the the autovacuum_cost_limit balance adjusts correctly when the
> autovacuum_max_workers value increases/decreases. I did not think the
> patch will break this behavior, but it's important to verify this.
Great.
> 1. A nit. There should be a tab here.
>
> - dlist_head av_freeWorkers;
> + dclist_head av_freeWorkers;
I dare not argue with pgindent.
> 2. autovacuum_max_worker_slots documentation:
>
> + <para>
> + Note that the value of <xref linkend="guc-autovacuum-max-workers"/> is
> + silently capped to this value.
> + </para>
>
> This comment looks redundant in the docs, since the entry
> for autovacuum_max_workers that follows mentions the
> same.
Removed in v2. I also noticed that I forgot to update the part about when
autovacuum_max_workers can be changed. *facepalm*
> 3. The docs for autovacuum_max_workers should mention that when
> the value changes, consider adjusting the autovacuum_cost_limit/cost_delay.
>
> This is not something new. Even in the current state, users should think about
> these settings. However, it seems even important if this value is to be
> dynamically adjusted.
I don't necessarily disagree that it might be worth mentioning these
parameters, but I would argue that this should be proposed in a separate
thread.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
[text/x-diff] v2-0001-Rename-autovacuum_max_workers-to-autovacuum_max_w.patch (17.1K, ../20240419154322.GA3988554@nathanxps13/2-v2-0001-Rename-autovacuum_max_workers-to-autovacuum_max_w.patch)
download | inline diff:
From 466f31a23605755a8e3d17c362c9f4940bf91da0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Sat, 13 Apr 2024 15:00:08 -0500
Subject: [PATCH v2 1/4] Rename autovacuum_max_workers to
autovacuum_max_worker_slots.
---
doc/src/sgml/config.sgml | 8 ++++----
doc/src/sgml/maintenance.sgml | 4 ++--
doc/src/sgml/runtime.sgml | 12 ++++++------
src/backend/access/transam/xlog.c | 2 +-
src/backend/postmaster/autovacuum.c | 8 ++++----
src/backend/postmaster/postmaster.c | 2 +-
src/backend/storage/lmgr/proc.c | 6 +++---
src/backend/utils/init/postinit.c | 12 ++++++------
src/backend/utils/misc/guc_tables.c | 6 +++---
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/include/postmaster/autovacuum.h | 2 +-
src/include/utils/guc_hooks.h | 4 ++--
.../modules/xid_wraparound/t/001_emergency_vacuum.pl | 2 +-
src/test/modules/xid_wraparound/t/003_wraparounds.pl | 2 +-
14 files changed, 36 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index d8e1282e12..b4d67a93b6 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1914,7 +1914,7 @@ include_dir 'conf.d'
</para>
<para>
Note that when autovacuum runs, up to
- <xref linkend="guc-autovacuum-max-workers"/> times this memory
+ <xref linkend="guc-autovacuum-max-worker-slots"/> times this memory
may be allocated, so be careful not to set the default value
too high. It may be useful to control for this by separately
setting <xref linkend="guc-autovacuum-work-mem"/>.
@@ -8534,10 +8534,10 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
- <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
- <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
+ <varlistentry id="guc-autovacuum-max-worker-slots" xreflabel="autovacuum_max_worker_slots">
+ <term><varname>autovacuum_max_worker_slots</varname> (<type>integer</type>)
<indexterm>
- <primary><varname>autovacuum_max_workers</varname> configuration parameter</primary>
+ <primary><varname>autovacuum_max_worker_slots</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 0be90bdc7e..5373acba41 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -864,9 +864,9 @@ HINT: Execute a database-wide VACUUM in that database.
seconds. (Therefore, if the installation has <replaceable>N</replaceable> databases,
a new worker will be launched every
<varname>autovacuum_naptime</varname>/<replaceable>N</replaceable> seconds.)
- A maximum of <xref linkend="guc-autovacuum-max-workers"/> worker processes
+ A maximum of <xref linkend="guc-autovacuum-max-worker-slots"/> worker processes
are allowed to run at the same time. If there are more than
- <varname>autovacuum_max_workers</varname> databases to be processed,
+ <varname>autovacuum_max_worker_slots</varname> databases to be processed,
the next database will be processed as soon as the first worker finishes.
Each worker process will check each table within its database and
execute <command>VACUUM</command> and/or <command>ANALYZE</command> as needed.
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 6047b8171d..26a02034c8 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 + 5) / 16)</literal> plus room for other applications</entry>
+ <entry>at least <literal>ceil((max_connections + autovacuum_max_worker_slots + max_wal_senders + max_worker_processes + 5) / 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 + 5) / 16) * 17</literal> plus room for other applications</entry>
+ <entry><literal>ceil((max_connections + autovacuum_max_worker_slots + max_wal_senders + max_worker_processes + 5) / 16) * 17</literal> plus room for other applications</entry>
</row>
<row>
@@ -838,7 +838,7 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such
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"/>) and allowed background
+ (<xref linkend="guc-autovacuum-max-worker-slots"/>) and allowed background
process (<xref linkend="guc-max-worker-processes"/>), in sets of 16.
Each such set will
also contain a 17th semaphore which contains a <quote>magic
@@ -846,13 +846,13 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such
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>,
+ <varname>autovacuum_max_worker_slots</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
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 + 5) / 16)</literal>.
+ least <literal>ceil((max_connections + autovacuum_max_worker_slots + max_wal_senders + max_worker_processes + 5) / 16)</literal>.
Lowering the number
of allowed connections is a temporary workaround for failures,
which are usually confusingly worded <quote>No space
@@ -883,7 +883,7 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such
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"/>) and allowed background
+ (<xref linkend="guc-autovacuum-max-worker-slots"/>) and allowed background
process (<xref linkend="guc-max-worker-processes"/>).
On the platforms where this option is preferred, there is no specific
kernel limit on the number of POSIX semaphores.
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 34a2c71812..9f9ce5da7d 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -5362,7 +5362,7 @@ CheckRequiredParameterValues(void)
*/
if (ArchiveRecoveryRequested && EnableHotStandby)
{
- /* We ignore autovacuum_max_workers when we make this test. */
+ /* We ignore autovacuum_max_worker_slots when we make this test. */
RecoveryRequiresIntParameter("max_connections",
MaxConnections,
ControlFile->MaxConnections);
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index c367ede6f8..af3d1e218e 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -114,7 +114,7 @@
* GUC parameters
*/
bool autovacuum_start_daemon = false;
-int autovacuum_max_workers;
+int autovacuum_max_worker_slots;
int autovacuum_work_mem = -1;
int autovacuum_naptime;
int autovacuum_vac_thresh;
@@ -209,7 +209,7 @@ typedef struct autovac_table
/*-------------
* This struct holds information about a single worker's whereabouts. We keep
* an array of these in shared memory, sized according to
- * autovacuum_max_workers.
+ * autovacuum_max_worker_slots.
*
* wi_links entry into free list or running list
* wi_dboid OID of the database this worker is supposed to work on
@@ -3262,7 +3262,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_max_worker_slots,
sizeof(WorkerInfoData)));
return size;
}
@@ -3299,7 +3299,7 @@ AutoVacuumShmemInit(void)
MAXALIGN(sizeof(AutoVacuumShmemStruct)));
/* initialize the WorkerInfo free list */
- for (i = 0; i < autovacuum_max_workers; i++)
+ for (i = 0; i < autovacuum_max_worker_slots; i++)
{
dlist_push_head(&AutoVacuumShmem->av_freeWorkers,
&worker[i].wi_links);
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 7f3170a8f0..0faec534c0 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_max_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 e4f256c63c..4587c5a508 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -142,7 +142,7 @@ ProcGlobalSemas(void)
* So, now we grab enough semaphores to support the desired max number
* of backends immediately at initialization --- if the sysadmin has set
* MaxConnections, max_worker_processes, max_wal_senders, or
- * autovacuum_max_workers higher than his kernel will support, he'll
+ * autovacuum_max_worker_slots higher than his kernel will support, he'll
* find out sooner rather than later.
*
* Another reason for creating semaphores here is that the semaphore
@@ -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_max_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_max_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 0805398e24..c05653262f 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -577,7 +577,7 @@ InitializeMaxBackends(void)
Assert(MaxBackends == 0);
/* the extra unit accounts for the autovacuum launcher */
- MaxBackends = MaxConnections + autovacuum_max_workers + 1 +
+ MaxBackends = MaxConnections + autovacuum_max_worker_slots + 1 +
max_worker_processes + max_wal_senders;
/* internal error because the values were all checked previously */
@@ -591,17 +591,17 @@ InitializeMaxBackends(void)
bool
check_max_connections(int *newval, void **extra, GucSource source)
{
- if (*newval + autovacuum_max_workers + 1 +
+ if (*newval + autovacuum_max_worker_slots + 1 +
max_worker_processes + max_wal_senders > MAX_BACKENDS)
return false;
return true;
}
/*
- * GUC check_hook for autovacuum_max_workers
+ * GUC check_hook for autovacuum_max_worker_slots
*/
bool
-check_autovacuum_max_workers(int *newval, void **extra, GucSource source)
+check_autovacuum_max_worker_slots(int *newval, void **extra, GucSource source)
{
if (MaxConnections + *newval + 1 +
max_worker_processes + max_wal_senders > MAX_BACKENDS)
@@ -615,7 +615,7 @@ check_autovacuum_max_workers(int *newval, void **extra, GucSource source)
bool
check_max_worker_processes(int *newval, void **extra, GucSource source)
{
- if (MaxConnections + autovacuum_max_workers + 1 +
+ if (MaxConnections + autovacuum_max_worker_slots + 1 +
*newval + max_wal_senders > MAX_BACKENDS)
return false;
return true;
@@ -627,7 +627,7 @@ check_max_worker_processes(int *newval, void **extra, GucSource source)
bool
check_max_wal_senders(int *newval, void **extra, GucSource source)
{
- if (MaxConnections + autovacuum_max_workers + 1 +
+ if (MaxConnections + autovacuum_max_worker_slots + 1 +
max_worker_processes + *newval > MAX_BACKENDS)
return false;
return true;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c68fdc008b..92dea7061a 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3402,13 +3402,13 @@ struct config_int ConfigureNamesInt[] =
},
{
/* see max_connections */
- {"autovacuum_max_workers", PGC_POSTMASTER, AUTOVACUUM,
+ {"autovacuum_max_worker_slots", PGC_POSTMASTER, AUTOVACUUM,
gettext_noop("Sets the maximum number of simultaneously running autovacuum worker processes."),
NULL
},
- &autovacuum_max_workers,
+ &autovacuum_max_worker_slots,
3, 1, MAX_BACKENDS,
- check_autovacuum_max_workers, NULL, NULL
+ check_autovacuum_max_worker_slots, NULL, NULL
},
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 2166ea4a87..c37767cecf 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -658,7 +658,7 @@
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
-#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
+#autovacuum_max_worker_slots = 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
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index cae1e8b329..754d04485d 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -28,7 +28,7 @@ typedef enum
/* GUC variables */
extern PGDLLIMPORT bool autovacuum_start_daemon;
-extern PGDLLIMPORT int autovacuum_max_workers;
+extern PGDLLIMPORT int autovacuum_max_worker_slots;
extern PGDLLIMPORT int autovacuum_work_mem;
extern PGDLLIMPORT int autovacuum_naptime;
extern PGDLLIMPORT int autovacuum_vac_thresh;
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index d64dc5fcdb..22d4c50bc6 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -29,8 +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_max_worker_slots(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,
diff --git a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
index 37550b67a4..f9cdd50c19 100644
--- a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
+++ b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
@@ -21,7 +21,7 @@ $node->append_conf(
autovacuum = off # run autovacuum only when to anti wraparound
autovacuum_naptime = 1s
# so it's easier to verify the order of operations
-autovacuum_max_workers = 1
+autovacuum_max_worker_slots = 1
log_autovacuum_min_duration = 0
]);
$node->start;
diff --git a/src/test/modules/xid_wraparound/t/003_wraparounds.pl b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
index 88063b4b52..99f76229d5 100644
--- a/src/test/modules/xid_wraparound/t/003_wraparounds.pl
+++ b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
@@ -24,7 +24,7 @@ $node->append_conf(
autovacuum = off # run autovacuum only when to anti wraparound
autovacuum_naptime = 1s
# so it's easier to verify the order of operations
-autovacuum_max_workers = 1
+autovacuum_max_worker_slots = 1
log_autovacuum_min_duration = 0
]);
$node->start;
--
2.25.1
[text/x-diff] v2-0002-Convert-autovacuum-s-free-workers-list-to-a-dclis.patch (3.8K, ../20240419154322.GA3988554@nathanxps13/3-v2-0002-Convert-autovacuum-s-free-workers-list-to-a-dclis.patch)
download | inline diff:
From 6ba708a66dfaf964f8330a81417e3efdfeef9c92 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Sat, 13 Apr 2024 21:48:53 -0500
Subject: [PATCH v2 2/4] Convert autovacuum's free workers list to a dclist.
---
src/backend/postmaster/autovacuum.c | 24 ++++++++++++------------
1 file changed, 12 insertions(+), 12 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index af3d1e218e..e925eff1e4 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -289,7 +289,7 @@ typedef struct
{
sig_atomic_t av_signal[AutoVacNumSignals];
pid_t av_launcherpid;
- dlist_head av_freeWorkers;
+ dclist_head av_freeWorkers;
dlist_head av_runningWorkers;
WorkerInfo av_startingWorker;
AutoVacuumWorkItem av_workItems[NUM_WORKITEMS];
@@ -575,7 +575,7 @@ AutoVacLauncherMain(char *startup_data, size_t startup_data_len)
* wakening conditions.
*/
- launcher_determine_sleep(!dlist_is_empty(&AutoVacuumShmem->av_freeWorkers),
+ launcher_determine_sleep(!dclist_is_empty(&AutoVacuumShmem->av_freeWorkers),
false, &nap);
/*
@@ -636,7 +636,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 = !dclist_is_empty(&AutoVacuumShmem->av_freeWorkers);
if (AutoVacuumShmem->av_startingWorker != NULL)
{
@@ -679,8 +679,8 @@ AutoVacLauncherMain(char *startup_data, size_t startup_data_len)
worker->wi_sharedrel = false;
worker->wi_proc = NULL;
worker->wi_launchtime = 0;
- dlist_push_head(&AutoVacuumShmem->av_freeWorkers,
- &worker->wi_links);
+ dclist_push_head(&AutoVacuumShmem->av_freeWorkers,
+ &worker->wi_links);
AutoVacuumShmem->av_startingWorker = NULL;
ereport(WARNING,
errmsg("autovacuum worker took too long to start; canceled"));
@@ -1087,7 +1087,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 (dclist_is_empty(&AutoVacuumShmem->av_freeWorkers))
{
LWLockRelease(AutovacuumLock);
return InvalidOid;
@@ -1240,7 +1240,7 @@ do_start_worker(void)
* Get a worker entry from the freelist. We checked above, so there
* really should be a free slot.
*/
- wptr = dlist_pop_head_node(&AutoVacuumShmem->av_freeWorkers);
+ wptr = dclist_pop_head_node(&AutoVacuumShmem->av_freeWorkers);
worker = dlist_container(WorkerInfoData, wi_links, wptr);
worker->wi_dboid = avdb->adw_datid;
@@ -1609,8 +1609,8 @@ FreeWorkerInfo(int code, Datum arg)
MyWorkerInfo->wi_proc = NULL;
MyWorkerInfo->wi_launchtime = 0;
pg_atomic_clear_flag(&MyWorkerInfo->wi_dobalance);
- dlist_push_head(&AutoVacuumShmem->av_freeWorkers,
- &MyWorkerInfo->wi_links);
+ dclist_push_head(&AutoVacuumShmem->av_freeWorkers,
+ &MyWorkerInfo->wi_links);
/* not mine anymore */
MyWorkerInfo = NULL;
@@ -3289,7 +3289,7 @@ AutoVacuumShmemInit(void)
Assert(!found);
AutoVacuumShmem->av_launcherpid = 0;
- dlist_init(&AutoVacuumShmem->av_freeWorkers);
+ dclist_init(&AutoVacuumShmem->av_freeWorkers);
dlist_init(&AutoVacuumShmem->av_runningWorkers);
AutoVacuumShmem->av_startingWorker = NULL;
memset(AutoVacuumShmem->av_workItems, 0,
@@ -3301,8 +3301,8 @@ AutoVacuumShmemInit(void)
/* initialize the WorkerInfo free list */
for (i = 0; i < autovacuum_max_worker_slots; i++)
{
- dlist_push_head(&AutoVacuumShmem->av_freeWorkers,
- &worker[i].wi_links);
+ dclist_push_head(&AutoVacuumShmem->av_freeWorkers,
+ &worker[i].wi_links);
pg_atomic_init_flag(&worker[i].wi_dobalance);
}
--
2.25.1
[text/x-diff] v2-0003-Move-free-autovacuum-worker-checks-to-a-helper-fu.patch (2.2K, ../20240419154322.GA3988554@nathanxps13/4-v2-0003-Move-free-autovacuum-worker-checks-to-a-helper-fu.patch)
download | inline diff:
From 6ecfb0aebb0b1d0e7d2d9f8b65038ae8c2c98f88 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Sun, 14 Apr 2024 09:04:01 -0500
Subject: [PATCH v2 3/4] Move free autovacuum worker checks to a helper
function.
---
src/backend/postmaster/autovacuum.c | 19 +++++++++++++++----
1 file changed, 15 insertions(+), 4 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index e925eff1e4..f80365faff 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -347,6 +347,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,8 +576,7 @@ AutoVacLauncherMain(char *startup_data, size_t startup_data_len)
* wakening conditions.
*/
- launcher_determine_sleep(!dclist_is_empty(&AutoVacuumShmem->av_freeWorkers),
- false, &nap);
+ launcher_determine_sleep(av_worker_available(), false, &nap);
/*
* Wait until naptime expires or we get some type of signal (all the
@@ -636,7 +636,7 @@ AutoVacLauncherMain(char *startup_data, size_t startup_data_len)
current_time = GetCurrentTimestamp();
LWLockAcquire(AutovacuumLock, LW_SHARED);
- can_launch = !dclist_is_empty(&AutoVacuumShmem->av_freeWorkers);
+ can_launch = av_worker_available();
if (AutoVacuumShmem->av_startingWorker != NULL)
{
@@ -1087,7 +1087,7 @@ do_start_worker(void)
/* return quickly when there are no free workers */
LWLockAcquire(AutovacuumLock, LW_SHARED);
- if (dclist_is_empty(&AutoVacuumShmem->av_freeWorkers))
+ if (!av_worker_available())
{
LWLockRelease(AutovacuumLock);
return InvalidOid;
@@ -3338,3 +3338,14 @@ check_autovacuum_work_mem(int *newval, void **extra, GucSource source)
return true;
}
+
+/*
+ * Returns whether there is a free autovacuum worker slot available.
+ */
+static bool
+av_worker_available(void)
+{
+ const dclist_head *freelist = &AutoVacuumShmem->av_freeWorkers;
+
+ return dclist_count(freelist) > 0;
+}
--
2.25.1
[text/x-diff] v2-0004-Reintroduce-autovacuum_max_workers-as-a-PGC_SIGHU.patch (8.1K, ../20240419154322.GA3988554@nathanxps13/5-v2-0004-Reintroduce-autovacuum_max_workers-as-a-PGC_SIGHU.patch)
download | inline diff:
From 915e1594439328d47a04096c9811dd43a4526efe Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Sat, 13 Apr 2024 21:42:33 -0500
Subject: [PATCH v2 4/4] Reintroduce autovacuum_max_workers as a PGC_SIGHUP
parameter.
---
doc/src/sgml/config.sgml | 24 +++++++++++++++++--
doc/src/sgml/maintenance.sgml | 4 ++--
src/backend/postmaster/autovacuum.c | 4 +++-
src/backend/utils/misc/guc_tables.c | 15 +++++++++---
src/backend/utils/misc/postgresql.conf.sample | 3 ++-
src/include/postmaster/autovacuum.h | 1 +
.../xid_wraparound/t/001_emergency_vacuum.pl | 2 +-
.../xid_wraparound/t/003_wraparounds.pl | 2 +-
8 files changed, 44 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index b4d67a93b6..0f022a8056 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1914,7 +1914,7 @@ include_dir 'conf.d'
</para>
<para>
Note that when autovacuum runs, up to
- <xref linkend="guc-autovacuum-max-worker-slots"/> times this memory
+ <xref linkend="guc-autovacuum-max-workers"/> times this memory
may be allocated, so be careful not to set the default value
too high. It may be useful to control for this by separately
setting <xref linkend="guc-autovacuum-work-mem"/>.
@@ -8540,11 +8540,31 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
<primary><varname>autovacuum_max_worker_slots</varname> configuration parameter</primary>
</indexterm>
</term>
+ <listitem>
+ <para>
+ Specifies the number of backend slots to reserve for autovacuum worker
+ processes. The default is 32. This parameter can only be set at server
+ start.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
+ <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_max_workers</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
<listitem>
<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>
+ Note that this value is silently capped to the value of
+ <xref linkend="guc-autovacuum-max-worker-slots"/>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 5373acba41..0be90bdc7e 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -864,9 +864,9 @@ HINT: Execute a database-wide VACUUM in that database.
seconds. (Therefore, if the installation has <replaceable>N</replaceable> databases,
a new worker will be launched every
<varname>autovacuum_naptime</varname>/<replaceable>N</replaceable> seconds.)
- A maximum of <xref linkend="guc-autovacuum-max-worker-slots"/> worker processes
+ A maximum of <xref linkend="guc-autovacuum-max-workers"/> worker processes
are allowed to run at the same time. If there are more than
- <varname>autovacuum_max_worker_slots</varname> databases to be processed,
+ <varname>autovacuum_max_workers</varname> databases to be processed,
the next database will be processed as soon as the first worker finishes.
Each worker process will check each table within its database and
execute <command>VACUUM</command> and/or <command>ANALYZE</command> as needed.
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index f80365faff..ed7e2b462f 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -115,6 +115,7 @@
*/
bool autovacuum_start_daemon = false;
int autovacuum_max_worker_slots;
+int autovacuum_max_workers;
int autovacuum_work_mem = -1;
int autovacuum_naptime;
int autovacuum_vac_thresh;
@@ -3346,6 +3347,7 @@ static bool
av_worker_available(void)
{
const dclist_head *freelist = &AutoVacuumShmem->av_freeWorkers;
+ int reserved_slots = autovacuum_max_worker_slots - autovacuum_max_workers;
- return dclist_count(freelist) > 0;
+ return dclist_count(freelist) > Max(0, reserved_slots);
}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 92dea7061a..92d4d10fe9 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3403,13 +3403,22 @@ struct config_int ConfigureNamesInt[] =
{
/* see max_connections */
{"autovacuum_max_worker_slots", PGC_POSTMASTER, AUTOVACUUM,
- gettext_noop("Sets the maximum number of simultaneously running autovacuum worker processes."),
- NULL
+ gettext_noop("Sets the number of backend slots to allocate for autovacuum workers."),
+ gettext_noop("autovacuum_max_workers is silently capped to this value.")
},
&autovacuum_max_worker_slots,
- 3, 1, MAX_BACKENDS,
+ 32, 1, MAX_BACKENDS,
check_autovacuum_max_worker_slots, NULL, NULL
},
+ {
+ {"autovacuum_max_workers", PGC_SIGHUP, AUTOVACUUM,
+ gettext_noop("Sets the maximum number of simultaneously running autovacuum worker processes."),
+ gettext_noop("This value is silently capped to autovacuum_max_worker_slots.")
+ },
+ &autovacuum_max_workers,
+ 3, 1, MAX_BACKENDS,
+ NULL, NULL, NULL
+ },
{
{"max_parallel_maintenance_workers", PGC_USERSET, RESOURCES_ASYNCHRONOUS,
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index c37767cecf..c46d245153 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -658,8 +658,9 @@
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
-#autovacuum_max_worker_slots = 3 # max number of autovacuum subprocesses
+autovacuum_max_worker_slots = 32 # autovacuum worker slots to allocate
# (change requires restart)
+#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#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 754d04485d..598782fd34 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -29,6 +29,7 @@ typedef enum
/* GUC variables */
extern PGDLLIMPORT bool autovacuum_start_daemon;
extern PGDLLIMPORT int autovacuum_max_worker_slots;
+extern PGDLLIMPORT int autovacuum_max_workers;
extern PGDLLIMPORT int autovacuum_work_mem;
extern PGDLLIMPORT int autovacuum_naptime;
extern PGDLLIMPORT int autovacuum_vac_thresh;
diff --git a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
index f9cdd50c19..37550b67a4 100644
--- a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
+++ b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
@@ -21,7 +21,7 @@ $node->append_conf(
autovacuum = off # run autovacuum only when to anti wraparound
autovacuum_naptime = 1s
# so it's easier to verify the order of operations
-autovacuum_max_worker_slots = 1
+autovacuum_max_workers = 1
log_autovacuum_min_duration = 0
]);
$node->start;
diff --git a/src/test/modules/xid_wraparound/t/003_wraparounds.pl b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
index 99f76229d5..88063b4b52 100644
--- a/src/test/modules/xid_wraparound/t/003_wraparounds.pl
+++ b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
@@ -24,7 +24,7 @@ $node->append_conf(
autovacuum = off # run autovacuum only when to anti wraparound
autovacuum_naptime = 1s
# so it's easier to verify the order of operations
-autovacuum_max_worker_slots = 1
+autovacuum_max_workers = 1
log_autovacuum_min_duration = 0
]);
$node->start;
--
2.25.1
view thread (71+ messages) latest in thread
Message-ID: <20240419154322.GA3988554@nathanxps13>
Permalink: ../20240419154322.GA3988554@nathanxps13/
Also on: postgresql.org/message-id/20240419154322.GA3988554@nathanxps13
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, simseih@amazon.com
Subject: Re: allow changing autovacuum_max_workers without restarting
In-Reply-To: <20240419154322.GA3988554@nathanxps13>
* 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