On Thu, Apr 6, 2023 at 5:45 PM Daniel Gustafsson <dan...@yesql.se> wrote: > > > On 6 Apr 2023, at 23:06, Melanie Plageman <melanieplage...@gmail.com> wrote: > > > Autovacuum workers, at the end of VacuumUpdateCosts(), check if cost > > limit or cost delay have been changed. If they have, they assert that > > they don't already hold the AutovacuumLock, take it in shared mode, and > > do the logging. > > Another idea would be to copy the values to local temp variables while holding > the lock, and release the lock before calling elog() to avoid holding the lock > over potential IO.
Good idea. I've done this in attached v19. Also I looked through the docs and everything still looks correct for balancing algo. - Melanie
From 0042067ce72a474fe4087245b978847c0b835b72 Mon Sep 17 00:00:00 2001 From: Melanie Plageman <melanieplage...@gmail.com> Date: Fri, 31 Mar 2023 10:38:39 -0400 Subject: [PATCH v19 1/3] Make vacuum failsafe_active global While vacuuming a table in failsafe mode, VacuumCostActive should not be re-enabled. This currently isn't a problem because vacuum cost parameters are only refreshed in between vacuuming tables and failsafe status is reset for every table. In preparation for allowing vacuum cost parameters to be updated more frequently, elevate LVRelState->failsafe_active to a global, VacuumFailsafeActive, which will be checked when determining whether or not to re-enable vacuum cost-related delays. Reviewed-by: Masahiko Sawada <sawada.m...@gmail.com> Reviewed-by: Daniel Gustafsson <dan...@yesql.se> Reviewed-by: Kyotaro Horiguchi <horikyota....@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CAAKRu_ZngzqnEODc7LmS1NH04Kt6Y9huSjz5pp7%2BDXhrjDA0gw%40mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 16 +++++++--------- src/backend/commands/vacuum.c | 15 +++++++++++++++ src/include/commands/vacuum.h | 1 + 3 files changed, 23 insertions(+), 9 deletions(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 639179aa46..2ba85bd3d6 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -153,8 +153,6 @@ typedef struct LVRelState bool aggressive; /* Use visibility map to skip? (disabled by DISABLE_PAGE_SKIPPING) */ bool skipwithvm; - /* Wraparound failsafe has been triggered? */ - bool failsafe_active; /* Consider index vacuuming bypass optimization? */ bool consider_bypass_optimization; @@ -391,7 +389,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, Assert(params->index_cleanup != VACOPTVALUE_UNSPECIFIED); Assert(params->truncate != VACOPTVALUE_UNSPECIFIED && params->truncate != VACOPTVALUE_AUTO); - vacrel->failsafe_active = false; + VacuumFailsafeActive = false; vacrel->consider_bypass_optimization = true; vacrel->do_index_vacuuming = true; vacrel->do_index_cleanup = true; @@ -709,7 +707,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, } else { - if (!vacrel->failsafe_active) + if (!VacuumFailsafeActive) appendStringInfoString(&buf, _("index scan bypassed: ")); else appendStringInfoString(&buf, _("index scan bypassed by failsafe: ")); @@ -2293,7 +2291,7 @@ lazy_vacuum(LVRelState *vacrel) * vacuuming or heap vacuuming. This VACUUM operation won't end up * back here again. */ - Assert(vacrel->failsafe_active); + Assert(VacuumFailsafeActive); } /* @@ -2374,7 +2372,7 @@ lazy_vacuum_all_indexes(LVRelState *vacrel) */ Assert(vacrel->num_index_scans > 0 || vacrel->dead_items->num_items == vacrel->lpdead_items); - Assert(allindexes || vacrel->failsafe_active); + Assert(allindexes || VacuumFailsafeActive); /* * Increase and report the number of index scans. @@ -2616,12 +2614,12 @@ static bool lazy_check_wraparound_failsafe(LVRelState *vacrel) { /* Don't warn more than once per VACUUM */ - if (vacrel->failsafe_active) + if (VacuumFailsafeActive) return true; if (unlikely(vacuum_xid_failsafe_check(&vacrel->cutoffs))) { - vacrel->failsafe_active = true; + VacuumFailsafeActive = true; /* * Abandon use of a buffer access strategy to allow use of all of @@ -2820,7 +2818,7 @@ should_attempt_truncation(LVRelState *vacrel) { BlockNumber possibly_freeable; - if (!vacrel->do_rel_truncate || vacrel->failsafe_active || + if (!vacrel->do_rel_truncate || VacuumFailsafeActive || old_snapshot_threshold >= 0) return false; diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index ea1d8960f4..7fc5c19e37 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -72,6 +72,21 @@ int vacuum_multixact_freeze_table_age; int vacuum_failsafe_age; int vacuum_multixact_failsafe_age; +/* + * VacuumFailsafeActive is a defined as a global so that we can determine + * whether or not to re-enable cost-based vacuum delay when vacuuming a table. + * If failsafe mode has been engaged, we will not re-enable cost-based delay + * for the table until after vacuuming has completed, regardless of other + * settings. + * + * Only VACUUM code should inspect this variable and only table access methods + * should set it to true. In Table AM-agnostic VACUUM code, this variable is + * inspected to determine whether or not to allow cost-based delays. Table AMs + * are free to set it if they desire this behavior, but it is false by default + * and reset to false in between vacuuming each relation. + */ +bool VacuumFailsafeActive = false; + /* * Variables for cost-based parallel vacuum. See comments atop * compute_parallel_delay to understand how it works. diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 19ca818dc2..1223d15e0d 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -306,6 +306,7 @@ extern PGDLLIMPORT pg_atomic_uint32 *VacuumSharedCostBalance; extern PGDLLIMPORT pg_atomic_uint32 *VacuumActiveNWorkers; extern PGDLLIMPORT int VacuumCostBalanceLocal; +extern PGDLLIMPORT bool VacuumFailsafeActive; /* in commands/vacuum.c */ extern void ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel); -- 2.37.2
From 3db6b900042f193892857ba7b2301a478ad0ef9a Mon Sep 17 00:00:00 2001 From: Melanie Plageman <melanieplage...@gmail.com> Date: Sat, 25 Mar 2023 14:14:55 -0400 Subject: [PATCH v19 3/3] Autovacuum refreshes cost-based delay params more often Allow autovacuum to reload the config file more often so that cost-based delay parameters can take effect while VACUUMing a relation. Previously, autovacuum workers only reloaded the config file once per relation vacuumed, so config changes could not take effect until beginning to vacuum the next table. Now, check if a reload is pending roughly once per block, when checking if we need to delay. In order for autovacuum workers to safely update their own cost delay and cost limit parameters without impacting performance, we had to rethink when and how these values were accessed. Previously, an autovacuum worker's wi_cost_limit was set only at the beginning of vacuuming a table, after reloading the config file. Therefore, at the time that autovac_balance_cost() was called, workers vacuuming tables with no cost-related storage parameters could still have different values for their wi_cost_limit_base and wi_cost_delay. Now that the cost parameters can be updated while vacuuming a table, workers will (within some margin of error) have no reason to have different values for cost limit and cost delay (in the absence of cost-related storage parameters). This removes the rationale for keeping cost limit and cost delay in shared memory. Balancing the cost limit requires only the number of active autovacuum workers vacuuming a table with no cost-based storage parameters. Reviewed-by: Masahiko Sawada <sawada.m...@gmail.com> Reviewed-by: Daniel Gustafsson <dan...@yesql.se> Reviewed-by: Kyotaro Horiguchi <horikyota....@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CAAKRu_ZngzqnEODc7LmS1NH04Kt6Y9huSjz5pp7%2BDXhrjDA0gw%40mail.gmail.com --- src/backend/access/heap/vacuumlazy.c | 2 +- src/backend/commands/vacuum.c | 46 +++- src/backend/commands/vacuumparallel.c | 1 - src/backend/postmaster/autovacuum.c | 293 ++++++++++++++++---------- src/include/commands/vacuum.h | 1 + 5 files changed, 221 insertions(+), 122 deletions(-) diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 2ba85bd3d6..0a9ebd22bd 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -389,7 +389,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, Assert(params->index_cleanup != VACOPTVALUE_UNSPECIFIED); Assert(params->truncate != VACOPTVALUE_UNSPECIFIED && params->truncate != VACOPTVALUE_AUTO); - VacuumFailsafeActive = false; + Assert(!VacuumFailsafeActive); vacrel->consider_bypass_optimization = true; vacrel->do_index_vacuuming = true; vacrel->do_index_cleanup = true; diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index f2be74cdb5..ca347e0a6d 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/pmsignal.h" @@ -523,9 +524,9 @@ vacuum(List *relations, VacuumParams *params, BufferAccessStrategy bstrategy, { ListCell *cur; - VacuumUpdateCosts(); in_vacuum = true; - VacuumCostActive = (vacuum_cost_delay > 0); + VacuumFailsafeActive = false; + VacuumUpdateCosts(); VacuumCostBalance = 0; VacuumPageHit = 0; VacuumPageMiss = 0; @@ -579,12 +580,20 @@ vacuum(List *relations, VacuumParams *params, BufferAccessStrategy bstrategy, CommandCounterIncrement(); } } + + /* + * Ensure VacuumFailsafeActive has been reset before vacuuming the + * next relation. + */ + VacuumFailsafeActive = false; } } PG_FINALLY(); { in_vacuum = false; VacuumCostActive = false; + VacuumFailsafeActive = false; + VacuumCostBalance = 0; } PG_END_TRY(); @@ -2245,7 +2254,28 @@ vacuum_delay_point(void) /* Always check for interrupts */ CHECK_FOR_INTERRUPTS(); - if (!VacuumCostActive || InterruptPending) + if (InterruptPending || + (!VacuumCostActive && !ConfigReloadPending)) + return; + + /* + * Autovacuum workers should 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 && IsAutoVacuumWorkerProcess()) + { + ConfigReloadPending = false; + ProcessConfigFile(PGC_SIGHUP); + VacuumUpdateCosts(); + } + + /* + * If we disabled cost-based delays after reloading the config file, + * return. + */ + if (!VacuumCostActive) return; /* @@ -2278,7 +2308,15 @@ vacuum_delay_point(void) VacuumCostBalance = 0; - VacuumUpdateCosts(); + /* + * Balance and update limit values for autovacuum workers. We must do + * this periodically, as the number of workers across which we are + * balancing the limit may have changed. + * + * XXX: There may be better criteria for determining when to do this + * besides "check after napping". + */ + AutoVacuumUpdateCostLimit(); /* Might have gotten an interrupt while sleeping */ CHECK_FOR_INTERRUPTS(); diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c index cc0aff7904..e200d5caf8 100644 --- a/src/backend/commands/vacuumparallel.c +++ b/src/backend/commands/vacuumparallel.c @@ -995,7 +995,6 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc) false); /* Set cost-based vacuum delay */ - VacuumCostActive = (vacuum_cost_delay > 0); VacuumUpdateCosts(); VacuumCostBalance = 0; VacuumPageHit = 0; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 3644b86443..17177c44c6 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -139,6 +139,18 @@ int Log_autovacuum_min_duration = 600000; static bool am_autovacuum_launcher = false; static bool am_autovacuum_worker = false; +/* + * Variables to save the cost-related storage parameters for the current + * relation being vacuumed by this autovacuum worker. Using these, we can + * ensure we don't overwrite the values of vacuum_cost_delay and + * vacuum_cost_limit after reloading the configuration file. They are + * initialized to "invalid" values to indicate that no cost-related storage + * parameters were specified and will be set in do_autovacuum() after checking + * the storage parameters in table_recheck_autovac(). + */ +static double av_storage_param_cost_delay = -1; +static int av_storage_param_cost_limit = -1; + /* Flags set by signal handlers */ static volatile sig_atomic_t got_SIGUSR2 = false; @@ -189,8 +201,8 @@ typedef struct autovac_table { Oid at_relid; VacuumParams at_params; - double at_vacuum_cost_delay; - int at_vacuum_cost_limit; + double at_storage_param_vac_cost_delay; + int at_storage_param_vac_cost_limit; bool at_dobalance; bool at_sharedrel; char *at_relname; @@ -209,7 +221,7 @@ typedef struct autovac_table * wi_sharedrel flag indicating whether table is marked relisshared * wi_proc pointer to PGPROC of the running worker, NULL if not started * wi_launchtime Time at which this worker was launched - * wi_cost_* Vacuum cost-based delay parameters current in this worker + * wi_dobalance Whether this worker should be included in balance calculations * * All fields are protected by AutovacuumLock, except for wi_tableoid and * wi_sharedrel which are protected by AutovacuumScheduleLock (note these @@ -223,11 +235,8 @@ typedef struct WorkerInfoData Oid wi_tableoid; PGPROC *wi_proc; TimestampTz wi_launchtime; - bool wi_dobalance; + pg_atomic_flag wi_dobalance; bool wi_sharedrel; - double wi_cost_delay; - int wi_cost_limit; - int wi_cost_limit_base; } WorkerInfoData; typedef struct WorkerInfoData *WorkerInfo; @@ -273,6 +282,8 @@ typedef struct AutoVacuumWorkItem * av_startingWorker pointer to WorkerInfo currently being started (cleared by * the worker itself as soon as it's up and running) * av_workItems work item array + * av_nworkersForBalance the number of autovacuum workers to use when + * calculating the per worker cost limit * * This struct is protected by AutovacuumLock, except for av_signal and parts * of the worker list (see above). @@ -286,6 +297,7 @@ typedef struct dlist_head av_runningWorkers; WorkerInfo av_startingWorker; AutoVacuumWorkItem av_workItems[NUM_WORKITEMS]; + pg_atomic_uint32 av_nworkersForBalance; } AutoVacuumShmemStruct; static AutoVacuumShmemStruct *AutoVacuumShmem; @@ -319,7 +331,7 @@ static void launch_worker(TimestampTz now); static List *get_database_list(void); static void rebuild_database_list(Oid newdb); static int db_comparator(const void *a, const void *b); -static void autovac_balance_cost(void); +static void autovac_recalculate_workers_for_balance(void); static void do_autovacuum(void); static void FreeWorkerInfo(int code, Datum arg); @@ -669,7 +681,7 @@ AutoVacLauncherMain(int argc, char *argv[]) { LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE); AutoVacuumShmem->av_signal[AutoVacRebalance] = false; - autovac_balance_cost(); + autovac_recalculate_workers_for_balance(); LWLockRelease(AutovacuumLock); } @@ -818,11 +830,6 @@ HandleAutoVacLauncherInterrupts(void) if (!AutoVacuumingActive()) AutoVacLauncherShutdown(); - /* rebalance in case the default cost parameters changed */ - LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE); - autovac_balance_cost(); - LWLockRelease(AutovacuumLock); - /* rebuild the list in case the naptime changed */ rebuild_database_list(InvalidOid); } @@ -1754,10 +1761,7 @@ FreeWorkerInfo(int code, Datum arg) MyWorkerInfo->wi_sharedrel = false; 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; + pg_atomic_clear_flag(&MyWorkerInfo->wi_dobalance); dlist_push_head(&AutoVacuumShmem->av_freeWorkers, &MyWorkerInfo->wi_links); /* not mine anymore */ @@ -1781,10 +1785,20 @@ FreeWorkerInfo(int code, Datum arg) void VacuumUpdateCosts(void) { + double original_cost_delay = vacuum_cost_delay; + int original_cost_limit = vacuum_cost_limit; + if (MyWorkerInfo) { - vacuum_cost_delay = MyWorkerInfo->wi_cost_delay; - vacuum_cost_limit = MyWorkerInfo->wi_cost_limit; + if (av_storage_param_cost_delay >= 0) + vacuum_cost_delay = av_storage_param_cost_delay; + else if (autovacuum_vac_cost_delay >= 0) + vacuum_cost_delay = autovacuum_vac_cost_delay; + else + /* fall back to VacuumCostDelay */ + vacuum_cost_delay = VacuumCostDelay; + + AutoVacuumUpdateCostLimit(); } else { @@ -1792,88 +1806,128 @@ VacuumUpdateCosts(void) vacuum_cost_delay = VacuumCostDelay; vacuum_cost_limit = VacuumCostLimit; } + + /* + * If configuration changes are allowed to impact VacuumCostActive, make + * sure it is updated. + */ + if (VacuumFailsafeActive) + Assert(!VacuumCostActive); + else if (vacuum_cost_delay > 0) + VacuumCostActive = true; + else + { + VacuumCostActive = false; + VacuumCostBalance = 0; + } + + if (MyWorkerInfo) + { + Oid dboid, + tableoid; + + /* Only log updates to cost-related variables */ + if (vacuum_cost_delay == original_cost_delay && + vacuum_cost_limit == original_cost_limit) + return; + + Assert(!LWLockHeldByMe(AutovacuumLock)); + + LWLockAcquire(AutovacuumLock, LW_SHARED); + dboid = MyWorkerInfo->wi_dboid; + tableoid = MyWorkerInfo->wi_tableoid; + LWLockRelease(AutovacuumLock); + + elog(DEBUG2, + "Autovacuum VacuumUpdateCosts(db=%u, rel=%u, dobalance=%s, cost_limit=%d, cost_delay=%g active=%s failsafe=%s)", + dboid, tableoid, pg_atomic_unlocked_test_flag(&MyWorkerInfo->wi_dobalance) ? "no" : "yes", + vacuum_cost_limit, vacuum_cost_delay, + vacuum_cost_delay > 0 ? "yes" : "no", + VacuumFailsafeActive ? "yes" : "no"); + + } } /* - * autovac_balance_cost - * Recalculate the cost limit setting for each active worker. - * - * Caller must hold the AutovacuumLock in exclusive mode. + * Update vacuum_cost_limit with the correct value for an autovacuum worker, + * given the value of other relevant cost limit parameters and the number of + * workers across which the limit must be balanced. Autovacuum workers must + * call this regularly in case av_nworkersForBalance has been updated by + * another worker or by the autovacuum launcher. They must also call it after a + * config reload. */ -static void -autovac_balance_cost(void) +void +AutoVacuumUpdateCostLimit(void) { + if (!MyWorkerInfo) + return; + /* - * The idea here is that we ration out I/O equally. The amount of I/O - * that a worker can consume is determined by cost_limit/cost_delay, so we - * try to equalize those ratios rather than the raw limit settings. - * * note: in cost_limit, zero also means use value from elsewhere, because * zero is not a valid value. */ - int vac_cost_limit = (autovacuum_vac_cost_limit > 0 ? - autovacuum_vac_cost_limit : VacuumCostLimit); - double vac_cost_delay = (autovacuum_vac_cost_delay >= 0 ? - autovacuum_vac_cost_delay : VacuumCostDelay); - double cost_total; - double cost_avail; - dlist_iter iter; - - /* not set? nothing to do */ - if (vac_cost_limit <= 0 || vac_cost_delay <= 0) - return; - /* calculate the total base cost limit of participating active workers */ - cost_total = 0.0; - dlist_foreach(iter, &AutoVacuumShmem->av_runningWorkers) + if (av_storage_param_cost_limit > 0) + vacuum_cost_limit = av_storage_param_cost_limit; + else { - WorkerInfo worker = dlist_container(WorkerInfoData, wi_links, iter.cur); + int nworkers_for_balance; + + if (autovacuum_vac_cost_limit > 0) + vacuum_cost_limit = autovacuum_vac_cost_limit; + else + vacuum_cost_limit = VacuumCostLimit; + + /* Only balance limit if no cost-related storage parameters specified */ + if (pg_atomic_unlocked_test_flag(&MyWorkerInfo->wi_dobalance)) + return; + + Assert(vacuum_cost_limit > 0); - if (worker->wi_proc != NULL && - worker->wi_dobalance && - worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0) - cost_total += - (double) worker->wi_cost_limit_base / worker->wi_cost_delay; + nworkers_for_balance = pg_atomic_read_u32(&AutoVacuumShmem->av_nworkersForBalance); + + /* There is at least 1 autovac worker (this worker) */ + if (nworkers_for_balance <= 0) + elog(ERROR, "nworkers_for_balance must be > 0"); + + vacuum_cost_limit = Max(vacuum_cost_limit / nworkers_for_balance, 1); } +} - /* there are no cost limits -- nothing to do */ - if (cost_total <= 0) - return; +/* + * autovac_recalculate_workers_for_balance + * Recalculate the number of workers to consider, given cost-related + * storage parameters and the current number of active workers. + * + * Caller must hold the AutovacuumLock in at least shared mode to access + * worker->wi_proc. + */ +static void +autovac_recalculate_workers_for_balance(void) +{ + dlist_iter iter; + int orig_nworkers_for_balance; + int nworkers_for_balance = 0; + + Assert(LWLockHeldByMe(AutovacuumLock)); + + orig_nworkers_for_balance = + pg_atomic_read_u32(&AutoVacuumShmem->av_nworkersForBalance); - /* - * Adjust cost limit of each active worker to balance the total of cost - * limit to autovacuum_vacuum_cost_limit. - */ - cost_avail = (double) vac_cost_limit / vac_cost_delay; dlist_foreach(iter, &AutoVacuumShmem->av_runningWorkers) { WorkerInfo worker = dlist_container(WorkerInfoData, wi_links, iter.cur); - if (worker->wi_proc != NULL && - worker->wi_dobalance && - worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0) - { - int limit = (int) - (cost_avail * worker->wi_cost_limit_base / cost_total); - - /* - * We put a lower bound of 1 on the cost_limit, to avoid division- - * by-zero in the vacuum code. Also, in case of roundoff trouble - * in these calculations, let's be sure we don't ever set - * cost_limit to more than the base value. - */ - worker->wi_cost_limit = Max(Min(limit, - worker->wi_cost_limit_base), - 1); - } + if (worker->wi_proc == NULL || + pg_atomic_unlocked_test_flag(&worker->wi_dobalance)) + continue; - 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)", - 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); + nworkers_for_balance++; } + + if (nworkers_for_balance != orig_nworkers_for_balance) + pg_atomic_write_u32(&AutoVacuumShmem->av_nworkersForBalance, + nworkers_for_balance); } /* @@ -2421,23 +2475,34 @@ do_autovacuum(void) continue; } - /* Must hold AutovacuumLock while mucking with cost balance info */ - LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE); + /* + * Save the cost-related storage parameter values in global variables + * for reference when updating vacuum_cost_delay and vacuum_cost_limit + * during vacuuming this table. + */ + av_storage_param_cost_delay = tab->at_storage_param_vac_cost_delay; + av_storage_param_cost_limit = tab->at_storage_param_vac_cost_limit; - /* 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; + /* + * We only expect this worker to ever set the flag, so don't bother + * checking the return value. We shouldn't have to retry. + */ + if (tab->at_dobalance) + pg_atomic_test_set_flag(&MyWorkerInfo->wi_dobalance); + else + pg_atomic_clear_flag(&MyWorkerInfo->wi_dobalance); - /* do a balance */ - autovac_balance_cost(); + LWLockAcquire(AutovacuumLock, LW_SHARED); + autovac_recalculate_workers_for_balance(); + LWLockRelease(AutovacuumLock); - /* set the active cost parameters from the result of that */ + /* + * We wait until this point to update cost delay and cost limit + * values, even though we reloaded the configuration file above, so + * that we can take into account the cost-related storage parameters. + */ VacuumUpdateCosts(); - /* done */ - LWLockRelease(AutovacuumLock); /* clean up memory before each iteration */ MemoryContextResetAndDeleteChildren(PortalContext); @@ -2521,16 +2586,17 @@ deleted: pfree(tab); /* - * Remove my info from shared memory. We could, but intentionally - * don't, clear wi_cost_limit and friends --- this is on the - * assumption that we probably have more to do with similar cost - * settings, so we don't want to give up our share of I/O for a very - * short interval and thereby thrash the global balance. + * Remove my info from shared memory. We set wi_dobalance on the + * assumption that we are more likely than not to vacuum a table with + * no cost-related storage parameters next, so we want to claim our + * share of I/O as soon as possible to avoid thrashing the global + * balance. */ LWLockAcquire(AutovacuumScheduleLock, LW_EXCLUSIVE); MyWorkerInfo->wi_tableoid = InvalidOid; MyWorkerInfo->wi_sharedrel = false; LWLockRelease(AutovacuumScheduleLock); + pg_atomic_test_set_flag(&MyWorkerInfo->wi_dobalance); } /* @@ -2562,6 +2628,7 @@ deleted: { ConfigReloadPending = false; ProcessConfigFile(PGC_SIGHUP); + VacuumUpdateCosts(); } LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE); @@ -2797,8 +2864,6 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, int freeze_table_age; int multixact_freeze_min_age; int multixact_freeze_table_age; - int vac_cost_limit; - double vac_cost_delay; int log_min_duration; /* @@ -2808,20 +2873,6 @@ 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; - - /* 0 or -1 in autovac setting means use plain vacuum_cost_limit */ - vac_cost_limit = (avopts && avopts->vacuum_cost_limit > 0) - ? avopts->vacuum_cost_limit - : (autovacuum_vac_cost_limit > 0) - ? autovacuum_vac_cost_limit - : VacuumCostLimit; - /* -1 in autovac setting means use log_autovacuum_min_duration */ log_min_duration = (avopts && avopts->log_min_duration >= 0) ? avopts->log_min_duration @@ -2877,8 +2928,10 @@ 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; - tab->at_vacuum_cost_limit = vac_cost_limit; - tab->at_vacuum_cost_delay = vac_cost_delay; + tab->at_storage_param_vac_cost_limit = avopts ? + avopts->vacuum_cost_limit : 0; + tab->at_storage_param_vac_cost_delay = avopts ? + avopts->vacuum_cost_delay : -1; tab->at_relname = NULL; tab->at_nspname = NULL; tab->at_datname = NULL; @@ -3380,10 +3433,18 @@ AutoVacuumShmemInit(void) worker = (WorkerInfo) ((char *) AutoVacuumShmem + MAXALIGN(sizeof(AutoVacuumShmemStruct))); + /* initialize the WorkerInfo free list */ for (i = 0; i < autovacuum_max_workers; i++) + { dlist_push_head(&AutoVacuumShmem->av_freeWorkers, &worker[i].wi_links); + + pg_atomic_init_flag(&worker[i].wi_dobalance); + } + + pg_atomic_init_u32(&AutoVacuumShmem->av_nworkersForBalance, 0); + } else Assert(found); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 50caf1315d..2a856b0e5e 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -350,6 +350,7 @@ extern IndexBulkDeleteResult *vac_cleanup_one_index(IndexVacuumInfo *ivinfo, extern Size vac_max_items_to_alloc_size(int max_items); /* In postmaster/autovacuum.c */ +extern void AutoVacuumUpdateCostLimit(void); extern void VacuumUpdateCosts(void); /* in commands/vacuumparallel.c */ -- 2.37.2
From 92fc87969a496c1a1f5fd612d3c7c32251dec6e1 Mon Sep 17 00:00:00 2001 From: Melanie Plageman <melanieplage...@gmail.com> Date: Thu, 6 Apr 2023 16:02:12 -0400 Subject: [PATCH v19 2/3] Separate vacuum cost variables from GUCs Vacuum code run both by autovacuum workers and a backend doing VACUUM/ANALYZE previously inspected VacuumCostLimit and VacuumCostDelay, which are the global variables backing the GUCs vacuum_cost_limit and vacuum_cost_delay. Autovacuum workers needed to override these variables with their own values, derived from autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay and worker cost limit balancing logic. This led to confusing code which, in some cases, both derived and set a new value of VacuumCostLimit from VacuumCostLimit. In preparation for refreshing these GUC values more often, introduce new, independent global variables and add a function to update them using the GUCs and existing logic. Per suggestion by Kyotaro Horiguchi Reviewed-by: Masahiko Sawada <sawada.m...@gmail.com> Reviewed-by: Daniel Gustafsson <dan...@yesql.se> Reviewed-by: Kyotaro Horiguchi <horikyota....@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CAAKRu_ZngzqnEODc7LmS1NH04Kt6Y9huSjz5pp7%2BDXhrjDA0gw%40mail.gmail.com --- src/backend/commands/vacuum.c | 29 +++++++++++++++-------- src/backend/commands/vacuumparallel.c | 3 ++- src/backend/postmaster/autovacuum.c | 34 +++++++++++---------------- src/include/commands/vacuum.h | 5 ++++ src/include/postmaster/autovacuum.h | 3 --- 5 files changed, 40 insertions(+), 34 deletions(-) diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 7fc5c19e37..f2be74cdb5 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -72,6 +72,15 @@ int vacuum_multixact_freeze_table_age; int vacuum_failsafe_age; int vacuum_multixact_failsafe_age; +/* + * Variables for cost-based vacuum delay. The defaults differ between + * autovacuum and vacuum. They should be set with the appropriate GUC value in + * vacuum code. They are initialized here to the defaults for client backends + * executing VACUUM or ANALYZE. + */ +double vacuum_cost_delay = 0; +int vacuum_cost_limit = 200; + /* * VacuumFailsafeActive is a defined as a global so that we can determine * whether or not to re-enable cost-based vacuum delay when vacuuming a table. @@ -514,8 +523,9 @@ vacuum(List *relations, VacuumParams *params, BufferAccessStrategy bstrategy, { ListCell *cur; + VacuumUpdateCosts(); in_vacuum = true; - VacuumCostActive = (VacuumCostDelay > 0); + VacuumCostActive = (vacuum_cost_delay > 0); VacuumCostBalance = 0; VacuumPageHit = 0; VacuumPageMiss = 0; @@ -2244,14 +2254,14 @@ vacuum_delay_point(void) */ if (VacuumSharedCostBalance != NULL) msec = compute_parallel_delay(); - else if (VacuumCostBalance >= VacuumCostLimit) - msec = VacuumCostDelay * VacuumCostBalance / VacuumCostLimit; + else if (VacuumCostBalance >= vacuum_cost_limit) + msec = vacuum_cost_delay * VacuumCostBalance / vacuum_cost_limit; /* Nap if appropriate */ if (msec > 0) { - if (msec > VacuumCostDelay * 4) - msec = VacuumCostDelay * 4; + if (msec > vacuum_cost_delay * 4) + msec = vacuum_cost_delay * 4; pgstat_report_wait_start(WAIT_EVENT_VACUUM_DELAY); pg_usleep(msec * 1000); @@ -2268,8 +2278,7 @@ vacuum_delay_point(void) VacuumCostBalance = 0; - /* update balance values for workers */ - AutoVacuumUpdateDelay(); + VacuumUpdateCosts(); /* Might have gotten an interrupt while sleeping */ CHECK_FOR_INTERRUPTS(); @@ -2319,11 +2328,11 @@ compute_parallel_delay(void) /* Compute the total local balance for the current worker */ VacuumCostBalanceLocal += VacuumCostBalance; - if ((shared_balance >= VacuumCostLimit) && - (VacuumCostBalanceLocal > 0.5 * ((double) VacuumCostLimit / nworkers))) + if ((shared_balance >= vacuum_cost_limit) && + (VacuumCostBalanceLocal > 0.5 * ((double) vacuum_cost_limit / nworkers))) { /* Compute sleep time based on the local cost balance */ - msec = VacuumCostDelay * VacuumCostBalanceLocal / VacuumCostLimit; + msec = vacuum_cost_delay * VacuumCostBalanceLocal / vacuum_cost_limit; pg_atomic_sub_fetch_u32(VacuumSharedCostBalance, VacuumCostBalanceLocal); VacuumCostBalanceLocal = 0; } diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c index 563117a8f6..cc0aff7904 100644 --- a/src/backend/commands/vacuumparallel.c +++ b/src/backend/commands/vacuumparallel.c @@ -995,7 +995,8 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc) false); /* Set cost-based vacuum delay */ - VacuumCostActive = (VacuumCostDelay > 0); + VacuumCostActive = (vacuum_cost_delay > 0); + VacuumUpdateCosts(); VacuumCostBalance = 0; VacuumPageHit = 0; VacuumPageMiss = 0; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index c1e911b1b3..3644b86443 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -1773,16 +1773,24 @@ FreeWorkerInfo(int code, Datum arg) } /* - * Update the cost-based delay parameters, so that multiple workers consume - * each a fraction of the total available I/O. + * Update vacuum cost-based delay-related parameters for autovacuum workers and + * backends executing VACUUM or ANALYZE using the value of relevant gucs and + * global state. This must be called during setup for vacuum and after every + * config reload to ensure up-to-date values. */ void -AutoVacuumUpdateDelay(void) +VacuumUpdateCosts(void) { if (MyWorkerInfo) { - VacuumCostDelay = MyWorkerInfo->wi_cost_delay; - VacuumCostLimit = MyWorkerInfo->wi_cost_limit; + vacuum_cost_delay = MyWorkerInfo->wi_cost_delay; + vacuum_cost_limit = MyWorkerInfo->wi_cost_limit; + } + else + { + /* Must be explicit VACUUM or ANALYZE */ + vacuum_cost_delay = VacuumCostDelay; + vacuum_cost_limit = VacuumCostLimit; } } @@ -2311,8 +2319,6 @@ do_autovacuum(void) autovac_table *tab; bool isshared; bool skipit; - double stdVacuumCostDelay; - int stdVacuumCostLimit; dlist_iter iter; CHECK_FOR_INTERRUPTS(); @@ -2415,14 +2421,6 @@ do_autovacuum(void) continue; } - /* - * Remember the prevailing values of the vacuum cost GUCs. We have to - * restore these at the bottom of the loop, else we'll compute wrong - * values in the next iteration of autovac_balance_cost(). - */ - stdVacuumCostDelay = VacuumCostDelay; - stdVacuumCostLimit = VacuumCostLimit; - /* Must hold AutovacuumLock while mucking with cost balance info */ LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE); @@ -2436,7 +2434,7 @@ do_autovacuum(void) autovac_balance_cost(); /* set the active cost parameters from the result of that */ - AutoVacuumUpdateDelay(); + VacuumUpdateCosts(); /* done */ LWLockRelease(AutovacuumLock); @@ -2533,10 +2531,6 @@ deleted: MyWorkerInfo->wi_tableoid = InvalidOid; MyWorkerInfo->wi_sharedrel = false; LWLockRelease(AutovacuumScheduleLock); - - /* restore vacuum cost GUCs for the next iteration */ - VacuumCostDelay = stdVacuumCostDelay; - VacuumCostLimit = stdVacuumCostLimit; } /* diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 1223d15e0d..50caf1315d 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -307,6 +307,8 @@ extern PGDLLIMPORT pg_atomic_uint32 *VacuumActiveNWorkers; extern PGDLLIMPORT int VacuumCostBalanceLocal; extern PGDLLIMPORT bool VacuumFailsafeActive; +extern PGDLLIMPORT double vacuum_cost_delay; +extern PGDLLIMPORT int vacuum_cost_limit; /* in commands/vacuum.c */ extern void ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel); @@ -347,6 +349,9 @@ extern IndexBulkDeleteResult *vac_cleanup_one_index(IndexVacuumInfo *ivinfo, IndexBulkDeleteResult *istat); extern Size vac_max_items_to_alloc_size(int max_items); +/* In postmaster/autovacuum.c */ +extern void VacuumUpdateCosts(void); + /* in commands/vacuumparallel.c */ extern ParallelVacuumState *parallel_vacuum_init(Relation rel, Relation *indrels, int nindexes, int nrequested_workers, diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h index c140371b51..65afd1ea1e 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -63,9 +63,6 @@ extern int StartAutoVacWorker(void); /* called from postmaster when a worker could not be forked */ extern void AutoVacWorkerFailed(void); -/* autovacuum cost-delay balancer */ -extern void AutoVacuumUpdateDelay(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
