Skip to content

Commit 67ed9d5

Browse files
alvherrealvherre
committed
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.
1 parent ef8ac58 commit 67ed9d5

File tree

2 files changed

+26
-6
lines changed

2 files changed

+26
-6
lines changed

‎doc/src/sgml/maintenance.sgml

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -789,10 +789,13 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
789789
</para>
790790

791791
<para>
792-
When multiple workers are running, the cost limit is
792+
When multiple workers are running, the cost delay parameters are
793793
<quote>balanced</quote> among all the running workers, so that the
794-
total impact on the system is the same, regardless of the number
795-
of workers actually running.
794+
total I/O impact on the system is the same regardless of the number
795+
of workers actually running. However, any workers processing tables whose
796+
<literal>autovacuum_vacuum_cost_delay</> or
797+
<literal>autovacuum_vacuum_cost_limit</> have been set are not considered
798+
in the balancing algorithm.
796799
</para>
797800
</sect2>
798801
</sect1>

‎src/backend/postmaster/autovacuum.c

Lines changed: 20 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -192,6 +192,7 @@ typedef struct autovac_table
192192
int at_multixact_freeze_table_age;
193193
int at_vacuum_cost_delay;
194194
int at_vacuum_cost_limit;
195+
bool at_dobalance;
195196
bool at_wraparound;
196197
char *at_relname;
197198
char *at_nspname;
@@ -222,6 +223,7 @@ typedef struct WorkerInfoData
222223
Oid wi_tableoid;
223224
PGPROC *wi_proc;
224225
TimestampTz wi_launchtime;
226+
bool wi_dobalance;
225227
int wi_cost_delay;
226228
int wi_cost_limit;
227229
int wi_cost_limit_base;
@@ -1717,6 +1719,7 @@ FreeWorkerInfo(int code, Datum arg)
17171719
MyWorkerInfo->wi_tableoid = InvalidOid;
17181720
MyWorkerInfo->wi_proc = NULL;
17191721
MyWorkerInfo->wi_launchtime = 0;
1722+
MyWorkerInfo->wi_dobalance = false;
17201723
MyWorkerInfo->wi_cost_delay = 0;
17211724
MyWorkerInfo->wi_cost_limit = 0;
17221725
MyWorkerInfo->wi_cost_limit_base = 0;
@@ -1777,17 +1780,19 @@ autovac_balance_cost(void)
17771780
if (vac_cost_limit <= 0 || vac_cost_delay <= 0)
17781781
return;
17791782

1780-
/* caculate the total base cost limit of active workers */
1783+
/* calculate the total base cost limit of participating active workers */
17811784
cost_total = 0.0;
17821785
dlist_foreach(iter, &AutoVacuumShmem->av_runningWorkers)
17831786
{
17841787
WorkerInfo worker = dlist_container(WorkerInfoData, wi_links, iter.cur);
17851788

17861789
if (worker->wi_proc != NULL &&
1790+
worker->wi_dobalance &&
17871791
worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0)
17881792
cost_total +=
17891793
(double) worker->wi_cost_limit_base / worker->wi_cost_delay;
17901794
}
1795+
17911796
/* there are no cost limits -- nothing to do */
17921797
if (cost_total <= 0)
17931798
return;
@@ -1802,6 +1807,7 @@ autovac_balance_cost(void)
18021807
WorkerInfo worker = dlist_container(WorkerInfoData, wi_links, iter.cur);
18031808

18041809
if (worker->wi_proc != NULL &&
1810+
worker->wi_dobalance &&
18051811
worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0)
18061812
{
18071813
int limit = (int)
@@ -1816,12 +1822,14 @@ autovac_balance_cost(void)
18161822
worker->wi_cost_limit = Max(Min(limit,
18171823
worker->wi_cost_limit_base),
18181824
1);
1825+
}
18191826

1820-
elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",
1827+
if (worker->wi_proc != NULL)
1828+
elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, dobalance=%s cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",
18211829
worker->wi_proc->pid, worker->wi_dboid, worker->wi_tableoid,
1830+
worker->wi_dobalance ? "yes" : "no",
18221831
worker->wi_cost_limit, worker->wi_cost_limit_base,
18231832
worker->wi_cost_delay);
1824-
}
18251833
}
18261834
}
18271835

@@ -2287,6 +2295,7 @@ do_autovacuum(void)
22872295
LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE);
22882296

22892297
/* advertise my cost delay parameters for the balancing algorithm */
2298+
MyWorkerInfo->wi_dobalance = tab->at_dobalance;
22902299
MyWorkerInfo->wi_cost_delay = tab->at_vacuum_cost_delay;
22912300
MyWorkerInfo->wi_cost_limit = tab->at_vacuum_cost_limit;
22922301
MyWorkerInfo->wi_cost_limit_base = tab->at_vacuum_cost_limit;
@@ -2593,6 +2602,14 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
25932602
tab->at_relname = NULL;
25942603
tab->at_nspname = NULL;
25952604
tab->at_datname = NULL;
2605+
2606+
/*
2607+
* If any of the cost delay parameters has been set individually for
2608+
* this table, disable the balancing algorithm.
2609+
*/
2610+
tab->at_dobalance =
2611+
!(avopts && (avopts->vacuum_cost_limit > 0 ||
2612+
avopts->vacuum_cost_delay > 0));
25962613
}
25972614

25982615
heap_freetuple(classTup);

0 commit comments

Comments
 (0)