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

Commit5100010

Browse files
Teach VACUUM to bypass unnecessary index vacuuming.
VACUUM has never needed to call ambulkdelete() for each index in caseswhere there are precisely zero TIDs in its dead_tuples array by the endof its first pass over the heap (also its only pass over the heap inthis scenario). Index vacuuming is simply not required when thishappens. Index cleanup will still go ahead, but in practice most callsto amvacuumcleanup() are usually no-ops when there were zero precedingambulkdelete() calls. In short, VACUUM has generally managed to avoidindex scans when there were clearly no index tuples to delete fromindexes. But cases with _close to_ no index tuples to delete wereanother matter -- a round of ambulkdelete() calls took place (one perindex), each of which performed a full index scan.VACUUM now behaves just as if there were zero index tuples to delete incases where there are in fact "virtually zero" such tuples. That is, itcan now bypass index vacuuming and heap vacuuming as an optimization(though not index cleanup). Whether or not VACUUM bypasses indexes isdetermined dynamically, based on the just-observed number of heap pagesin the table that have one or more LP_DEAD items (LP_DEAD items in heappages have a 1:1 correspondence with index tuples that still need to bedeleted from each index in the worst case).We only skip index vacuuming when 2% or less of the table's pages haveone or more LP_DEAD items -- bypassing index vacuuming as anoptimization must not noticeably impede setting bits in the visibilitymap. As a further condition, the dead_tuples array (i.e. VACUUM's arrayof LP_DEAD item TIDs) must not exceed 32MB at the point that the firstpass over the heap finishes, which is also when the decision to bypassis made. (The VACUUM must also have been able to fit all TIDs in itsmaintenance_work_mem-bound dead_tuples space, though with a defaultmaintenance_work_mem setting it can't matter.)This avoids surprising jumps in the duration and overhead of routinevacuuming with workloads where successive VACUUM operations consistentlyhave almost zero dead index tuples. The number of LP_DEAD items maywell accumulate over multiple VACUUM operations, before finally thethreshold is crossed and VACUUM performs conventional index vacuuming.Even then, the optimization will have avoided a great deal of largelyunnecessary index vacuuming.In the future we may teach VACUUM to skip index vacuuming on a per-indexbasis, using a much more sophisticated approach. For now we onlyconsider the extreme cases, where we can be quite confident that indexvacuuming just isn't worth it using simple heuristics.Also log information about how many heap pages have one or more LP_DEADitems when autovacuum logging is enabled.Author: Masahiko Sawada <sawada.mshk@gmail.com>Author: Peter Geoghegan <pg@bowt.ie>Discussion:https://postgr.es/m/CAD21AoD0SkE11fMw4jD4RENAwBMcw1wasVnwpJVw3tVqPOQgAw@mail.gmail.comDiscussion:https://postgr.es/m/CAH2-WzmkebqPd4MVGuPTOS9bMFvp9MDs5cRTCOsv1rQJ3jCbXw@mail.gmail.com
1 parentbc70728 commit5100010

File tree

1 file changed

+128
-10
lines changed

1 file changed

+128
-10
lines changed

‎src/backend/access/heap/vacuumlazy.c

Lines changed: 128 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -103,6 +103,12 @@
103103
#defineVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL50/* ms */
104104
#defineVACUUM_TRUNCATE_LOCK_TIMEOUT5000/* ms */
105105

106+
/*
107+
* Threshold that controls whether we bypass index vacuuming and heap
108+
* vacuuming as an optimization
109+
*/
110+
#defineBYPASS_THRESHOLD_PAGES0.02/* i.e. 2% of rel_pages */
111+
106112
/*
107113
* When a table is small (i.e. smaller than this), save cycles by avoiding
108114
* repeated failsafe checks
@@ -401,7 +407,7 @@ static void lazy_scan_prune(LVRelState *vacrel, Buffer buf,
401407
BlockNumberblkno,Pagepage,
402408
GlobalVisState*vistest,
403409
LVPagePruneState*prunestate);
404-
staticvoidlazy_vacuum(LVRelState*vacrel);
410+
staticvoidlazy_vacuum(LVRelState*vacrel,boolonecall);
405411
staticboollazy_vacuum_all_indexes(LVRelState*vacrel);
406412
staticvoidlazy_vacuum_heap_rel(LVRelState*vacrel);
407413
staticintlazy_vacuum_heap_page(LVRelState*vacrel,BlockNumberblkno,
@@ -760,6 +766,31 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
760766
(long long)VacuumPageHit,
761767
(long long)VacuumPageMiss,
762768
(long long)VacuumPageDirty);
769+
if (vacrel->rel_pages>0)
770+
{
771+
if (vacrel->do_index_vacuuming)
772+
{
773+
msgfmt=_(" %u pages from table (%.2f%% of total) had %lld dead item identifiers removed\n");
774+
775+
if (vacrel->nindexes==0||vacrel->num_index_scans==0)
776+
appendStringInfo(&buf,_("index scan not needed:"));
777+
else
778+
appendStringInfo(&buf,_("index scan needed:"));
779+
}
780+
else
781+
{
782+
msgfmt=_(" %u pages from table (%.2f%% of total) have %lld dead item identifiers\n");
783+
784+
if (!vacrel->do_failsafe)
785+
appendStringInfo(&buf,_("index scan bypassed:"));
786+
else
787+
appendStringInfo(&buf,_("index scan bypassed by failsafe:"));
788+
}
789+
appendStringInfo(&buf,msgfmt,
790+
vacrel->lpdead_item_pages,
791+
100.0*vacrel->lpdead_item_pages /vacrel->rel_pages,
792+
(long long)vacrel->lpdead_items);
793+
}
763794
for (inti=0;i<vacrel->nindexes;i++)
764795
{
765796
IndexBulkDeleteResult*istat=vacrel->indstats[i];
@@ -850,7 +881,8 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
850881
next_fsm_block_to_vacuum;
851882
PGRUsageru0;
852883
Buffervmbuffer=InvalidBuffer;
853-
boolskipping_blocks;
884+
boolskipping_blocks,
885+
have_vacuumed_indexes= false;
854886
StringInfoDatabuf;
855887
constintinitprog_index[]= {
856888
PROGRESS_VACUUM_PHASE,
@@ -1108,7 +1140,8 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
11081140
}
11091141

11101142
/* Remove the collected garbage tuples from table and indexes */
1111-
lazy_vacuum(vacrel);
1143+
lazy_vacuum(vacrel, false);
1144+
have_vacuumed_indexes= true;
11121145

11131146
/*
11141147
* Vacuum the Free Space Map to make newly-freed space visible on
@@ -1475,9 +1508,10 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
14751508
* Note: It's not in fact 100% certain that we really will call
14761509
* lazy_vacuum_heap_rel() -- lazy_vacuum() might yet opt to skip
14771510
* index vacuuming (and so must skip heap vacuuming). This is
1478-
* deemed okay because it only happens in emergencies. (Besides,
1479-
* we start recording free space in the FSM once index vacuuming
1480-
* has been abandoned.)
1511+
* deemed okay because it only happens in emergencies, or when
1512+
* there is very little free space anyway. (Besides, we start
1513+
* recording free space in the FSM once index vacuuming has been
1514+
* abandoned.)
14811515
*
14821516
* Note: The one-pass (no indexes) case is only supposed to make
14831517
* it this far when there were no LP_DEAD items during pruning.
@@ -1522,9 +1556,8 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
15221556
}
15231557

15241558
/* If any tuples need to be deleted, perform final vacuum cycle */
1525-
/* XXX put a threshold on min number of tuples here? */
15261559
if (dead_tuples->num_tuples>0)
1527-
lazy_vacuum(vacrel);
1560+
lazy_vacuum(vacrel, !have_vacuumed_indexes);
15281561

15291562
/*
15301563
* Vacuum the remainder of the Free Space Map. We must do this whether or
@@ -1555,6 +1588,16 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
15551588
* If table has no indexes and at least one heap pages was vacuumed, make
15561589
* log report that lazy_vacuum_heap_rel would've made had there been
15571590
* indexes (having indexes implies using the two pass strategy).
1591+
*
1592+
* We deliberately don't do this in the case where there are indexes but
1593+
* index vacuuming was bypassed. We make a similar report at the point
1594+
* that index vacuuming is bypassed, but that's actually quite different
1595+
* in one important sense: it shows information about work we _haven't_
1596+
* done.
1597+
*
1598+
* log_autovacuum output does things differently; it consistently presents
1599+
* information about LP_DEAD items for the VACUUM as a whole. We always
1600+
* report on each round of index and heap vacuuming separately, though.
15581601
*/
15591602
if (vacrel->nindexes==0&&vacrel->lpdead_item_pages>0)
15601603
ereport(elevel,
@@ -1983,14 +2026,21 @@ lazy_scan_prune(LVRelState *vacrel,
19832026
/*
19842027
* Remove the collected garbage tuples from the table and its indexes.
19852028
*
2029+
* We may choose to bypass index vacuuming at this point, though only when the
2030+
* ongoing VACUUM operation will definitely only have one index scan/round of
2031+
* index vacuuming. Caller indicates whether or not this is such a VACUUM
2032+
* operation using 'onecall' argument.
2033+
*
19862034
* In rare emergencies, the ongoing VACUUM operation can be made to skip both
19872035
* index vacuuming and index cleanup at the point we're called. This avoids
19882036
* having the whole system refuse to allocate further XIDs/MultiXactIds due to
19892037
* wraparound.
19902038
*/
19912039
staticvoid
1992-
lazy_vacuum(LVRelState*vacrel)
2040+
lazy_vacuum(LVRelState*vacrel,boolonecall)
19932041
{
2042+
booldo_bypass_optimization;
2043+
19942044
/* Should not end up here with no indexes */
19952045
Assert(vacrel->nindexes>0);
19962046
Assert(!IsParallelWorker());
@@ -2003,7 +2053,75 @@ lazy_vacuum(LVRelState *vacrel)
20032053
return;
20042054
}
20052055

2006-
if (lazy_vacuum_all_indexes(vacrel))
2056+
/*
2057+
* Consider bypassing index vacuuming (and heap vacuuming) entirely.
2058+
*
2059+
* We currently only do this in cases where the number of LP_DEAD items
2060+
* for the entire VACUUM operation is close to zero. This avoids sharp
2061+
* discontinuities in the duration and overhead of successive VACUUM
2062+
* operations that run against the same table with a fixed workload.
2063+
* Ideally, successive VACUUM operations will behave as if there are
2064+
* exactly zero LP_DEAD items in cases where there are close to zero.
2065+
*
2066+
* This is likely to be helpful with a table that is continually affected
2067+
* by UPDATEs that can mostly apply the HOT optimization, but occasionally
2068+
* have small aberrations that lead to just a few heap pages retaining
2069+
* only one or two LP_DEAD items. This is pretty common; even when the
2070+
* DBA goes out of their way to make UPDATEs use HOT, it is practically
2071+
* impossible to predict whether HOT will be applied in 100% of cases.
2072+
* It's far easier to ensure that 99%+ of all UPDATEs against a table use
2073+
* HOT through careful tuning.
2074+
*/
2075+
do_bypass_optimization= false;
2076+
if (onecall&&vacrel->rel_pages>0)
2077+
{
2078+
BlockNumberthreshold;
2079+
2080+
Assert(vacrel->num_index_scans==0);
2081+
Assert(vacrel->lpdead_items==vacrel->dead_tuples->num_tuples);
2082+
Assert(vacrel->do_index_vacuuming);
2083+
Assert(vacrel->do_index_cleanup);
2084+
2085+
/*
2086+
* This crossover point at which we'll start to do index vacuuming is
2087+
* expressed as a percentage of the total number of heap pages in the
2088+
* table that are known to have at least one LP_DEAD item. This is
2089+
* much more important than the total number of LP_DEAD items, since
2090+
* it's a proxy for the number of heap pages whose visibility map bits
2091+
* cannot be set on account of bypassing index and heap vacuuming.
2092+
*
2093+
* We apply one further precautionary test: the space currently used
2094+
* to store the TIDs (TIDs that now all point to LP_DEAD items) must
2095+
* not exceed 32MB. This limits the risk that we will bypass index
2096+
* vacuuming again and again until eventually there is a VACUUM whose
2097+
* dead_tuples space is not CPU cache resident.
2098+
*/
2099+
threshold= (double)vacrel->rel_pages*BYPASS_THRESHOLD_PAGES;
2100+
do_bypass_optimization=
2101+
(vacrel->lpdead_item_pages<threshold&&
2102+
vacrel->lpdead_items<MAXDEADTUPLES(32L*1024L*1024L));
2103+
}
2104+
2105+
if (do_bypass_optimization)
2106+
{
2107+
/*
2108+
* There are almost zero TIDs. Behave as if there were precisely
2109+
* zero: bypass index vacuuming, but do index cleanup.
2110+
*
2111+
* We expect that the ongoing VACUUM operation will finish very
2112+
* quickly, so there is no point in considering speeding up as a
2113+
* failsafe against wraparound failure. (Index cleanup is expected to
2114+
* finish very quickly in cases where there were no ambulkdelete()
2115+
* calls.)
2116+
*/
2117+
vacrel->do_index_vacuuming= false;
2118+
ereport(elevel,
2119+
(errmsg("\"%s\": index scan bypassed: %u pages from table (%.2f%% of total) have %lld dead item identifiers",
2120+
vacrel->relname,vacrel->rel_pages,
2121+
100.0*vacrel->lpdead_item_pages /vacrel->rel_pages,
2122+
(long long)vacrel->lpdead_items)));
2123+
}
2124+
elseif (lazy_vacuum_all_indexes(vacrel))
20072125
{
20082126
/*
20092127
* We successfully completed a round of index vacuuming. Do related

0 commit comments

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