From ea1fbd78532c7ee898dd4d84c197f8422debaf43 Mon Sep 17 00:00:00 2001 From: Melanie Plageman Date: Sun, 19 Mar 2023 18:00:08 -0400 Subject: [PATCH v6 5/6] Add vacuum[db] buffer usage limit option and guc --- doc/src/sgml/config.sgml | 26 ++++++++ doc/src/sgml/ref/vacuum.sgml | 23 ++++++++ src/backend/commands/vacuum.c | 51 +++++++++++++++- src/backend/commands/vacuumparallel.c | 8 ++- src/backend/postmaster/autovacuum.c | 17 +++++- src/backend/storage/buffer/README | 21 +++++-- src/backend/storage/buffer/freelist.c | 59 +++++++++++++++++++ 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 | 5 ++ 14 files changed, 221 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index fca38a4514..1bf0050d8d 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1981,6 +1981,32 @@ include_dir 'conf.d' + + + vacuum_buffer_usage_limit (integer) + + vacuum_buffer_usage_limit configuration parameter + + + + + Specifies the size of shared_buffers to be reused + for a given invocation of VACUUM or instance of + autovacuum. This size is converted to the number of shared buffers + which will be reused as part of a Buffer Access + Strategy. 0 will disable use of a + Buffer Access Strategy. -1 will + set the size to a default of 256 kB. The maximum + ring buffer size is 16 GB. Though you may set + vacuum_buffer_usage_limit below 128 + kB, it will be clamped to 128 kB at + runtime. The default value is -1. If this value is + specified without units, it is taken as kilobytes. This parameter can + be set at any time. + + + + logical_decoding_work_mem (integer) diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index b6d30b5764..8ab89cfa3c 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -39,6 +39,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ integer SKIP_DATABASE_STATS [ boolean ] ONLY_DATABASE_STATS [ boolean ] + BUFFER_USAGE_LIMIT [ string ] and table_and_columns is: @@ -345,6 +346,28 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ . The maximum value is + 16 GB. Though you may specify a size smaller than + 128, the value will be clamped to 128 + kB at runtime. If this value is specified without units, it is + taken as kilobytes. This size applies to a single invocation of + VACUUM. Parallel VACUUM workers use the default Buffer + Access Strategy ring size during index scan and index cleanup phases of + VACUUM. + + + + boolean diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index a6aac30529..f434f07dd1 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,43 @@ 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), @@ -400,7 +440,16 @@ vacuum(List *relations, VacuumParams *params, { MemoryContext old_context = MemoryContextSwitchTo(vac_context); - bstrategy = GetAccessStrategy(BAS_VACUUM); + 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 bcd40c80a1..4c19fc095e 100644 --- a/src/backend/commands/vacuumparallel.c +++ b/src/backend/commands/vacuumparallel.c @@ -1012,7 +1012,13 @@ 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 */ + /* + * Each parallel VACUUM worker gets its own access strategy + * For now, use the default buffer access strategy ring size. + * TODO: should each parallel VACUUM worker doing index vacuum get a ring + * of the full custom size if we are doing that? Or should we split it + * amongst them? Or should we just use the default? + */ pvs.bstrategy = GetAccessStrategy(BAS_VACUUM); /* Setup error traceback support for ereport() */ diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index c0e2e00a7e..a8657b0b32 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2291,8 +2291,14 @@ 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. + * 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 @@ -2881,6 +2887,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..f0a1b59e2c 100644 --- a/src/backend/storage/buffer/freelist.c +++ b/src/backend/storage/buffer/freelist.c @@ -586,6 +586,65 @@ GetAccessStrategy(BufferAccessStrategyType btype) return strategy; } +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; +} + +BufferAccessStrategy +GetAccessStrategyWithSize(BufferAccessStrategyType btype, int ring_size) +{ + BufferAccessStrategy strategy; + int nbuffers; + int clamped_nbuffers; + + /* Default nbuffers should have resulted in calling GetAccessStrategy() */ + Assert(ring_size != -1); + + 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); + + 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; + + /* Allocate the object and initialize all elements to zeroes */ + strategy = (BufferAccessStrategy) + palloc0(offsetof(BufferAccessStrategyData, buffers) + + nbuffers * sizeof(Buffer)); + + /* Set fields that don't start out zero */ + strategy->btype = btype; + strategy->nbuffers = nbuffers; + + return strategy; +} + /* * 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 1c0583fe26..883ee29d14 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -2206,6 +2206,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 d06074b86f..e55a1008f1 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -156,6 +156,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 42e87b9e49..6fd80dd3c3 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 b8a18b8081..338de38568 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 */ @@ -196,6 +199,8 @@ extern void AtProcExit_LocalBuffers(void); /* in freelist.c */ extern BufferAccessStrategy GetAccessStrategy(BufferAccessStrategyType btype); + +extern BufferAccessStrategy GetAccessStrategyWithSize(BufferAccessStrategyType btype, int nbuffers); extern void FreeAccessStrategy(BufferAccessStrategy strategy); -- 2.37.2