On Sat, Apr 1, 2023 at 1:57 PM Justin Pryzby <pry...@telsasoft.com> wrote:
>
> On Sat, Apr 01, 2023 at 01:29:13PM -0400, Melanie Plageman wrote:
> > Hi,
> >
> > I was just doing some cleanup on the main patch in this set and realized
> > that it was missing a few things. One of which is forbidding the
> > BUFFER_USAGE_LIMIT with VACUUM FULL since VACUUM FULL does not use a
> > BAS_VACUUM strategy.
> >
> > VACUUM FULL technically uses a bulkread buffer access strategy for
> > reading the original relation if its number of blocks is > number of
> > shared buffers / 4 (see initscan()). The new rel writing is done using
> > smgrextend/write directly and doesn't go through shared buffers. I
> > think it is a stretch to try and use the size passed in to VACUUM by
> > BUFFER_USAGE_LIMIT for the bulkread strategy ring.
>
> When you say that it's a stretch, do you mean that it'd be a pain to add
> arguments to handful of functions to pass down the setting ? Or that
> it's unclear if doing so would be the desirable/needed/intended/expected
> behavior ?
More that I don't think it makes sense. VACUUM FULL only uses a buffer
access strategy (BAS_BULKREAD) for reading the original relation in and
not for writing the new one. It has different concerns because its
behavior is totally different from regular vacuum. It is not modifying
the original buffers (AFAIK) and the amount of WAL it is generating is
different. Also, no matter what, the new relation won't be in shared
buffers because of VACUUM FULL using the smgr functions directly. So, I
think that allowing the two options together is confusing for the user
because it seems to imply we can give them some benefit that we cannot.
> I wonder if maybe strategy should be configurable in some more generic
> way, like a GUC. At one point I had a patch to allow INSERT to use
> strategy buffers (not just INSERT SELECT). And that's still pretty
> desirable. Also COPY. I've seen load spikes caused by pg_dumping
> tables which are just below 25% of shared_buffers. Which is exacerbated
> because pg_dump deliberately orders tables by size, so those tables are
> dumped one after another, each causing eviction of ~20% of shared
> buffers. And exacerbated some more because TOAST don't seem to use a
> ring buffer in that case.
Yes, it is probably worth exploring how configurable or dynamic Buffer
Access Strategies should be for other users (e.g. not just VACUUM).
However, since the ring sizes wouldn't be the same for all the different
operations, it is probably easier to start with a single kind of
operation and go from there.
> > I somehow feel like VACUUM (FULL, BUFFER_USAGE_LIMIT 'x') should error
> > out instead of silently not using the buffer usage limit, though.
> >
> > I am looking for others' opinions.
>
> Sorry, no opinion here :)
>
> One thing is that it's fine to take something that previously throw an
> error and change it to not throw an error anymore. But it's undesirable
> to do the opposite. For that reason, there's may be a tendency to add
> errors for cases like this.
So, I have made it error out when you specify BUFFER_USAGE_LIMIT with
VACUUM FULL or VACUUM ONLY_DATABASE_STATS. However, if you specify
buffer_usage_limit -1 with either of these options, it will not error
out. I don't love this, but I noticed that VACUUM (FULL, PARALLEL 0)
does not error out, while VACUUM (FULL, PARALLEL X) where X > 0 does.
If I want to error out when BUFFER_USAGE_LIMIT specified at all but
still do so at the bottom of ExecVacuum() with the rest of the vacuum
option sanity checking, I will probably need to add a flag bit for
VacuumParams->options.
I was wondering why some "sanity checking" of vacuum options is done in
ExecVacuum() and some in vacuum() (it isn't just split by what is
applicable to autovacuum and what isn't).
I noticed that even in cases where we don't use the strategy object we
still made it, which I thought seemed like a bit of a waste and easy to
fix. I've added a commit which does not make the BufferAccessStrategy
object when VACUUM FULL or VACUUM ONLY_DATABASE_STATS are specified. I
noticed that we also don't use the strategy for VACUUM (PROCESS_MAIN
false, PROCESS_TOAST false), but it didn't seem worth handling this very
specific case, so I didn't.
v8 attached has the prohibitions specified above (including for
vacuumdb, as relevant) as well as some cleanup, added test cases, and
updated documentation.
0001 is essentially unmodified (i.e. I didn't do anything with the other
global variable David mentioned).
I still have a few open questions:
- what the initial value of ring_size for autovacuum should be (see the
one remaining TODO in the code)
- should ANALYZE allow specifying BUFFER_USAGE_LIMIT since it uses the guc
value when that is set?
- should INDEX_CLEANUP off cause VACUUM to use shared buffers and
disable use of a strategy (like failsafe vacuum)
- should we add anything to VACUUM VERBOSE output about the number of
reuses of strategy buffers?
- Should we make BufferAccessStrategyData non-opaque so that we don't
have to add a getter for nbuffers. I could have implemented this in
another way, but I don't really see why BufferAccessStrategyData
should be opaque
- Melanie
From f1398dd6e4649424e754bbe1a8c00b3ea8755cde Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 22 Feb 2023 12:06:41 -0500
Subject: [PATCH v8 1/6] remove global variable vac_strategy
---
src/backend/commands/vacuum.c | 20 +++++++++-----------
1 file changed, 9 insertions(+), 11 deletions(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index c54360a6a0..4f7c132c2f 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -75,7 +75,6 @@ int vacuum_multixact_failsafe_age;
/* A few variables that don't seem worth passing around as parameters */
static MemoryContext vac_context = NULL;
-static BufferAccessStrategy vac_strategy;
/*
@@ -94,7 +93,7 @@ static void vac_truncate_clog(TransactionId frozenXID,
TransactionId lastSaneFrozenXid,
MultiXactId lastSaneMinMulti);
static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params,
- bool skip_privs);
+ bool skip_privs, BufferAccessStrategy bstrategy);
static double compute_parallel_delay(void);
static VacOptValue get_vacoptval_from_boolean(DefElem *def);
static bool vac_tid_reaped(ItemPointer itemptr, void *state);
@@ -338,9 +337,9 @@ vacuum(List *relations, VacuumParams *params,
in_outer_xact = IsInTransactionBlock(isTopLevel);
/*
- * Due to static variables vac_context, anl_context and vac_strategy,
- * vacuum() is not reentrant. This matters when VACUUM FULL or ANALYZE
- * calls a hostile index expression that itself calls ANALYZE.
+ * Due to static variable vac_context vacuum() is not reentrant. This
+ * matters when VACUUM FULL or ANALYZE calls a hostile index expression
+ * that itself calls ANALYZE.
*/
if (in_vacuum)
ereport(ERROR,
@@ -404,7 +403,6 @@ vacuum(List *relations, VacuumParams *params,
bstrategy = GetAccessStrategy(BAS_VACUUM);
MemoryContextSwitchTo(old_context);
}
- vac_strategy = bstrategy;
/*
* Build list of relation(s) to process, putting any new data in
@@ -509,7 +507,7 @@ vacuum(List *relations, VacuumParams *params,
if (params->options & VACOPT_VACUUM)
{
- if (!vacuum_rel(vrel->oid, vrel->relation, params, false))
+ if (!vacuum_rel(vrel->oid, vrel->relation, params, false, bstrategy))
continue;
}
@@ -527,7 +525,7 @@ vacuum(List *relations, VacuumParams *params,
}
analyze_rel(vrel->oid, vrel->relation, params,
- vrel->va_cols, in_outer_xact, vac_strategy);
+ vrel->va_cols, in_outer_xact, bstrategy);
if (use_own_xacts)
{
@@ -1838,7 +1836,7 @@ vac_truncate_clog(TransactionId frozenXID,
* At entry and exit, we are not inside a transaction.
*/
static bool
-vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
+vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs, BufferAccessStrategy bstrategy)
{
LOCKMODE lmode;
Relation rel;
@@ -2084,7 +2082,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
cluster_rel(relid, InvalidOid, &cluster_params);
}
else
- table_relation_vacuum(rel, params, vac_strategy);
+ table_relation_vacuum(rel, params, bstrategy);
}
/* Roll back any GUC changes executed by index functions */
@@ -2118,7 +2116,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
memcpy(&toast_vacuum_params, params, sizeof(VacuumParams));
toast_vacuum_params.options |= VACOPT_PROCESS_MAIN;
- vacuum_rel(toast_relid, NULL, &toast_vacuum_params, true);
+ vacuum_rel(toast_relid, NULL, &toast_vacuum_params, true, bstrategy);
}
/*
--
2.37.2
From 3383a88c0b817244bf19c05ab346291ce0339e0c Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 22 Feb 2023 12:26:01 -0500
Subject: [PATCH v8 3/6] use shared buffers when failsafe active
---
doc/src/sgml/config.sgml | 8 +++++---
src/backend/access/heap/vacuumlazy.c | 8 ++++++++
2 files changed, 13 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fcb53c6997..7b8cf624dc 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9319,9 +9319,11 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
the failsafe to trigger during any <command>VACUUM</command>.
</para>
<para>
- When the failsafe is triggered, any cost-based delay that is
- in effect will no longer be applied, and further non-essential
- maintenance tasks (such as index vacuuming) are bypassed.
+ When the failsafe is triggered, any cost-based delay that is in effect
+ will no longer be applied, further non-essential maintenance tasks
+ (such as index vacuuming) are bypassed, and any Buffer Access Strategy
+ in use will be abandoned and the vacuum will be free to use as many
+ shared buffers as it needs to finish vacuuming the table.
</para>
<para>
The default is 1.6 billion transactions. Although users can
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index ae628d747d..0be41640be 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -2623,6 +2623,14 @@ lazy_check_wraparound_failsafe(LVRelState *vacrel)
{
vacrel->failsafe_active = true;
+ /*
+ * Abandon use of a buffer access strategy when entering failsafe
+ * mode, as completing the ongoing VACUUM is our top priority. Assume
+ * the caller who allocated the memory for the BufferAccessStrategy
+ * object will free it.
+ */
+ vacrel->bstrategy = NULL;
+
/* Disable index vacuuming, index cleanup, and heap rel truncation */
vacrel->do_index_vacuuming = false;
vacrel->do_index_cleanup = false;
--
2.37.2
From d087f002cd8ee34391ec2e7d635e9cfde893c849 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Sun, 19 Mar 2023 18:00:08 -0400
Subject: [PATCH v8 5/6] Add VACUUM BUFFER_USAGE_LIMIT option and GUC
Add GUC, vacuum_buffer_usage_limit, and VACUUM option,
BUFFER_USAGE_LIMIT, through which the user can specify the maximum size
to use for buffers for VACUUM, ANALYZE, and autovacuum. The size is
converted into a number of shared buffers which are tracked in a
BufferAccessStrategyData object. The explicit VACUUM option, when
specified, overrides the GUC value, unless it is specified as -1.
Discussion: https://www.postgresql.org/message-id/flat/20230111182720.ejifsclfwymw2reb%40awork3.anarazel.de
---
doc/src/sgml/config.sgml | 30 ++++++++
doc/src/sgml/ref/analyze.sgml | 10 ++-
doc/src/sgml/ref/vacuum.sgml | 26 +++++++
src/backend/commands/vacuum.c | 62 ++++++++++++++-
src/backend/commands/vacuumparallel.c | 13 +++-
src/backend/postmaster/autovacuum.c | 19 ++++-
src/backend/storage/buffer/README | 21 ++++--
src/backend/storage/buffer/freelist.c | 75 ++++++++++++++++++-
src/backend/utils/init/globals.c | 2 +
src/backend/utils/misc/guc_tables.c | 11 +++
src/backend/utils/misc/postgresql.conf.sample | 4 +
src/bin/psql/tab-complete.c | 2 +-
src/include/commands/vacuum.h | 1 +
src/include/miscadmin.h | 1 +
src/include/storage/bufmgr.h | 8 ++
src/test/regress/expected/vacuum.out | 17 +++++
src/test/regress/sql/vacuum.sql | 13 ++++
17 files changed, 299 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7b8cf624dc..4974e4eaac 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2001,6 +2001,36 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-vacuum-buffer-usage-limit" xreflabel="vacuum_buffer_usage_limit">
+ <term>
+ <varname>vacuum_buffer_usage_limit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_buffer_usage_limit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the size of <varname>shared_buffers</varname> to be reused
+ for each backend participating in a given invocation of
+ <command>VACUUM</command> or <command>ANALYZE</command> or in
+ autovacuum. This size is converted to the number of shared buffers
+ which will be reused as part of a <glossterm
+ linkend="glossary-buffer-access-strategy">Buffer Access
+ Strategy</glossterm>. <literal>0</literal> will disable use of a
+ <literal>Buffer Access Strategy</literal>. <literal>-1</literal> will
+ set the size to a default of <literal>256 kB</literal>. The maximum
+ ring buffer size is <literal>16 GB</literal>. Though you may set
+ <varname>vacuum_buffer_usage_limit</varname> below <literal>128
+ kB</literal>, it will be clamped to <literal>128 kB</literal> at
+ runtime. The default value is <literal>-1</literal>. If this value is
+ specified without units, it is taken as kilobytes. This parameter can
+ be set at any time. It can be overridden for <link
+ linkend="sql-vacuum"><command>VACUUM</command></link> when passing the
+ <command>BUFFER_USAGE_LIMIT</command> parameter.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-logical-decoding-work-mem" xreflabel="logical_decoding_work_mem">
<term><varname>logical_decoding_work_mem</varname> (<type>integer</type>)
<indexterm>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2f94e89cb0..019d34423a 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -51,9 +51,13 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
Without a <replaceable class="parameter">table_and_columns</replaceable>
list, <command>ANALYZE</command> processes every table and materialized view
in the current database that the current user has permission to analyze.
- With a list, <command>ANALYZE</command> processes only those table(s).
- It is further possible to give a list of column names for a table,
- in which case only the statistics for those columns are collected.
+ With a list, <command>ANALYZE</command> processes only those table(s). It is
+ further possible to give a list of column names for a table, in which case
+ only the statistics for those columns are collected.
+ <command>ANALYZE</command> uses a <glossterm
+ linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+ when reading in the sample data. The number of buffers consumed for this can
+ be controlled by <xref linkend="guc-vacuum-buffer-usage-limit"/>.
</para>
<para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index b6d30b5764..df21a35ef5 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -39,6 +39,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
PARALLEL <replaceable class="parameter">integer</replaceable>
SKIP_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
ONLY_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
+ BUFFER_USAGE_LIMIT [ <replaceable class="parameter">string</replaceable> ]
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -345,6 +346,31 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>BUFFER_USAGE_LIMIT</literal></term>
+ <listitem>
+ <para>
+ Specifies the ring buffer size for <command>VACUUM</command>. This size
+ is used to calculate the number of shared buffers which will be reused as
+ part of a <glossterm linkend="glossary-buffer-access-strategy">Buffer
+ Access Strategy</glossterm>. <literal>0</literal> disables use of a
+ <literal>Buffer Access Strategy</literal>. <literal>-1</literal>
+ indicates that <command>VACUUM</command> should fall back to the value
+ specified by <xref linkend="guc-vacuum-buffer-usage-limit"/>. The maximum
+ value is <literal>16 GB</literal>. Though you may specify a size smaller
+ than <literal>128</literal>, the value will be clamped to <literal>128
+ kB</literal> at runtime. If this value is specified without units, it is
+ taken as kilobytes. This size applies to a backend participating in a
+ single invocation of <command>VACUUM</command>. This option can't be used
+ with the <literal>FULL</literal> option or
+ <literal>ONLY_DATABASE_STATS</literal> option. If
+ <literal>ANALYZE</literal> is also specified, the
+ <literal>BUFFER_USAGE_LIMIT</literal> value is used for both the vacuum
+ and analyze stages.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 0cc9c31523..6780ea0a70 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -128,6 +128,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
/* By default parallel vacuum is enabled */
params.nworkers = 0;
+ /* by default use buffer access strategy with default size */
+ params.ring_size = -1;
+
/* Parse options list */
foreach(lc, vacstmt->options)
{
@@ -211,6 +214,42 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
skip_database_stats = defGetBoolean(opt);
else if (strcmp(opt->defname, "only_database_stats") == 0)
only_database_stats = defGetBoolean(opt);
+ else if (strcmp(opt->defname, "buffer_usage_limit") == 0)
+ {
+ char *vac_buffer_size;
+ int result;
+ const char *hintmsg;
+
+ if (opt->arg == NULL)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("buffer_usage_limit option requires a valid value"),
+ parser_errposition(pstate, opt->location)));
+ }
+
+ vac_buffer_size = defGetString(opt);
+
+ if (!parse_int(vac_buffer_size, &result, GUC_UNIT_KB, &hintmsg))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("value: \"%s\": is invalid for buffer_usage_limit",
+ vac_buffer_size),
+ hintmsg ? errhint("%s", _(hintmsg)) : 0));
+ }
+
+ /* check for out-of-bounds */
+ if (result < -1 || result > MAX_BAS_RING_SIZE_KB)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("buffer_usage_limit for a vacuum must be between -1 and %d",
+ MAX_BAS_RING_SIZE_KB)));
+ }
+
+ params.ring_size = result;
+ }
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -242,6 +281,16 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("VACUUM FULL cannot be performed in parallel")));
+ if ((params.options & VACOPT_FULL) && params.ring_size > -1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("BUFFER_USAGE_LIMIT cannot be specified for VACUUM FULL")));
+
+ if ((params.options & VACOPT_ONLY_DATABASE_STATS) && params.ring_size > -1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("BUFFER_USAGE_LIMIT cannot be specified for VACUUM ONLY_DATABASE_STATS")));
+
/*
* Make sure VACOPT_ANALYZE is specified if any column lists are present.
*/
@@ -402,7 +451,18 @@ vacuum(List *relations, VacuumParams *params,
{
MemoryContext old_context = MemoryContextSwitchTo(vac_context);
- bstrategy = GetAccessStrategy(BAS_VACUUM);
+ Assert(params->ring_size >= -1);
+
+ if (params->ring_size == -1)
+ {
+ if (vacuum_buffer_usage_limit == -1)
+ bstrategy = GetAccessStrategy(BAS_VACUUM);
+ else
+ bstrategy = GetAccessStrategyWithSize(BAS_VACUUM, vacuum_buffer_usage_limit);
+ }
+ else
+ bstrategy = GetAccessStrategyWithSize(BAS_VACUUM, params->ring_size);
+
MemoryContextSwitchTo(old_context);
}
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 2cdbd182b6..10f54377d4 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -87,6 +87,12 @@ typedef struct PVShared
*/
int maintenance_work_mem_worker;
+ /*
+ * The number of buffers each worker's Buffer Access Strategy ring should
+ * contain.
+ */
+ int ring_nbuffers;
+
/*
* Shared vacuum cost balance. During parallel vacuum,
* VacuumSharedCostBalance points to this value and it accumulates the
@@ -365,6 +371,9 @@ parallel_vacuum_init(Relation rel, Relation *indrels, int nindexes,
maintenance_work_mem / Min(parallel_workers, nindexes_mwm) :
maintenance_work_mem;
+ /* Use the same buffer size for all workers */
+ shared->ring_nbuffers = bas_nbuffers(bstrategy);
+
pg_atomic_init_u32(&(shared->cost_balance), 0);
pg_atomic_init_u32(&(shared->active_nworkers), 0);
pg_atomic_init_u32(&(shared->idx), 0);
@@ -1018,8 +1027,8 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
pvs.indname = NULL;
pvs.status = PARALLEL_INDVAC_STATUS_INITIAL;
- /* Each parallel VACUUM worker gets its own access strategy */
- pvs.bstrategy = GetAccessStrategy(BAS_VACUUM);
+ /* Each parallel VACUUM worker gets its own access strategy. */
+ pvs.bstrategy = GetAccessStrategyWithNBuffers(BAS_VACUUM, shared->ring_nbuffers);
/* Setup error traceback support for ereport() */
errcallback.callback = parallel_vacuum_error_callback;
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 585d28148c..ce54535692 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2290,9 +2290,15 @@ do_autovacuum(void)
/*
* Create a buffer access strategy object for VACUUM to use. We want to
* use the same one across all the vacuum operations we perform, since the
- * point is for VACUUM not to blow out the shared cache.
+ * point is for VACUUM not to blow out the shared cache. If we later enter
+ * failsafe mode, we will cease use of the BufferAccessStrategy. Either
+ * way, we clean up the BufferAccessStrategy object at the end of this
+ * function.
*/
- bstrategy = GetAccessStrategy(BAS_VACUUM);
+ if (vacuum_buffer_usage_limit == -1)
+ bstrategy = GetAccessStrategy(BAS_VACUUM);
+ else
+ bstrategy = GetAccessStrategyWithSize(BAS_VACUUM, vacuum_buffer_usage_limit);
/*
* create a memory context to act as fake PortalContext, so that the
@@ -2884,6 +2890,15 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age;
tab->at_params.is_wraparound = wraparound;
tab->at_params.log_min_duration = log_min_duration;
+
+ /*
+ * TODO: should this be 0 so that we are sure that vacuum() never
+ * allocates a new bstrategy for us, even if we pass in NULL for that
+ * parameter? maybe could change how failsafe NULLs out bstrategy if
+ * so?
+ */
+ tab->at_params.ring_size = vacuum_buffer_usage_limit;
+
tab->at_vacuum_cost_limit = vac_cost_limit;
tab->at_vacuum_cost_delay = vac_cost_delay;
tab->at_relname = NULL;
diff --git a/src/backend/storage/buffer/README b/src/backend/storage/buffer/README
index a775276ff2..d1be1ca5b7 100644
--- a/src/backend/storage/buffer/README
+++ b/src/backend/storage/buffer/README
@@ -229,12 +229,21 @@ update hint bits). In a scan that modifies every page in the scan, like a
bulk UPDATE or DELETE, the buffers in the ring will always be dirtied and
the ring strategy effectively degrades to the normal strategy.
-VACUUM uses a 256KB ring like sequential scans, but dirty pages are not
-removed from the ring. Instead, WAL is flushed if needed to allow reuse of
-the buffers. Before introducing the buffer ring strategy in 8.3, VACUUM's
-buffers were sent to the freelist, which was effectively a buffer ring of 1
-buffer, resulting in excessive WAL flushing. Allowing VACUUM to update
-256KB between WAL flushes should be more efficient.
+VACUUM's default Buffer Access Strategy uses a 256KB ring like sequential
+scans, but dirty pages are not removed from the ring. Instead, WAL is flushed
+if needed to allow reuse of the buffers. Before introducing the buffer ring
+strategy in 8.3, VACUUM's buffers were sent to the freelist, which was
+effectively a buffer ring of 1 buffer, resulting in excessive WAL flushing.
+Allowing VACUUM to update 256KB between WAL flushes should be more efficient.
+
+As an alternative, VACUUM can use a user-specified ring size. The VACUUM
+parameter "BUFFER_USAGE_LIMIT" and GUC vacuum_buffer_usage_limit can be used to
+specify the amount of shared memory to be used during vacuuming. This size is
+used to calculate the number of buffers in the ring when it is created. A value
+of 0 for vacuum_buffer_usage_limit will disable use of the Buffer Access
+Strategy and allow vacuuming to use shared buffers as normal.
+In failsafe mode, autovacuum will always abandon use of a Buffer Access
+Strategy.
Bulk writes work similarly to VACUUM. Currently this applies only to
COPY IN and CREATE TABLE AS SELECT. (Might it be interesting to make
diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c
index f122709fbe..a387c8b9c6 100644
--- a/src/backend/storage/buffer/freelist.c
+++ b/src/backend/storage/buffer/freelist.c
@@ -531,6 +531,23 @@ StrategyInitialize(bool init)
* ----------------------------------------------------------------
*/
+static inline int
+bufsize_limit_to_nbuffers(int bufsize_limit_kb)
+{
+ int blcksz_kb = BLCKSZ / 1024;
+
+ Assert(blcksz_kb > 0);
+
+ return bufsize_limit_kb / blcksz_kb;
+}
+
+
+int
+bas_nbuffers(BufferAccessStrategy strategy)
+{
+ return strategy->nbuffers;
+}
+
/*
* GetAccessStrategy -- create a BufferAccessStrategy object
@@ -540,7 +557,6 @@ StrategyInitialize(bool init)
BufferAccessStrategy
GetAccessStrategy(BufferAccessStrategyType btype)
{
- BufferAccessStrategy strategy;
int nbuffers;
/*
@@ -574,6 +590,17 @@ GetAccessStrategy(BufferAccessStrategyType btype)
/* Make sure ring isn't an undue fraction of shared buffers */
nbuffers = Min(NBuffers / 8, nbuffers);
+ return GetAccessStrategyWithNBuffers(btype, nbuffers);
+}
+
+
+BufferAccessStrategy
+GetAccessStrategyWithNBuffers(BufferAccessStrategyType btype, int nbuffers)
+{
+ BufferAccessStrategy strategy;
+
+ Assert(nbuffers > 0);
+
/* Allocate the object and initialize all elements to zeroes */
strategy = (BufferAccessStrategy)
palloc0(offsetof(BufferAccessStrategyData, buffers) +
@@ -586,6 +613,52 @@ GetAccessStrategy(BufferAccessStrategyType btype)
return strategy;
}
+
+BufferAccessStrategy
+GetAccessStrategyWithSize(BufferAccessStrategyType btype, int ring_size)
+{
+ int nbuffers;
+ int clamped_nbuffers;
+
+ /* Default nbuffers should have resulted in calling GetAccessStrategy() */
+ Assert(ring_size >= 0);
+
+ if (ring_size == 0)
+ return NULL;
+
+ Assert(ring_size <= MAX_BAS_RING_SIZE_KB);
+
+ if (ring_size < MIN_BAS_RING_SIZE_KB)
+ {
+ ereport(DEBUG1,
+ (errmsg_internal("Buffer Access Strategy ring_size %d kB has been clamped to minimum %d kB",
+ ring_size,
+ MIN_BAS_RING_SIZE_KB)));
+
+ nbuffers = bufsize_limit_to_nbuffers(MIN_BAS_RING_SIZE_KB);
+ }
+ else
+ nbuffers = bufsize_limit_to_nbuffers(ring_size);
+
+ clamped_nbuffers = Min(NBuffers / 8, nbuffers);
+
+ /*
+ * Though default GetAccessStrategy() may also clamp the number of
+ * buffers, only bother warning the user when the input size was
+ * user-specified.
+ */
+ if (clamped_nbuffers < nbuffers)
+ ereport(DEBUG1,
+ (errmsg_internal("active Buffer Access Strategy may use a maximum of %d buffers. %d has been clamped",
+ NBuffers / 8,
+ nbuffers)));
+
+ nbuffers = clamped_nbuffers;
+
+ return GetAccessStrategyWithNBuffers(btype, nbuffers);
+}
+
+
/*
* FreeAccessStrategy -- release a BufferAccessStrategy object
*
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 1b1d814254..6eca3371bd 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -139,6 +139,8 @@ int max_worker_processes = 8;
int max_parallel_workers = 8;
int MaxBackends = 0;
+int vacuum_buffer_usage_limit = -1;
+
int VacuumCostPageHit = 1; /* GUC parameters for vacuum */
int VacuumCostPageMiss = 2;
int VacuumCostPageDirty = 20;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 8062589efd..5476c0f4aa 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2224,6 +2224,17 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"vacuum_buffer_usage_limit", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the buffer pool size for VACUUM and autovacuum."),
+ NULL,
+ GUC_UNIT_KB
+ },
+ &vacuum_buffer_usage_limit,
+ -1, -1, MAX_BAS_RING_SIZE_KB,
+ NULL, NULL, NULL
+ },
+
{
{"shared_memory_size", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows the size of the server's main shared memory area (rounded up to the nearest MB)."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ee49ca3937..91599a4975 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -157,6 +157,10 @@
# mmap
# (change requires restart)
#min_dynamic_shared_memory = 0MB # (change requires restart)
+#vacuum_buffer_usage_limit = -1 # size of vacuum buffer access strategy ring.
+ # -1 to use default,
+ # 0 to disable vacuum buffer access strategy
+ # > 0 to specify size
# - Disk -
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index e38a49e8bd..26947f7928 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4620,7 +4620,7 @@ psql_completion(const char *text, int start, int end)
"DISABLE_PAGE_SKIPPING", "SKIP_LOCKED",
"INDEX_CLEANUP", "PROCESS_MAIN", "PROCESS_TOAST",
"TRUNCATE", "PARALLEL", "SKIP_DATABASE_STATS",
- "ONLY_DATABASE_STATS");
+ "ONLY_DATABASE_STATS", "BUFFER_USAGE_LIMIT");
else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_MAIN|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS"))
COMPLETE_WITH("ON", "OFF");
else if (TailMatches("INDEX_CLEANUP"))
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index bdfd96cfec..5f2a58b2c3 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -236,6 +236,7 @@ typedef struct VacuumParams
* disabled.
*/
int nworkers;
+ int ring_size;
} VacuumParams;
/*
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 06a86f9ac1..b572dfcc6c 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -262,6 +262,7 @@ extern PGDLLIMPORT int work_mem;
extern PGDLLIMPORT double hash_mem_multiplier;
extern PGDLLIMPORT int maintenance_work_mem;
extern PGDLLIMPORT int max_parallel_maintenance_workers;
+extern PGDLLIMPORT int vacuum_buffer_usage_limit;
extern PGDLLIMPORT int VacuumCostPageHit;
extern PGDLLIMPORT int VacuumCostPageMiss;
diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h
index 73762cb1ec..c5d67ad3b9 100644
--- a/src/include/storage/bufmgr.h
+++ b/src/include/storage/bufmgr.h
@@ -101,6 +101,9 @@ extern PGDLLIMPORT int32 *LocalRefCount;
/* upper limit for effective_io_concurrency */
#define MAX_IO_CONCURRENCY 1000
+#define MAX_BAS_RING_SIZE_KB (16 * 1024 * 1024)
+#define MIN_BAS_RING_SIZE_KB 128
+
/* special block number for ReadBuffer() */
#define P_NEW InvalidBlockNumber /* grow the file to get a new page */
@@ -194,7 +197,12 @@ extern Size BufferShmemSize(void);
extern void AtProcExit_LocalBuffers(void);
/* in freelist.c */
+extern int bas_nbuffers(BufferAccessStrategy strategy);
extern BufferAccessStrategy GetAccessStrategy(BufferAccessStrategyType btype);
+
+extern BufferAccessStrategy GetAccessStrategyWithSize(BufferAccessStrategyType btype, int nbuffers);
+
+extern BufferAccessStrategy GetAccessStrategyWithNBuffers(BufferAccessStrategyType btype, int nbuffers);
extern void FreeAccessStrategy(BufferAccessStrategy strategy);
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index e5a312182e..7542129f52 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -350,6 +350,23 @@ SELECT t.relfilenode = :toast_filenode AS is_same_toast_filenode
f
(1 row)
+-- BUFFER_USAGE_LIMIT option
+VACUUM (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;
+-- works with PARALLEL option
+VACUUM (BUFFER_USAGE_LIMIT '512 kB', PARALLEL 2) vac_option_tab;
+-- integer overflow error
+VACUUM (BUFFER_USAGE_LIMIT 10000000000) vac_option_tab;
+ERROR: value: "10000000000": is invalid for buffer_usage_limit
+HINT: Value exceeds integer range.
+-- value exceeds ring size max error
+VACUUM (BUFFER_USAGE_LIMIT '17 GB') vac_option_tab;
+ERROR: buffer_usage_limit for a vacuum must be between -1 and 16777216
+-- incompatible with VACUUM FULL error
+VACUUM (BUFFER_USAGE_LIMIT '512 kB', FULL) vac_option_tab;
+ERROR: BUFFER_USAGE_LIMIT cannot be specified for VACUUM FULL
+-- incompatible with VACUUM ONLY_DATABASE_STATS error
+VACUUM (BUFFER_USAGE_LIMIT '512 kB', ONLY_DATABASE_STATS) vac_option_tab;
+ERROR: BUFFER_USAGE_LIMIT cannot be specified for VACUUM ONLY_DATABASE_STATS
-- SKIP_DATABASE_STATS option
VACUUM (SKIP_DATABASE_STATS) vactst;
-- ONLY_DATABASE_STATS option
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index a1fad43657..a3a99ff56c 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -272,6 +272,19 @@ SELECT t.relfilenode = :toast_filenode AS is_same_toast_filenode
FROM pg_class c, pg_class t
WHERE c.reltoastrelid = t.oid AND c.relname = 'vac_option_tab';
+-- BUFFER_USAGE_LIMIT option
+VACUUM (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;
+-- works with PARALLEL option
+VACUUM (BUFFER_USAGE_LIMIT '512 kB', PARALLEL 2) vac_option_tab;
+-- integer overflow error
+VACUUM (BUFFER_USAGE_LIMIT 10000000000) vac_option_tab;
+-- value exceeds ring size max error
+VACUUM (BUFFER_USAGE_LIMIT '17 GB') vac_option_tab;
+-- incompatible with VACUUM FULL error
+VACUUM (BUFFER_USAGE_LIMIT '512 kB', FULL) vac_option_tab;
+-- incompatible with VACUUM ONLY_DATABASE_STATS error
+VACUUM (BUFFER_USAGE_LIMIT '512 kB', ONLY_DATABASE_STATS) vac_option_tab;
+
-- SKIP_DATABASE_STATS option
VACUUM (SKIP_DATABASE_STATS) vactst;
--
2.37.2
From 8f0af2e3b7f2db10f8d5c4013da7dcf71b6a3d0d Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Sun, 2 Apr 2023 10:28:09 -0400
Subject: [PATCH v8 2/6] Don't make vacuum strategy ring when unused
VACUUM FULL and VACUUM ONLY_DATABASE_STATS will not use the vacuum
strategy ring created in vacuum(), so don't waste time and memory making
it. It is worth noting that VACUUM (PROCESS_MAIN false, PROCESS_TOAST
false) will also not use it, but that doesn't seem worth handling
specifically.
---
src/backend/commands/vacuum.c | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 4f7c132c2f..0cc9c31523 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -396,7 +396,9 @@ vacuum(List *relations, VacuumParams *params,
* If caller didn't give us a buffer strategy object, make one in the
* cross-transaction memory context.
*/
- if (bstrategy == NULL)
+ if (bstrategy == NULL &&
+ !(params->options & VACOPT_ONLY_DATABASE_STATS ||
+ params->options & VACOPT_FULL))
{
MemoryContext old_context = MemoryContextSwitchTo(vac_context);
--
2.37.2
From e266f181f4b7665cb663a2b4a1e6872d45526f39 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Sun, 19 Mar 2023 13:15:24 -0400
Subject: [PATCH v8 4/6] Rename Buffer Access Strategy->ring_size nbuffers
ring_size sounds like it is in units of bytes when it is actually a
number of buffers. Rename it to nbuffers to make future commit related
to ring size less confusing.
---
src/backend/storage/buffer/freelist.c | 18 +++++++++---------
1 file changed, 9 insertions(+), 9 deletions(-)
diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c
index c690d5f15f..f122709fbe 100644
--- a/src/backend/storage/buffer/freelist.c
+++ b/src/backend/storage/buffer/freelist.c
@@ -74,7 +74,7 @@ typedef struct BufferAccessStrategyData
/* Overall strategy type */
BufferAccessStrategyType btype;
/* Number of elements in buffers[] array */
- int ring_size;
+ int nbuffers;
/*
* Index of the "current" slot in the ring, ie, the one most recently
@@ -541,7 +541,7 @@ BufferAccessStrategy
GetAccessStrategy(BufferAccessStrategyType btype)
{
BufferAccessStrategy strategy;
- int ring_size;
+ int nbuffers;
/*
* Select ring size to use. See buffer/README for rationales.
@@ -556,13 +556,13 @@ GetAccessStrategy(BufferAccessStrategyType btype)
return NULL;
case BAS_BULKREAD:
- ring_size = 256 * 1024 / BLCKSZ;
+ nbuffers = 256 * 1024 / BLCKSZ;
break;
case BAS_BULKWRITE:
- ring_size = 16 * 1024 * 1024 / BLCKSZ;
+ nbuffers = 16 * 1024 * 1024 / BLCKSZ;
break;
case BAS_VACUUM:
- ring_size = 256 * 1024 / BLCKSZ;
+ nbuffers = 256 * 1024 / BLCKSZ;
break;
default:
@@ -572,16 +572,16 @@ GetAccessStrategy(BufferAccessStrategyType btype)
}
/* Make sure ring isn't an undue fraction of shared buffers */
- ring_size = Min(NBuffers / 8, ring_size);
+ nbuffers = Min(NBuffers / 8, nbuffers);
/* Allocate the object and initialize all elements to zeroes */
strategy = (BufferAccessStrategy)
palloc0(offsetof(BufferAccessStrategyData, buffers) +
- ring_size * sizeof(Buffer));
+ nbuffers * sizeof(Buffer));
/* Set fields that don't start out zero */
strategy->btype = btype;
- strategy->ring_size = ring_size;
+ strategy->nbuffers = nbuffers;
return strategy;
}
@@ -615,7 +615,7 @@ GetBufferFromRing(BufferAccessStrategy strategy, uint32 *buf_state)
/* Advance to next ring slot */
- if (++strategy->current >= strategy->ring_size)
+ if (++strategy->current >= strategy->nbuffers)
strategy->current = 0;
/*
--
2.37.2
From f59309c221f4ff776d0a6ebee5e1152300a884a1 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Sun, 19 Mar 2023 18:00:28 -0400
Subject: [PATCH v8 6/6] Add buffer-usage-limit option to vacuumdb
---
doc/src/sgml/ref/vacuumdb.sgml | 12 ++++++++++++
src/bin/scripts/vacuumdb.c | 23 +++++++++++++++++++++++
src/include/commands/vacuum.h | 3 +++
3 files changed, 38 insertions(+)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 74bac2d4ba..8cedef8d79 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -278,6 +278,18 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--buffer-usage-limit <replaceable class="parameter">buffer_usage_limit</replaceable></option></term>
+ <listitem>
+ <para>
+ The size of the ring buffer used for vacuuming. This size is used to
+ calculate the number of shared buffers which will be reused as part of
+ a <glossterm linkend="glossary-buffer-access-strategy">Buffer Access
+ Strategy</glossterm>. See <xref linkend="sql-vacuum"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
<term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 39be265b5b..941eac7727 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -46,6 +46,7 @@ typedef struct vacuumingOptions
bool process_main;
bool process_toast;
bool skip_database_stats;
+ char *buffer_usage_limit;
} vacuumingOptions;
/* object filter options */
@@ -123,6 +124,7 @@ main(int argc, char *argv[])
{"no-truncate", no_argument, NULL, 10},
{"no-process-toast", no_argument, NULL, 11},
{"no-process-main", no_argument, NULL, 12},
+ {"buffer-usage-limit", required_argument, NULL, 13},
{NULL, 0, NULL, 0}
};
@@ -147,6 +149,7 @@ main(int argc, char *argv[])
/* initialize options */
memset(&vacopts, 0, sizeof(vacopts));
vacopts.parallel_workers = -1;
+ vacopts.buffer_usage_limit = NULL;
vacopts.no_index_cleanup = false;
vacopts.force_index_cleanup = false;
vacopts.do_truncate = true;
@@ -266,6 +269,9 @@ main(int argc, char *argv[])
case 12:
vacopts.process_main = false;
break;
+ case 13:
+ vacopts.buffer_usage_limit = pg_strdup(optarg);
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -343,6 +349,11 @@ main(int argc, char *argv[])
pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
"no-index-cleanup", "force-index-cleanup");
+ /* buffer-usage-limit doesn't make sense with VACUUM FULL */
+ if (vacopts.buffer_usage_limit && vacopts.full)
+ pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
+ "buffer-usage-limit", "full");
+
/* fill cparams except for dbname, which is set below */
cparams.pghost = host;
cparams.pgport = port;
@@ -550,6 +561,10 @@ vacuum_one_database(ConnParams *cparams,
pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
"--parallel", "13");
+ if (vacopts->buffer_usage_limit && PQserverVersion(conn) < 160000)
+ pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+ "--buffer-usage-limit", "16");
+
/* skip_database_stats is used automatically if server supports it */
vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
@@ -1048,6 +1063,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
vacopts->parallel_workers);
sep = comma;
}
+ if (vacopts->buffer_usage_limit)
+ {
+ Assert(serverVersion >= 160000);
+ appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
+ vacopts->buffer_usage_limit);
+ sep = comma;
+ }
if (sep != paren)
appendPQExpBufferChar(sql, ')');
}
@@ -1111,6 +1133,7 @@ help(const char *progname)
printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
+ printf(_(" --buffer-usage-limit=BUFSIZE size of ring buffer used for vacuum\n"));
printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
printf(_(" --no-process-main skip the main relation\n"));
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 5f2a58b2c3..d8fba51d82 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -213,6 +213,9 @@ typedef enum VacOptValue
*
* Note that at least one of VACOPT_VACUUM and VACOPT_ANALYZE must be set
* in options.
+ *
+ * When adding a new VacuumParam member, consider adding it to vacuumdb as
+ * well.
*/
typedef struct VacuumParams
{
--
2.37.2
