From 9b5cbbc0c8f892dde3e220f0945b2c1e0d175b84 Mon Sep 17 00:00:00 2001 From: Melanie Plageman Date: Sun, 5 Mar 2023 14:39:16 -0500 Subject: [PATCH v2] Reload config file more often while vacuuming --- src/backend/commands/vacuum.c | 38 ++++++++--- src/backend/postmaster/autovacuum.c | 97 ++++++++++++++++++++++------- src/include/postmaster/autovacuum.h | 2 + 3 files changed, 104 insertions(+), 33 deletions(-) diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index aa79d9de4d..f6cea30168 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -48,6 +48,7 @@ #include "pgstat.h" #include "postmaster/autovacuum.h" #include "postmaster/bgworker_internals.h" +#include "postmaster/interrupt.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/proc.h" @@ -75,6 +76,7 @@ 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; +static bool analyze_in_outer_xact = false; /* @@ -309,8 +311,7 @@ vacuum(List *relations, VacuumParams *params, static bool in_vacuum = false; const char *stmttype; - volatile bool in_outer_xact, - use_own_xacts; + volatile bool use_own_xacts; Assert(params != NULL); @@ -327,10 +328,10 @@ vacuum(List *relations, VacuumParams *params, if (params->options & VACOPT_VACUUM) { PreventInTransactionBlock(isTopLevel, stmttype); - in_outer_xact = false; + analyze_in_outer_xact = false; } else - in_outer_xact = IsInTransactionBlock(isTopLevel); + analyze_in_outer_xact = IsInTransactionBlock(isTopLevel); /* * Due to static variables vac_context, anl_context and vac_strategy, @@ -451,7 +452,7 @@ vacuum(List *relations, VacuumParams *params, Assert(params->options & VACOPT_ANALYZE); if (IsAutoVacuumWorkerProcess()) use_own_xacts = true; - else if (in_outer_xact) + else if (analyze_in_outer_xact) use_own_xacts = false; else if (list_length(relations) > 1) use_own_xacts = true; @@ -469,7 +470,7 @@ vacuum(List *relations, VacuumParams *params, */ if (use_own_xacts) { - Assert(!in_outer_xact); + Assert(!analyze_in_outer_xact); /* ActiveSnapshot is not set by autovacuum */ if (ActiveSnapshotSet()) @@ -521,7 +522,7 @@ vacuum(List *relations, VacuumParams *params, } analyze_rel(vrel->oid, vrel->relation, params, - vrel->va_cols, in_outer_xact, vac_strategy); + vrel->va_cols, analyze_in_outer_xact, vac_strategy); if (use_own_xacts) { @@ -544,6 +545,7 @@ vacuum(List *relations, VacuumParams *params, { in_vacuum = false; VacuumCostActive = false; + analyze_in_outer_xact = false; } PG_END_TRY(); @@ -2214,10 +2216,28 @@ vacuum_delay_point(void) WAIT_EVENT_VACUUM_DELAY); ResetLatch(MyLatch); + /* + * Reload the configuration file if requested. This allows changes to + * [autovacuum_]vacuum_cost_limit and [autovacuum_]vacuum_cost_delay + * to take effect while a table is being vacuumed or analyzed. + */ + if (ConfigReloadPending && !analyze_in_outer_xact) + { + ConfigReloadPending = false; + ProcessConfigFile(PGC_SIGHUP); + AutoVacuumUpdateDelay(); + } + VacuumCostBalance = 0; - /* update balance values for workers */ - AutoVacuumUpdateDelay(); + /* + * Update balance values for workers. We must always do this in case + * the autovacuum launcher has done a rebalance (as it does when + * launching a new worker). + */ + AutoVacuumUpdateLimit(); + + VacuumCostActive = (VacuumCostDelay > 0); /* Might have gotten an interrupt while sleeping */ CHECK_FOR_INTERRUPTS(); diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index ff6149a179..78b4233241 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -139,6 +139,9 @@ int Log_autovacuum_min_duration = 600000; static bool am_autovacuum_launcher = false; static bool am_autovacuum_worker = false; +static bool av_use_table_option_cost_delay = false; +static double av_table_option_cost_delay = 0; + /* Flags set by signal handlers */ static volatile sig_atomic_t got_SIGUSR2 = false; @@ -189,7 +192,6 @@ typedef struct autovac_table { Oid at_relid; VacuumParams at_params; - double at_vacuum_cost_delay; int at_vacuum_cost_limit; bool at_dobalance; bool at_sharedrel; @@ -225,7 +227,6 @@ typedef struct WorkerInfoData TimestampTz wi_launchtime; bool wi_dobalance; bool wi_sharedrel; - double wi_cost_delay; int wi_cost_limit; int wi_cost_limit_base; } WorkerInfoData; @@ -1756,7 +1757,6 @@ FreeWorkerInfo(int code, Datum arg) MyWorkerInfo->wi_proc = NULL; MyWorkerInfo->wi_launchtime = 0; MyWorkerInfo->wi_dobalance = false; - MyWorkerInfo->wi_cost_delay = 0; MyWorkerInfo->wi_cost_limit = 0; MyWorkerInfo->wi_cost_limit_base = 0; dlist_push_head(&AutoVacuumShmem->av_freeWorkers, @@ -1780,11 +1780,37 @@ FreeWorkerInfo(int code, Datum arg) void AutoVacuumUpdateDelay(void) { - if (MyWorkerInfo) + /* + * We are using autovacuum-related GUCs to update VacuumCostDelay, so we + * only want autovacuum workers and autovacuum launcher to do this. + */ + if (!(am_autovacuum_worker || am_autovacuum_launcher)) + return; + + if (av_use_table_option_cost_delay) { - VacuumCostDelay = MyWorkerInfo->wi_cost_delay; - VacuumCostLimit = MyWorkerInfo->wi_cost_limit; + VacuumCostDelay = av_table_option_cost_delay; } + else + { + VacuumCostDelay = autovacuum_vac_cost_delay >= 0 ? + autovacuum_vac_cost_delay : VacuumCostDelay; + } +} + +/* + * Helper for vacuum_delay_point() to allow workers to read their + * wi_cost_limit. + */ +void +AutoVacuumUpdateLimit(void) +{ + if (!MyWorkerInfo) + return; + + LWLockAcquire(AutovacuumLock, LW_SHARED); + VacuumCostLimit = MyWorkerInfo->wi_cost_limit; + LWLockRelease(AutovacuumLock); } /* @@ -1824,9 +1850,9 @@ autovac_balance_cost(void) if (worker->wi_proc != NULL && worker->wi_dobalance && - worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0) + worker->wi_cost_limit_base > 0 && vac_cost_delay > 0) cost_total += - (double) worker->wi_cost_limit_base / worker->wi_cost_delay; + (double) worker->wi_cost_limit_base / vac_cost_delay; } /* there are no cost limits -- nothing to do */ @@ -1844,7 +1870,7 @@ autovac_balance_cost(void) if (worker->wi_proc != NULL && worker->wi_dobalance && - worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0) + worker->wi_cost_limit_base > 0 && vac_cost_delay > 0) { int limit = (int) (cost_avail * worker->wi_cost_limit_base / cost_total); @@ -1861,11 +1887,10 @@ autovac_balance_cost(void) } if (worker->wi_proc != NULL) - elog(DEBUG2, "autovac_balance_cost(pid=%d db=%u, rel=%u, dobalance=%s cost_limit=%d, cost_limit_base=%d, cost_delay=%g)", + elog(DEBUG2, "autovac_balance_cost(pid=%d db=%u, rel=%u, dobalance=%s cost_limit=%d, cost_limit_base=%d)", worker->wi_proc->pid, worker->wi_dboid, worker->wi_tableoid, worker->wi_dobalance ? "yes" : "no", - worker->wi_cost_limit, worker->wi_cost_limit_base, - worker->wi_cost_delay); + worker->wi_cost_limit, worker->wi_cost_limit_base); } } @@ -2326,6 +2351,15 @@ do_autovacuum(void) ConfigReloadPending = false; ProcessConfigFile(PGC_SIGHUP); + /* + * Autovacuum workers should always update VacuumCostDelay and + * VacuumCostLimit in case they were overridden by the reload. + */ + AutoVacuumUpdateDelay(); + LWLockAcquire(AutovacuumLock, LW_SHARED); + VacuumCostLimit = MyWorkerInfo->wi_cost_limit; + LWLockRelease(AutovacuumLock); + /* * You might be tempted to bail out if we see autovacuum is now * disabled. Must resist that temptation -- this might be a @@ -2424,21 +2458,20 @@ do_autovacuum(void) stdVacuumCostDelay = VacuumCostDelay; stdVacuumCostLimit = VacuumCostLimit; + AutoVacuumUpdateDelay(); + /* Must hold AutovacuumLock while mucking with cost balance info */ LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE); /* advertise my cost delay parameters for the balancing algorithm */ MyWorkerInfo->wi_dobalance = tab->at_dobalance; - MyWorkerInfo->wi_cost_delay = tab->at_vacuum_cost_delay; MyWorkerInfo->wi_cost_limit = tab->at_vacuum_cost_limit; MyWorkerInfo->wi_cost_limit_base = tab->at_vacuum_cost_limit; + VacuumCostLimit = MyWorkerInfo->wi_cost_limit; /* do a balance */ autovac_balance_cost(); - /* set the active cost parameters from the result of that */ - AutoVacuumUpdateDelay(); - /* done */ LWLockRelease(AutovacuumLock); @@ -2569,6 +2602,11 @@ deleted: { ConfigReloadPending = false; ProcessConfigFile(PGC_SIGHUP); + AutoVacuumUpdateDelay(); + + LWLockAcquire(AutovacuumLock, LW_SHARED); + VacuumCostLimit = MyWorkerInfo->wi_cost_limit; + LWLockRelease(AutovacuumLock); } LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE); @@ -2771,7 +2809,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, /* fetch the relation's relcache entry */ classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid)); if (!HeapTupleIsValid(classTup)) + { + av_use_table_option_cost_delay = false; + av_table_option_cost_delay = 0; return NULL; + } classForm = (Form_pg_class) GETSTRUCT(classTup); /* @@ -2802,7 +2844,6 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, int multixact_freeze_min_age; int multixact_freeze_table_age; int vac_cost_limit; - double vac_cost_delay; int log_min_duration; /* @@ -2812,12 +2853,16 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, * defaults, autovacuum's own first and plain vacuum second. */ - /* -1 in autovac setting means use plain vacuum_cost_delay */ - vac_cost_delay = (avopts && avopts->vacuum_cost_delay >= 0) - ? avopts->vacuum_cost_delay - : (autovacuum_vac_cost_delay >= 0) - ? autovacuum_vac_cost_delay - : VacuumCostDelay; + if (avopts && avopts->vacuum_cost_delay >= 0) + { + av_use_table_option_cost_delay = true; + av_table_option_cost_delay = avopts->vacuum_cost_delay; + } + else + { + av_use_table_option_cost_delay = false; + av_table_option_cost_delay = 0; + } /* 0 or -1 in autovac setting means use plain vacuum_cost_limit */ vac_cost_limit = (avopts && avopts->vacuum_cost_limit > 0) @@ -2880,7 +2925,6 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, tab->at_params.is_wraparound = wraparound; tab->at_params.log_min_duration = log_min_duration; tab->at_vacuum_cost_limit = vac_cost_limit; - tab->at_vacuum_cost_delay = vac_cost_delay; tab->at_relname = NULL; tab->at_nspname = NULL; tab->at_datname = NULL; @@ -2893,6 +2937,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, !(avopts && (avopts->vacuum_cost_limit > 0 || avopts->vacuum_cost_delay > 0)); } + else + { + av_use_table_option_cost_delay = false; + av_table_option_cost_delay = 0; + } heap_freetuple(classTup); return tab; diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h index c140371b51..558358911c 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -66,6 +66,8 @@ extern void AutoVacWorkerFailed(void); /* autovacuum cost-delay balancer */ extern void AutoVacuumUpdateDelay(void); +extern void AutoVacuumUpdateLimit(void); + #ifdef EXEC_BACKEND extern void AutoVacLauncherMain(int argc, char *argv[]) pg_attribute_noreturn(); extern void AutoVacWorkerMain(int argc, char *argv[]) pg_attribute_noreturn(); -- 2.37.2