From 23a8cae6bcdadb1569336cf911cbeb772fda099b Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 3 Oct 2014 13:01:27 -0300 Subject: [PATCH] Don't balance vacuum cost delay when per-table settings are in effect When there are cost-delay-related storage options set for a table, trying to make that table participate in the autovacuum cost-limit balancing algorithm produces undesirable results: instead of using the configured values, the global values are always used, as illustrated by Mark Kirkwood in http://www.postgresql.org/message-id/52FACF15.8020507@catalyst.net.nz Since the mechanism is already complicated, just disable it for those cases rather than trying to make it cope. There are undesirable side-effects from this too, namely that the total I/O impact on the system will be higher whenever such tables are vacuumed. However, this is seen as less harmful than slowing down vacuum, because that would cause bloat to accumulate. Anyway, in the new system it is possible to tweak options to get the precise behavior one wants, whereas with the previous system one was simply hosed. This has been broken forever, so backpatch to all supported branches. This might affect systems where cost_limit and cost_delay have been set for individual tables. --- doc/src/sgml/maintenance.sgml | 9 ++++++--- src/backend/postmaster/autovacuum.c | 23 ++++++++++++++++++++--- 2 files changed, 26 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index cf174f0715..d13a3fbb0e 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -787,10 +787,13 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu - When multiple workers are running, the cost limit is + When multiple workers are running, the cost delay parameters are balanced among all the running workers, so that the - total impact on the system is the same, regardless of the number - of workers actually running. + total I/O impact on the system is the same regardless of the number + of workers actually running. However, any workers processing tables whose + autovacuum_vacuum_cost_delay or + autovacuum_vacuum_cost_limit have been set are not considered + in the balancing algorithm. diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index c240d2444c..1d6e3f35f9 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -193,6 +193,7 @@ typedef struct autovac_table int at_multixact_freeze_table_age; int at_vacuum_cost_delay; int at_vacuum_cost_limit; + bool at_dobalance; bool at_wraparound; char *at_relname; char *at_nspname; @@ -223,6 +224,7 @@ typedef struct WorkerInfoData Oid wi_tableoid; PGPROC *wi_proc; TimestampTz wi_launchtime; + bool wi_dobalance; int wi_cost_delay; int wi_cost_limit; int wi_cost_limit_base; @@ -1716,6 +1718,7 @@ FreeWorkerInfo(int code, Datum arg) MyWorkerInfo->wi_tableoid = InvalidOid; 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; @@ -1776,17 +1779,19 @@ autovac_balance_cost(void) if (vac_cost_limit <= 0 || vac_cost_delay <= 0) return; - /* caculate the total base cost limit of active workers */ + /* calculate the total base cost limit of participating active workers */ cost_total = 0.0; 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) cost_total += (double) worker->wi_cost_limit_base / worker->wi_cost_delay; } + /* there are no cost limits -- nothing to do */ if (cost_total <= 0) return; @@ -1801,6 +1806,7 @@ autovac_balance_cost(void) 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) @@ -1815,12 +1821,14 @@ autovac_balance_cost(void) worker->wi_cost_limit = Max(Min(limit, worker->wi_cost_limit_base), 1); + } - elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, cost_limit=%d, cost_limit_base=%d, cost_delay=%d)", + if (worker->wi_proc != NULL) + elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, dobalance=%s cost_limit=%d, cost_limit_base=%d, cost_delay=%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); - } } } @@ -2284,6 +2292,7 @@ do_autovacuum(void) 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; @@ -2579,6 +2588,14 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, tab->at_relname = NULL; tab->at_nspname = NULL; tab->at_datname = NULL; + + /* + * If any of the cost delay parameters has been set individually for + * this table, disable the balancing algorithm. + */ + tab->at_dobalance = + !(avopts && (avopts->vacuum_cost_limit > 0 || + avopts->vacuum_cost_delay > 0)); } heap_freetuple(classTup); -- 2.40.0