Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit50a7576

Browse files
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-limitbalancing algorithm produces undesirable results: instead of using theconfigured values, the global values are always used,as illustrated by Mark Kirkwood inhttp://www.postgresql.org/message-id/52FACF15.8020507@catalyst.net.nzSince the mechanism is already complicated, just disable it for thosecases rather than trying to make it cope. There are undesirableside-effects from this too, namely that the total I/O impact on thesystem will be higher whenever such tables are vacuumed. However, thisis seen as less harmful than slowing down vacuum, because that wouldcause bloat to accumulate. Anyway, in the new system it is possible totweak options to get the precise behavior one wants, whereas with theprevious 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 setfor individual tables.
1 parentf04b112 commit50a7576

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
@@ -688,10 +688,13 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
688688
</para>
689689

690690
<para>
691-
When multiple workers are running, the costlimit is
691+
When multiple workers are running, the costdelay parameters are
692692
<quote>balanced</quote> among all the running workers, so that the
693-
total impact on the system is the same, regardless of the number
694-
of workers actually running.
693+
total I/O impact on the system is the same regardless of the number
694+
of workers actually running. However, any workers processing tables whose
695+
<literal>autovacuum_vacuum_cost_delay</> or
696+
<literal>autovacuum_vacuum_cost_limit</> have been set are not considered
697+
in the balancing algorithm.
695698
</para>
696699
</sect2>
697700
</sect1>

‎src/backend/postmaster/autovacuum.c

Lines changed: 20 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -177,6 +177,7 @@ typedef struct autovac_table
177177
intat_freeze_table_age;
178178
intat_vacuum_cost_delay;
179179
intat_vacuum_cost_limit;
180+
boolat_dobalance;
180181
boolat_wraparound;
181182
char*at_relname;
182183
char*at_nspname;
@@ -207,6 +208,7 @@ typedef struct WorkerInfoData
207208
Oidwi_tableoid;
208209
PGPROC*wi_proc;
209210
TimestampTzwi_launchtime;
211+
boolwi_dobalance;
210212
intwi_cost_delay;
211213
intwi_cost_limit;
212214
intwi_cost_limit_base;
@@ -1641,6 +1643,7 @@ FreeWorkerInfo(int code, Datum arg)
16411643
MyWorkerInfo->wi_tableoid=InvalidOid;
16421644
MyWorkerInfo->wi_proc=NULL;
16431645
MyWorkerInfo->wi_launchtime=0;
1646+
MyWorkerInfo->wi_dobalance= false;
16441647
MyWorkerInfo->wi_cost_delay=0;
16451648
MyWorkerInfo->wi_cost_limit=0;
16461649
MyWorkerInfo->wi_cost_limit_base=0;
@@ -1700,14 +1703,15 @@ autovac_balance_cost(void)
17001703
if (vac_cost_limit <=0||vac_cost_delay <=0)
17011704
return;
17021705

1703-
/*caculate the total base cost limit of active workers */
1706+
/*calculate the total base cost limit of participating active workers */
17041707
cost_total=0.0;
17051708
worker= (WorkerInfo)SHMQueueNext(&AutoVacuumShmem->av_runningWorkers,
17061709
&AutoVacuumShmem->av_runningWorkers,
17071710
offsetof(WorkerInfoData,wi_links));
17081711
while (worker)
17091712
{
17101713
if (worker->wi_proc!=NULL&&
1714+
worker->wi_dobalance&&
17111715
worker->wi_cost_limit_base>0&&worker->wi_cost_delay>0)
17121716
cost_total+=
17131717
(double)worker->wi_cost_limit_base /worker->wi_cost_delay;
@@ -1716,6 +1720,7 @@ autovac_balance_cost(void)
17161720
&worker->wi_links,
17171721
offsetof(WorkerInfoData,wi_links));
17181722
}
1723+
17191724
/* there are no cost limits -- nothing to do */
17201725
if (cost_total <=0)
17211726
return;
@@ -1731,6 +1736,7 @@ autovac_balance_cost(void)
17311736
while (worker)
17321737
{
17331738
if (worker->wi_proc!=NULL&&
1739+
worker->wi_dobalance&&
17341740
worker->wi_cost_limit_base>0&&worker->wi_cost_delay>0)
17351741
{
17361742
intlimit= (int)
@@ -1745,12 +1751,14 @@ autovac_balance_cost(void)
17451751
worker->wi_cost_limit=Max(Min(limit,
17461752
worker->wi_cost_limit_base),
17471753
1);
1754+
}
17481755

1749-
elog(DEBUG2,"autovac_balance_cost(pid=%u db=%u, rel=%u, cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",
1756+
if (worker->wi_proc!=NULL)
1757+
elog(DEBUG2,"autovac_balance_cost(pid=%u db=%u, rel=%u, dobalance=%s cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",
17501758
worker->wi_proc->pid,worker->wi_dboid,worker->wi_tableoid,
1759+
worker->wi_dobalance ?"yes" :"no",
17511760
worker->wi_cost_limit,worker->wi_cost_limit_base,
17521761
worker->wi_cost_delay);
1753-
}
17541762

17551763
worker= (WorkerInfo)SHMQueueNext(&AutoVacuumShmem->av_runningWorkers,
17561764
&worker->wi_links,
@@ -2220,6 +2228,7 @@ do_autovacuum(void)
22202228
LWLockAcquire(AutovacuumLock,LW_EXCLUSIVE);
22212229

22222230
/* advertise my cost delay parameters for the balancing algorithm */
2231+
MyWorkerInfo->wi_dobalance=tab->at_dobalance;
22232232
MyWorkerInfo->wi_cost_delay=tab->at_vacuum_cost_delay;
22242233
MyWorkerInfo->wi_cost_limit=tab->at_vacuum_cost_limit;
22252234
MyWorkerInfo->wi_cost_limit_base=tab->at_vacuum_cost_limit;
@@ -2500,6 +2509,14 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
25002509
tab->at_relname=NULL;
25012510
tab->at_nspname=NULL;
25022511
tab->at_datname=NULL;
2512+
2513+
/*
2514+
* If any of the cost delay parameters has been set individually for
2515+
* this table, disable the balancing algorithm.
2516+
*/
2517+
tab->at_dobalance=
2518+
!(avopts&& (avopts->vacuum_cost_limit>0||
2519+
avopts->vacuum_cost_delay>0));
25032520
}
25042521

25052522
heap_freetuple(classTup);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp