Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rxqOk-00CR55-Tn for pgsql-hackers@arkaria.postgresql.org; Fri, 19 Apr 2024 15:43:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rxqOj-003Utx-8e for pgsql-hackers@arkaria.postgresql.org; Fri, 19 Apr 2024 15:43:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rxqOi-003Utp-SG for pgsql-hackers@lists.postgresql.org; Fri, 19 Apr 2024 15:43:32 +0000 Received: from mail-il1-x12e.google.com ([2607:f8b0:4864:20::12e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rxqOe-001poH-SV for pgsql-hackers@postgresql.org; Fri, 19 Apr 2024 15:43:32 +0000 Received: by mail-il1-x12e.google.com with SMTP id e9e14a558f8ab-36a26272440so9087885ab.1 for ; Fri, 19 Apr 2024 08:43:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713541406; x=1714146206; darn=postgresql.org; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=LA7qW6MeuOcWpuiOksQB8O0PFBbRjHi1Kho/9ji4CeU=; b=FbZp+JanPrAuzogr7TRSEuL/FxFoMZILO3CPX9TuewlvjgNfDBghO2DGJAKK5jJjUG LEBc2t8c/a9s3qoIvrQ3iMeeadoFUN+bVCT42nwsBT3kOKyO383oo3M1rG6Xc1pWXxi2 0qypnxfpzFZ0biNnkBbU83jHcDwdGX6SOqlZXhe3lB4OtKmtW0dUDEM21bJLYIZhTvyY 3NPD2p0BiNNrd4QQxgIjs0KFk+XBwuOx1NL4u/LJYUEge07OYgm6oZmcudQBZzWkxD/u 9oJOKaANHOdfjHmBck3e8MvlCkdcswctJbRACKLkOXCp5DngqDkg4U8lE63oPVaVPGEF nTjw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713541406; x=1714146206; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=LA7qW6MeuOcWpuiOksQB8O0PFBbRjHi1Kho/9ji4CeU=; b=C2Gt4xD0JnYs7DZxicqBQoe6pnSsobO18lShHR89wVJWPwDOn7ajAFguTiZNca60jQ Em9xvyzknaoBqj+eXouQ3ZO/RDVdHj5nHXz9sRYBqSDqNzseq8ZZ8YXoP4ti1ez5nwnY QiQngLRKSghqzLz+S15iq/DvfI2Y5coBpPKp6zTwn13cjt/mFRRBdfdGx80ux0XSyqJI ///1Mym+uEo/0irQGQRjAkjmigJnyFXQnwVpK9Pwx7UxttkjhqeOnMLDNlT/rBEF+PtK tjcq406dctPogCAkZUtYaq/UmpqlaituIvn+cM7BroAK34iS6I7DDrjBr94XhQ20xF9b mRqQ== X-Gm-Message-State: AOJu0Yy5A51LTttl40bSdcH1+GWvgGT3Nmg0CUhW6/3ONZoOh8cbIwcJ Fd5HHDjzocQuHQTGOvyxXBvzkkIc8pw3Pv8Tl5e5fUHoTO5JTwY5 X-Google-Smtp-Source: AGHT+IFVWBZNEqU2jv4RP0yW814jQ0OkYtmLdTHOIySJsnfw7MVLLIVskSk1OyY3zIUAgzy8YY+bVQ== X-Received: by 2002:a05:6e02:1989:b0:368:96a2:f759 with SMTP id g9-20020a056e02198900b0036896a2f759mr3118792ilf.13.1713541406090; Fri, 19 Apr 2024 08:43:26 -0700 (PDT) Received: from nathanxps13 (162-195-168-172.lightspeed.stlsmo.sbcglobal.net. [162.195.168.172]) by smtp.gmail.com with ESMTPSA id h13-20020a056e02052d00b00369fd0b4595sm820996ils.59.2024.04.19.08.43.23 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 19 Apr 2024 08:43:24 -0700 (PDT) Date: Fri, 19 Apr 2024 10:43:22 -0500 From: Nathan Bossart To: "Imseih (AWS), Sami" Cc: "pgsql-hackers@postgresql.org" Subject: Re: allow changing autovacuum_max_workers without restarting Message-ID: <20240419154322.GA3988554@nathanxps13> References: <20240411144240.GD1882158@nathanxps13> <66B70E79-54C0-4E58-831D-502B4B97E33D@amazon.com> <20240411192423.GB2005410@nathanxps13> <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> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="VS++wcV0S1rZb1Fb" Content-Disposition: inline In-Reply-To: <37F50121-2DC5-4B7D-AB8F-FC2A0CD827A5@amazon.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --VS++wcV0S1rZb1Fb Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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: > > + > + Note that the value of is > + silently capped to this value. > + > > 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 --VS++wcV0S1rZb1Fb Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0001-Rename-autovacuum_max_workers-to-autovacuum_max_w.patch" From 466f31a23605755a8e3d17c362c9f4940bf91da0 Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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' Note that when autovacuum runs, up to - times this memory + 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 . @@ -8534,10 +8534,10 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; - - autovacuum_max_workers (integer) + + autovacuum_max_worker_slots (integer) - autovacuum_max_workers configuration parameter + autovacuum_max_worker_slots configuration parameter 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 N databases, a new worker will be launched every autovacuum_naptime/N seconds.) - A maximum of worker processes + A maximum of worker processes are allowed to run at the same time. If there are more than - autovacuum_max_workers databases to be processed, + autovacuum_max_worker_slots 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 VACUUM and/or ANALYZE 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 SEMMNI Maximum number of semaphore identifiers (i.e., sets) - at least ceil((max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes + 5) / 16) plus room for other applications + at least ceil((max_connections + autovacuum_max_worker_slots + max_wal_senders + max_worker_processes + 5) / 16) plus room for other applications SEMMNS Maximum number of semaphores system-wide - ceil((max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes + 5) / 16) * 17 plus room for other applications + ceil((max_connections + autovacuum_max_worker_slots + max_wal_senders + max_worker_processes + 5) / 16) * 17 plus room for other applications @@ -838,7 +838,7 @@ psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such When using System V semaphores, PostgreSQL uses one semaphore per allowed connection (), allowed autovacuum worker process - () and allowed background + () and allowed background process (), in sets of 16. Each such set will also contain a 17th semaphore which contains a 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 SEMMNS, which consequently must be at least as high as max_connections plus - autovacuum_max_workers plus max_wal_senders, + autovacuum_max_worker_slots plus max_wal_senders, plus max_worker_processes, plus one extra for each 16 allowed connections plus workers (see the formula in ). The parameter SEMMNI 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 ceil((max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes + 5) / 16). + least ceil((max_connections + autovacuum_max_worker_slots + max_wal_senders + max_worker_processes + 5) / 16). Lowering the number of allowed connections is a temporary workaround for failures, which are usually confusingly worded 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 (), allowed autovacuum worker process - () and allowed background + () and allowed background process (). 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 --VS++wcV0S1rZb1Fb Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0002-Convert-autovacuum-s-free-workers-list-to-a-dclis.patch" From 6ba708a66dfaf964f8330a81417e3efdfeef9c92 Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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 --VS++wcV0S1rZb1Fb Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0003-Move-free-autovacuum-worker-checks-to-a-helper-fu.patch" From 6ecfb0aebb0b1d0e7d2d9f8b65038ae8c2c98f88 Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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 --VS++wcV0S1rZb1Fb Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0004-Reintroduce-autovacuum_max_workers-as-a-PGC_SIGHU.patch" From 915e1594439328d47a04096c9811dd43a4526efe Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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' Note that when autovacuum runs, up to - times this memory + 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 . @@ -8540,11 +8540,31 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; autovacuum_max_worker_slots configuration parameter + + + 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. + + + + + + autovacuum_max_workers (integer) + + autovacuum_max_workers configuration parameter + + 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 + postgresql.conf file or on the server command line. + + + Note that this value is silently capped to the value of + . 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 N databases, a new worker will be launched every autovacuum_naptime/N seconds.) - A maximum of worker processes + A maximum of worker processes are allowed to run at the same time. If there are more than - autovacuum_max_worker_slots databases to be processed, + autovacuum_max_workers 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 VACUUM and/or ANALYZE 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 --VS++wcV0S1rZb1Fb--