Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 1cbbee0

Browse filesBrowse files
david-rowleydavid-rowley
committed
Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option
Add new options to the VACUUM and ANALYZE commands called BUFFER_USAGE_LIMIT to allow users more control over how large to make the buffer access strategy that is used to limit the usage of buffers in shared buffers. Larger rings can allow VACUUM to run more quickly but have the drawback of VACUUM possibly evicting more buffers from shared buffers that might be useful for other queries running on the database. Here we also add a new GUC named vacuum_buffer_usage_limit which controls how large to make the access strategy when it's not specified in the VACUUM/ANALYZE command. This defaults to 256KB, which is the same size as the access strategy was prior to this change. This setting also controls how large to make the buffer access strategy for autovacuum. Per idea by Andres Freund. Author: Melanie Plageman Reviewed-by: David Rowley Reviewed-by: Andres Freund Reviewed-by: Justin Pryzby Reviewed-by: Bharath Rupireddy Discussion: https://postgr.es/m/20230111182720.ejifsclfwymw2reb@awork3.anarazel.de
1 parent 5279e9d commit 1cbbee0
Copy full SHA for 1cbbee0

File tree

17 files changed

+322
-25
lines changed
Filter options

17 files changed

+322
-25
lines changed

‎doc/src/sgml/config.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/config.sgml
+30
Original file line numberDiff line numberDiff line change
@@ -2001,6 +2001,36 @@ include_dir 'conf.d'
20012001
</listitem>
20022002
</varlistentry>
20032003

2004+
<varlistentry id="guc-vacuum-buffer-usage-limit" xreflabel="vacuum_buffer_usage_limit">
2005+
<term>
2006+
<varname>vacuum_buffer_usage_limit</varname> (<type>integer</type>)
2007+
<indexterm>
2008+
<primary><varname>vacuum_buffer_usage_limit</varname> configuration parameter</primary>
2009+
</indexterm>
2010+
</term>
2011+
<listitem>
2012+
<para>
2013+
Specifies the size of the
2014+
<glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
2015+
used by the <command>VACUUM</command> and <command>ANALYZE</command>
2016+
commands. A setting of <literal>0</literal> will allow the operation
2017+
to use any number of <varname>shared_buffers</varname>. Otherwise
2018+
valid sizes range from <literal>128 KB</literal> to
2019+
<literal>16 GB</literal>. If the specified size would exceed 1/8 the
2020+
size of <varname>shared_buffers</varname>, the size is silently capped
2021+
to that value. The default value is <literal>256 KB</literal>. If
2022+
this value is specified without units, it is taken as kilobytes. This
2023+
parameter can be set at any time. It can be overridden for
2024+
<xref linkend="sql-vacuum"/> and <xref linkend="sql-analyze"/>
2025+
when passing the <option>BUFFER_USAGE_LIMIT</option> option. Higher
2026+
settings can allow <command>VACUUM</command> and
2027+
<command>ANALYZE</command> to run more quickly, but having too large a
2028+
setting may cause too many other useful pages to be evicted from
2029+
shared buffers.
2030+
</para>
2031+
</listitem>
2032+
</varlistentry>
2033+
20042034
<varlistentry id="guc-logical-decoding-work-mem" xreflabel="logical_decoding_work_mem">
20052035
<term><varname>logical_decoding_work_mem</varname> (<type>integer</type>)
20062036
<indexterm>

‎doc/src/sgml/ref/analyze.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/analyze.sgml
+20
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
2828

2929
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
3030
SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
31+
BUFFER_USAGE_LIMIT [ <replaceable class="parameter">string</replaceable> ]
3132

3233
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
3334

@@ -95,6 +96,25 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
9596
</listitem>
9697
</varlistentry>
9798

99+
<varlistentry>
100+
<term><literal>BUFFER_USAGE_LIMIT</literal></term>
101+
<listitem>
102+
<para>
103+
Specifies the
104+
<glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
105+
ring buffer size for <command>ANALYZE</command>. This size is used to
106+
calculate the number of shared buffers which will be reused as part of
107+
this strategy. <literal>0</literal> disables use of a
108+
<literal>Buffer Access Strategy</literal>. When this option is not
109+
specified, <command>ANALYZE</command> uses the value from
110+
<xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can
111+
allow <command>ANALYZE</command> to run more quickly, but having too
112+
large a setting may cause too many other useful pages to be evicted from
113+
shared buffers.
114+
</para>
115+
</listitem>
116+
</varlistentry>
117+
98118
<varlistentry>
99119
<term><replaceable class="parameter">boolean</replaceable></term>
100120
<listitem>

‎doc/src/sgml/ref/vacuum.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/vacuum.sgml
+24
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
3939
PARALLEL <replaceable class="parameter">integer</replaceable>
4040
SKIP_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
4141
ONLY_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
42+
BUFFER_USAGE_LIMIT [ <replaceable class="parameter">string</replaceable> ]
4243

4344
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
4445

@@ -345,6 +346,29 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
345346
</listitem>
346347
</varlistentry>
347348

349+
<varlistentry>
350+
<term><literal>BUFFER_USAGE_LIMIT</literal></term>
351+
<listitem>
352+
<para>
353+
Specifies the
354+
<glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
355+
ring buffer size for <command>VACUUM</command>. This size is used to
356+
calculate the number of shared buffers which will be reused as part of
357+
this strategy. <literal>0</literal> disables use of a
358+
<literal>Buffer Access Strategy</literal>. If <option>ANALYZE</option>
359+
is also specified, the <option>BUFFER_USAGE_LIMIT</option> value is used
360+
for both the vacuum and analyze stages. This option can't be used with
361+
the <option>FULL</option> option except if <option>ANALYZE</option> is
362+
also specified. When this option is not specified,
363+
<command>VACUUM</command> uses the value from
364+
<xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can
365+
allow <command>VACUUM</command> to run more quickly, but having too
366+
large a setting may cause too many other useful pages to be evicted from
367+
shared buffers.
368+
</para>
369+
</listitem>
370+
</varlistentry>
371+
348372
<varlistentry>
349373
<term><replaceable class="parameter">boolean</replaceable></term>
350374
<listitem>

‎src/backend/commands/vacuum.c

Copy file name to clipboardExpand all lines: src/backend/commands/vacuum.c
+94-1
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,7 @@
5757
#include "utils/acl.h"
5858
#include "utils/fmgroids.h"
5959
#include "utils/guc.h"
60+
#include "utils/guc_hooks.h"
6061
#include "utils/memutils.h"
6162
#include "utils/pg_rusage.h"
6263
#include "utils/snapmgr.h"
@@ -120,6 +121,26 @@ static VacOptValue get_vacoptval_from_boolean(DefElem *def);
120121
static bool vac_tid_reaped(ItemPointer itemptr, void *state);
121122
static int vac_cmp_itemptr(const void *left, const void *right);
122123

124+
/*
125+
* GUC check function to ensure GUC value specified is within the allowable
126+
* range.
127+
*/
128+
bool
129+
check_vacuum_buffer_usage_limit(int *newval, void **extra,
130+
GucSource source)
131+
{
132+
/* Value upper and lower hard limits are inclusive */
133+
if (*newval == 0 || (*newval >= MIN_BAS_VAC_RING_SIZE_KB &&
134+
*newval <= MAX_BAS_VAC_RING_SIZE_KB))
135+
return true;
136+
137+
/* Value does not fall within any allowable range */
138+
GUC_check_errdetail("\"vacuum_buffer_usage_limit\" must be 0 or between %d KB and %d KB",
139+
MIN_BAS_VAC_RING_SIZE_KB, MAX_BAS_VAC_RING_SIZE_KB);
140+
141+
return false;
142+
}
143+
123144
/*
124145
* Primary entry point for manual VACUUM and ANALYZE commands
125146
*
@@ -139,6 +160,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
139160
bool disable_page_skipping = false;
140161
bool process_main = true;
141162
bool process_toast = true;
163+
int ring_size;
142164
bool skip_database_stats = false;
143165
bool only_database_stats = false;
144166
MemoryContext vac_context;
@@ -151,6 +173,12 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
151173
/* By default parallel vacuum is enabled */
152174
params.nworkers = 0;
153175

176+
/*
177+
* Set this to an invalid value so it is clear whether or not a
178+
* BUFFER_USAGE_LIMIT was specified when making the access strategy.
179+
*/
180+
ring_size = -1;
181+
154182
/* Parse options list */
155183
foreach(lc, vacstmt->options)
156184
{
@@ -161,6 +189,48 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
161189
verbose = defGetBoolean(opt);
162190
else if (strcmp(opt->defname, "skip_locked") == 0)
163191
skip_locked = defGetBoolean(opt);
192+
else if (strcmp(opt->defname, "buffer_usage_limit") == 0)
193+
{
194+
const char *hintmsg;
195+
int result;
196+
char *vac_buffer_size;
197+
198+
if (opt->arg == NULL)
199+
{
200+
ereport(ERROR,
201+
(errcode(ERRCODE_SYNTAX_ERROR),
202+
errmsg("buffer_usage_limit option requires a valid value"),
203+
parser_errposition(pstate, opt->location)));
204+
}
205+
206+
vac_buffer_size = defGetString(opt);
207+
208+
if (!parse_int(vac_buffer_size, &result, GUC_UNIT_KB, &hintmsg))
209+
{
210+
ereport(ERROR,
211+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
212+
errmsg("value: \"%s\": is invalid for buffer_usage_limit",
213+
vac_buffer_size),
214+
hintmsg ? errhint("%s", _(hintmsg)) : 0));
215+
}
216+
217+
/*
218+
* Check that the specified size falls within the hard upper and
219+
* lower limits if it is not 0. We explicitly disallow -1 since
220+
* that behavior can be obtained by not specifying
221+
* BUFFER_USAGE_LIMIT.
222+
*/
223+
if (result != 0 &&
224+
(result < MIN_BAS_VAC_RING_SIZE_KB || result > MAX_BAS_VAC_RING_SIZE_KB))
225+
{
226+
ereport(ERROR,
227+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
228+
errmsg("buffer_usage_limit option must be 0 or between %d KB and %d KB",
229+
MIN_BAS_VAC_RING_SIZE_KB, MAX_BAS_VAC_RING_SIZE_KB)));
230+
}
231+
232+
ring_size = result;
233+
}
164234
else if (!vacstmt->is_vacuumcmd)
165235
ereport(ERROR,
166236
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -265,6 +335,17 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
265335
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
266336
errmsg("VACUUM FULL cannot be performed in parallel")));
267337

338+
/*
339+
* BUFFER_USAGE_LIMIT does nothing for VACUUM (FULL) so just raise an
340+
* ERROR for that case. VACUUM (FULL, ANALYZE) does make use of it, so
341+
* we'll permit that.
342+
*/
343+
if (ring_size != -1 && (params.options & VACOPT_FULL) &&
344+
!(params.options & VACOPT_ANALYZE))
345+
ereport(ERROR,
346+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
347+
errmsg("BUFFER_USAGE_LIMIT cannot be specified for VACUUM FULL")));
348+
268349
/*
269350
* Make sure VACOPT_ANALYZE is specified if any column lists are present.
270351
*/
@@ -366,7 +447,19 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
366447

367448
MemoryContext old_context = MemoryContextSwitchTo(vac_context);
368449

369-
bstrategy = GetAccessStrategy(BAS_VACUUM);
450+
Assert(ring_size >= -1);
451+
452+
/*
453+
* If BUFFER_USAGE_LIMIT was specified by the VACUUM or ANALYZE
454+
* command, it overrides the value of VacuumBufferUsageLimit. Either
455+
* value may be 0, in which case GetAccessStrategyWithSize() will
456+
* return NULL, effectively allowing full use of shared buffers.
457+
*/
458+
if (ring_size == -1)
459+
ring_size = VacuumBufferUsageLimit;
460+
461+
bstrategy = GetAccessStrategyWithSize(BAS_VACUUM, ring_size);
462+
370463
MemoryContextSwitchTo(old_context);
371464
}
372465

‎src/backend/commands/vacuumparallel.c

Copy file name to clipboardExpand all lines: src/backend/commands/vacuumparallel.c
+12-2
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,12 @@ typedef struct PVShared
8787
*/
8888
int maintenance_work_mem_worker;
8989

90+
/*
91+
* The number of buffers each worker's Buffer Access Strategy ring should
92+
* contain.
93+
*/
94+
int ring_nbuffers;
95+
9096
/*
9197
* Shared vacuum cost balance. During parallel vacuum,
9298
* VacuumSharedCostBalance points to this value and it accumulates the
@@ -365,6 +371,9 @@ parallel_vacuum_init(Relation rel, Relation *indrels, int nindexes,
365371
maintenance_work_mem / Min(parallel_workers, nindexes_mwm) :
366372
maintenance_work_mem;
367373

374+
/* Use the same buffer size for all workers */
375+
shared->ring_nbuffers = GetAccessStrategyBufferCount(bstrategy);
376+
368377
pg_atomic_init_u32(&(shared->cost_balance), 0);
369378
pg_atomic_init_u32(&(shared->active_nworkers), 0);
370379
pg_atomic_init_u32(&(shared->idx), 0);
@@ -1018,8 +1027,9 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
10181027
pvs.indname = NULL;
10191028
pvs.status = PARALLEL_INDVAC_STATUS_INITIAL;
10201029

1021-
/* Each parallel VACUUM worker gets its own access strategy */
1022-
pvs.bstrategy = GetAccessStrategy(BAS_VACUUM);
1030+
/* Each parallel VACUUM worker gets its own access strategy. */
1031+
pvs.bstrategy = GetAccessStrategyWithSize(BAS_VACUUM,
1032+
shared->ring_nbuffers * (BLCKSZ / 1024));
10231033

10241034
/* Setup error traceback support for ereport() */
10251035
errcallback.callback = parallel_vacuum_error_callback;

‎src/backend/postmaster/autovacuum.c

Copy file name to clipboardExpand all lines: src/backend/postmaster/autovacuum.c
+14-4
Original file line numberDiff line numberDiff line change
@@ -2349,11 +2349,21 @@ do_autovacuum(void)
23492349
}
23502350

23512351
/*
2352-
* Create a buffer access strategy object for VACUUM to use. We want to
2353-
* use the same one across all the vacuum operations we perform, since the
2354-
* point is for VACUUM not to blow out the shared cache.
2352+
* Optionally, create a buffer access strategy object for VACUUM to use.
2353+
* We use the same BufferAccessStrategy object for all tables VACUUMed by
2354+
* this worker to prevent autovacuum from blowing out shared buffers.
2355+
*
2356+
* VacuumBufferUsageLimit being set to 0 results in
2357+
* GetAccessStrategyWithSize returning NULL, effectively meaning we can
2358+
* use up to all of shared buffers.
2359+
*
2360+
* If we later enter failsafe mode on any of the tables being vacuumed, we
2361+
* will cease use of the BufferAccessStrategy only for that table.
2362+
*
2363+
* XXX should we consider adding code to adjust the size of this if
2364+
* VacuumBufferUsageLimit changes?
23552365
*/
2356-
bstrategy = GetAccessStrategy(BAS_VACUUM);
2366+
bstrategy = GetAccessStrategyWithSize(BAS_VACUUM, VacuumBufferUsageLimit);
23572367

23582368
/*
23592369
* create a memory context to act as fake PortalContext, so that the

‎src/backend/storage/buffer/README

Copy file name to clipboardExpand all lines: src/backend/storage/buffer/README
+6-6
Original file line numberDiff line numberDiff line change
@@ -229,12 +229,12 @@ update hint bits). In a scan that modifies every page in the scan, like a
229229
bulk UPDATE or DELETE, the buffers in the ring will always be dirtied and
230230
the ring strategy effectively degrades to the normal strategy.
231231

232-
VACUUM uses a 256KB ring like sequential scans, but dirty pages are not
233-
removed from the ring. Instead, WAL is flushed if needed to allow reuse of
234-
the buffers. Before introducing the buffer ring strategy in 8.3, VACUUM's
235-
buffers were sent to the freelist, which was effectively a buffer ring of 1
236-
buffer, resulting in excessive WAL flushing. Allowing VACUUM to update
237-
256KB between WAL flushes should be more efficient.
232+
VACUUM uses a ring like sequential scans, however, the size of this ring is
233+
controlled by the vacuum_buffer_usage_limit GUC. Dirty pages are not removed
234+
from the ring. Instead, WAL is flushed if needed to allow reuse of the
235+
buffers. Before introducing the buffer ring strategy in 8.3, VACUUM's buffers
236+
were sent to the freelist, which was effectively a buffer ring of 1 buffer,
237+
resulting in excessive WAL flushing.
238238

239239
Bulk writes work similarly to VACUUM. Currently this applies only to
240240
COPY IN and CREATE TABLE AS SELECT. (Might it be interesting to make

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.