From 11706c4735c4948a99974a364c3df2ca0e2eab76 Mon Sep 17 00:00:00 2001 From: Melanie Plageman Date: Sun, 19 Mar 2023 18:00:08 -0400 Subject: [PATCH v7 4/5] Add vacuum[db] buffer usage limit option and guc --- doc/src/sgml/config.sgml | 26 ++++++++ doc/src/sgml/ref/vacuum.sgml | 21 ++++++ src/backend/commands/vacuum.c | 51 ++++++++++++++- src/backend/commands/vacuumparallel.c | 13 +++- src/backend/postmaster/autovacuum.c | 19 +++++- src/backend/storage/buffer/README | 21 ++++-- src/backend/storage/buffer/freelist.c | 65 ++++++++++++++++++- 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 +++ 14 files changed, 232 insertions(+), 13 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 7b8cf624dc..dcae06ff05 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -2001,6 +2001,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 each backend participating in 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..a8c169e11e 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,26 @@ 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 backend participating in a + single invocation 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..7ff4421596 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 @@ -361,6 +367,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); @@ -1012,8 +1021,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..8514da0c65 100644 --- a/src/backend/storage/buffer/freelist.c +++ b/src/backend/storage/buffer/freelist.c @@ -531,6 +531,22 @@ 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 +556,6 @@ StrategyInitialize(bool init) BufferAccessStrategy GetAccessStrategy(BufferAccessStrategyType btype) { - BufferAccessStrategy strategy; int nbuffers; /* @@ -574,6 +589,14 @@ 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; + /* Allocate the object and initialize all elements to zeroes */ strategy = (BufferAccessStrategy) palloc0(offsetof(BufferAccessStrategyData, buffers) + @@ -586,6 +609,46 @@ 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 != -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; + + 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); -- 2.37.2