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

Commit872770f

Browse files
Add VACUUM instrumentation for scanned pages, relfrozenxid.
Report on scanned pages within VACUUM VERBOSE and autovacuum logging.These are pages that were physically examined during the VACUUMoperation. Note that this can include a small number of pages that weremarked all-visible in the visibility map by some earlier VACUUMoperation. VACUUM won't skip all-visible pages that aren't part of arange of all-visible pages that's at least 32 blocks in length (partlyto avoid missing out on opportunities to advance relfrozenxid duringnon-aggressive VACUUMs).Commit44fa848 simplified the definition of scanned pages. It becamethe complement of the pages (of those pages from rel_pages) that wereskipped using the visibility map. And so scanned pages preciselyindicates how effective the visibility map was at saving work. (Beforenow we displayed the number of pages skipped via the visibility map whenhappened to be frozen pages, but not when they were merely all-visible,which was less useful to users.)Rename the user-visible OldestXmin output field to "removal cutoff", andshow some supplementary information: how far behind the cutoff is(number of XIDs behind) by the time the VACUUM operation finished. Thiswill help users to figure out what's _not_ working in extreme caseswhere VACUUM is fundamentally unable to remove dead tuples or freezeolder tuples (e.g., due to a leaked replication slot). Also report whenrelfrozenxid is advanced by VACUUM in output that immediately follows"removal cutoff". This structure is intended to highlight therelationship between the new relfrozenxid value for the table, and theVACUUM operation's removal cutoff.Finally, add instrumentation of "missed dead tuples", and the number ofpages that had at least one such tuple. These are fully DEAD (not justRECENTLY_DEAD) tuples with storage that could not be pruned due tofailure to acquire a cleanup lock on a heap page. This is a replacementfor the "skipped due to pin" instrumentation removed by commit44fa848.It shows more details than before for pages where failing to get acleanup lock actually resulted in VACUUM missing out on useful work, butusually shows nothing at all instead (the mere fact that we couldn't geta cleanup lock is usually of no consequence whatsoever now).Author: Peter Geoghegan <pg@bowt.ie>Reviewed-By: Andres Freund <andres@anarazel.de>Discussion:https://postgr.es/m/CAH2-Wznp=c=Opj8Z7RMR3G=ec3_JfGYMN_YvmCEjoPCHzWbx0g@mail.gmail.com
1 parent80901b3 commit872770f

File tree

4 files changed

+82
-29
lines changed

4 files changed

+82
-29
lines changed

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

Lines changed: 68 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -199,6 +199,7 @@ typedef struct LVRelState
199199
BlockNumberfrozenskipped_pages;/* # frozen pages skipped via VM */
200200
BlockNumberremoved_pages;/* # pages removed by relation truncation */
201201
BlockNumberlpdead_item_pages;/* # pages with LP_DEAD items */
202+
BlockNumbermissed_dead_pages;/* # pages with missed dead tuples */
202203
BlockNumbernonempty_pages;/* actually, last nonempty page + 1 */
203204

204205
/* Statistics output by us, for table */
@@ -212,8 +213,8 @@ typedef struct LVRelState
212213
/* Counters that follow are only for scanned_pages */
213214
int64tuples_deleted;/* # deleted from table */
214215
int64lpdead_items;/* # deleted from indexes */
215-
int64new_dead_tuples;/*new estimated total # of dead items in
216-
* table */
216+
int64recently_dead_tuples;/*# dead, but not yet removable */
217+
int64missed_dead_tuples;/* # removable, but not removed */
217218
int64num_tuples;/* total number of nonremovable tuples */
218219
int64live_tuples;/* live tuples (reltuples estimate) */
219220
}LVRelState;
@@ -318,6 +319,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
318319
write_rate;
319320
boolaggressive,
320321
skipwithvm;
322+
boolfrozenxid_updated,
323+
minmulti_updated;
321324
BlockNumberorig_rel_pages;
322325
char**indnames=NULL;
323326
TransactionIdxidFullScanLimit;
@@ -539,17 +542,20 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
539542
{
540543
/* Cannot advance relfrozenxid/relminmxid */
541544
Assert(!aggressive);
545+
frozenxid_updated=minmulti_updated= false;
542546
vac_update_relstats(rel,new_rel_pages,new_live_tuples,
543547
new_rel_allvisible,vacrel->nindexes>0,
544-
InvalidTransactionId,InvalidMultiXactId, false);
548+
InvalidTransactionId,InvalidMultiXactId,
549+
NULL,NULL, false);
545550
}
546551
else
547552
{
548553
Assert(vacrel->scanned_pages+vacrel->frozenskipped_pages==
549554
orig_rel_pages);
550555
vac_update_relstats(rel,new_rel_pages,new_live_tuples,
551556
new_rel_allvisible,vacrel->nindexes>0,
552-
FreezeLimit,MultiXactCutoff, false);
557+
FreezeLimit,MultiXactCutoff,
558+
&frozenxid_updated,&minmulti_updated, false);
553559
}
554560

555561
/*
@@ -565,7 +571,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
565571
pgstat_report_vacuum(RelationGetRelid(rel),
566572
rel->rd_rel->relisshared,
567573
Max(new_live_tuples,0),
568-
vacrel->new_dead_tuples);
574+
vacrel->recently_dead_tuples+
575+
vacrel->missed_dead_tuples);
569576
pgstat_progress_end_command();
570577

571578
if (instrument)
@@ -578,6 +585,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
578585
{
579586
StringInfoDatabuf;
580587
char*msgfmt;
588+
int32diff;
581589

582590
TimestampDifference(starttime,endtime,&secs,&usecs);
583591

@@ -629,16 +637,40 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
629637
vacrel->relnamespace,
630638
vacrel->relname,
631639
vacrel->num_index_scans);
632-
appendStringInfo(&buf,_("pages: %u removed, %u remain, %uskipped frozen\n"),
640+
appendStringInfo(&buf,_("pages: %u removed, %u remain, %uscanned (%.2f%% of total)\n"),
633641
vacrel->removed_pages,
634642
vacrel->rel_pages,
635-
vacrel->frozenskipped_pages);
643+
vacrel->scanned_pages,
644+
orig_rel_pages==0 ?0 :
645+
100.0*vacrel->scanned_pages /orig_rel_pages);
636646
appendStringInfo(&buf,
637-
_("tuples: %lld removed, %lld remain, %lld are dead but not yet removable, oldest xmin: %u\n"),
647+
_("tuples: %lld removed, %lld remain, %lld are dead but not yet removable\n"),
638648
(long long)vacrel->tuples_deleted,
639649
(long long)vacrel->new_rel_tuples,
640-
(long long)vacrel->new_dead_tuples,
641-
OldestXmin);
650+
(long long)vacrel->recently_dead_tuples);
651+
if (vacrel->missed_dead_tuples>0)
652+
appendStringInfo(&buf,
653+
_("tuples missed: %lld dead from %u pages not removed due to cleanup lock contention\n"),
654+
(long long)vacrel->missed_dead_tuples,
655+
vacrel->missed_dead_pages);
656+
diff= (int32) (ReadNextTransactionId()-OldestXmin);
657+
appendStringInfo(&buf,
658+
_("removable cutoff: %u, older by %d xids when operation ended\n"),
659+
OldestXmin,diff);
660+
if (frozenxid_updated)
661+
{
662+
diff= (int32) (FreezeLimit-vacrel->relfrozenxid);
663+
appendStringInfo(&buf,
664+
_("new relfrozenxid: %u, which is %d xids ahead of previous value\n"),
665+
FreezeLimit,diff);
666+
}
667+
if (minmulti_updated)
668+
{
669+
diff= (int32) (MultiXactCutoff-vacrel->relminmxid);
670+
appendStringInfo(&buf,
671+
_("new relminmxid: %u, which is %d mxids ahead of previous value\n"),
672+
MultiXactCutoff,diff);
673+
}
642674
if (orig_rel_pages>0)
643675
{
644676
if (vacrel->do_index_vacuuming)
@@ -779,13 +811,15 @@ lazy_scan_heap(LVRelState *vacrel, int nworkers)
779811
vacrel->frozenskipped_pages=0;
780812
vacrel->removed_pages=0;
781813
vacrel->lpdead_item_pages=0;
814+
vacrel->missed_dead_pages=0;
782815
vacrel->nonempty_pages=0;
783816

784817
/* Initialize instrumentation counters */
785818
vacrel->num_index_scans=0;
786819
vacrel->tuples_deleted=0;
787820
vacrel->lpdead_items=0;
788-
vacrel->new_dead_tuples=0;
821+
vacrel->recently_dead_tuples=0;
822+
vacrel->missed_dead_tuples=0;
789823
vacrel->num_tuples=0;
790824
vacrel->live_tuples=0;
791825

@@ -1331,7 +1365,8 @@ lazy_scan_heap(LVRelState *vacrel, int nworkers)
13311365
* (unlikely) scenario that new_live_tuples is -1, take it as zero.
13321366
*/
13331367
vacrel->new_rel_tuples=
1334-
Max(vacrel->new_live_tuples,0)+vacrel->new_dead_tuples;
1368+
Max(vacrel->new_live_tuples,0)+vacrel->recently_dead_tuples+
1369+
vacrel->missed_dead_tuples;
13351370

13361371
/*
13371372
* Release any remaining pin on visibility map page.
@@ -1539,7 +1574,7 @@ lazy_scan_prune(LVRelState *vacrel,
15391574
HTSV_Resultres;
15401575
inttuples_deleted,
15411576
lpdead_items,
1542-
new_dead_tuples,
1577+
recently_dead_tuples,
15431578
num_tuples,
15441579
live_tuples;
15451580
intnnewlpdead;
@@ -1556,7 +1591,7 @@ lazy_scan_prune(LVRelState *vacrel,
15561591
/* Initialize (or reset) page-level counters */
15571592
tuples_deleted=0;
15581593
lpdead_items=0;
1559-
new_dead_tuples=0;
1594+
recently_dead_tuples=0;
15601595
num_tuples=0;
15611596
live_tuples=0;
15621597

@@ -1715,11 +1750,11 @@ lazy_scan_prune(LVRelState *vacrel,
17151750
caseHEAPTUPLE_RECENTLY_DEAD:
17161751

17171752
/*
1718-
* If tuple is recentlydeleted then we must not remove it
1719-
*from relation. (We only remove items that are LP_DEAD from
1753+
* If tuple is recentlydead then we must not remove it from
1754+
*the relation. (We only remove items that are LP_DEAD from
17201755
* pruning.)
17211756
*/
1722-
new_dead_tuples++;
1757+
recently_dead_tuples++;
17231758
prunestate->all_visible= false;
17241759
break;
17251760
caseHEAPTUPLE_INSERT_IN_PROGRESS:
@@ -1895,7 +1930,7 @@ lazy_scan_prune(LVRelState *vacrel,
18951930
/* Finally, add page-local counts to whole-VACUUM counts */
18961931
vacrel->tuples_deleted+=tuples_deleted;
18971932
vacrel->lpdead_items+=lpdead_items;
1898-
vacrel->new_dead_tuples+=new_dead_tuples;
1933+
vacrel->recently_dead_tuples+=recently_dead_tuples;
18991934
vacrel->num_tuples+=num_tuples;
19001935
vacrel->live_tuples+=live_tuples;
19011936
}
@@ -1932,7 +1967,8 @@ lazy_scan_noprune(LVRelState *vacrel,
19321967
intlpdead_items,
19331968
num_tuples,
19341969
live_tuples,
1935-
new_dead_tuples;
1970+
recently_dead_tuples,
1971+
missed_dead_tuples;
19361972
HeapTupleHeadertupleheader;
19371973
OffsetNumberdeadoffsets[MaxHeapTuplesPerPage];
19381974

@@ -1944,7 +1980,8 @@ lazy_scan_noprune(LVRelState *vacrel,
19441980
lpdead_items=0;
19451981
num_tuples=0;
19461982
live_tuples=0;
1947-
new_dead_tuples=0;
1983+
recently_dead_tuples=0;
1984+
missed_dead_tuples=0;
19481985

19491986
maxoff=PageGetMaxOffsetNumber(page);
19501987
for (offnum=FirstOffsetNumber;
@@ -2018,16 +2055,15 @@ lazy_scan_noprune(LVRelState *vacrel,
20182055
/*
20192056
* There is some useful work for pruning to do, that won't be
20202057
* done due to failure to get a cleanup lock.
2021-
*
2022-
* TODO Add dedicated instrumentation for this case
20232058
*/
2059+
missed_dead_tuples++;
20242060
break;
20252061
caseHEAPTUPLE_RECENTLY_DEAD:
20262062

20272063
/*
2028-
* Count innew_dead_tuples, just like lazy_scan_prune
2064+
* Count inrecently_dead_tuples, just like lazy_scan_prune
20292065
*/
2030-
new_dead_tuples++;
2066+
recently_dead_tuples++;
20312067
break;
20322068
caseHEAPTUPLE_INSERT_IN_PROGRESS:
20332069

@@ -2063,7 +2099,7 @@ lazy_scan_noprune(LVRelState *vacrel,
20632099
*/
20642100
*hastup= true;
20652101
num_tuples+=lpdead_items;
2066-
/* TODO HEAPTUPLE_DEAD style instrumentation needed here, too */
2102+
missed_dead_tuples+=lpdead_items;
20672103
}
20682104

20692105
*recordfreespace= true;
@@ -2112,9 +2148,12 @@ lazy_scan_noprune(LVRelState *vacrel,
21122148
/*
21132149
* Finally, add relevant page-local counts to whole-VACUUM counts
21142150
*/
2115-
vacrel->new_dead_tuples+=new_dead_tuples;
2151+
vacrel->recently_dead_tuples+=recently_dead_tuples;
2152+
vacrel->missed_dead_tuples+=missed_dead_tuples;
21162153
vacrel->num_tuples+=num_tuples;
21172154
vacrel->live_tuples+=live_tuples;
2155+
if (missed_dead_tuples>0)
2156+
vacrel->missed_dead_pages++;
21182157

21192158
/* Caller won't need to call lazy_scan_prune with same page */
21202159
return true;
@@ -2193,8 +2232,8 @@ lazy_vacuum(LVRelState *vacrel)
21932232
* dead_items space is not CPU cache resident.
21942233
*
21952234
* We don't take any special steps to remember the LP_DEAD items (such
2196-
* as counting them innew_dead_tuplesreport to the stats collector)
2197-
*whenthe optimization is applied. Though the accounting used in
2235+
* as counting them inour finalreport to the stats collector) when
2236+
* the optimization is applied. Though the accounting used in
21982237
* analyze.c's acquire_sample_rows() will recognize the same LP_DEAD
21992238
* items as dead rows in its own stats collector report, that's okay.
22002239
* The discrepancy should be negligible. If this optimization is ever
@@ -3321,7 +3360,7 @@ update_index_statistics(LVRelState *vacrel)
33213360
false,
33223361
InvalidTransactionId,
33233362
InvalidMultiXactId,
3324-
false);
3363+
NULL,NULL,false);
33253364
}
33263365
}
33273366

‎src/backend/commands/analyze.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -645,6 +645,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
645645
hasindex,
646646
InvalidTransactionId,
647647
InvalidMultiXactId,
648+
NULL,NULL,
648649
in_outer_xact);
649650

650651
/* Same for indexes */
@@ -661,6 +662,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
661662
false,
662663
InvalidTransactionId,
663664
InvalidMultiXactId,
665+
NULL,NULL,
664666
in_outer_xact);
665667
}
666668
}
@@ -673,6 +675,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
673675
vac_update_relstats(onerel,-1,totalrows,
674676
0,hasindex,InvalidTransactionId,
675677
InvalidMultiXactId,
678+
NULL,NULL,
676679
in_outer_xact);
677680
}
678681

‎src/backend/commands/vacuum.c

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1315,6 +1315,7 @@ vac_update_relstats(Relation relation,
13151315
BlockNumbernum_all_visible_pages,
13161316
boolhasindex,TransactionIdfrozenxid,
13171317
MultiXactIdminmulti,
1318+
bool*frozenxid_updated,bool*minmulti_updated,
13181319
boolin_outer_xact)
13191320
{
13201321
Oidrelid=RelationGetRelid(relation);
@@ -1390,22 +1391,30 @@ vac_update_relstats(Relation relation,
13901391
* This should match vac_update_datfrozenxid() concerning what we consider
13911392
* to be "in the future".
13921393
*/
1394+
if (frozenxid_updated)
1395+
*frozenxid_updated= false;
13931396
if (TransactionIdIsNormal(frozenxid)&&
13941397
pgcform->relfrozenxid!=frozenxid&&
13951398
(TransactionIdPrecedes(pgcform->relfrozenxid,frozenxid)||
13961399
TransactionIdPrecedes(ReadNextTransactionId(),
13971400
pgcform->relfrozenxid)))
13981401
{
1402+
if (frozenxid_updated)
1403+
*frozenxid_updated= true;
13991404
pgcform->relfrozenxid=frozenxid;
14001405
dirty= true;
14011406
}
14021407

14031408
/* Similarly for relminmxid */
1409+
if (minmulti_updated)
1410+
*minmulti_updated= false;
14041411
if (MultiXactIdIsValid(minmulti)&&
14051412
pgcform->relminmxid!=minmulti&&
14061413
(MultiXactIdPrecedes(pgcform->relminmxid,minmulti)||
14071414
MultiXactIdPrecedes(ReadNextMultiXactId(),pgcform->relminmxid)))
14081415
{
1416+
if (minmulti_updated)
1417+
*minmulti_updated= true;
14091418
pgcform->relminmxid=minmulti;
14101419
dirty= true;
14111420
}

‎src/include/commands/vacuum.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -283,6 +283,8 @@ extern void vac_update_relstats(Relation relation,
283283
boolhasindex,
284284
TransactionIdfrozenxid,
285285
MultiXactIdminmulti,
286+
bool*frozenxid_updated,
287+
bool*minmulti_updated,
286288
boolin_outer_xact);
287289
externvoidvacuum_set_xid_limits(Relationrel,
288290
intfreeze_min_age,intfreeze_table_age,

0 commit comments

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