From caab36b29b9a9e2582283279d2e9b0fa404307ae Mon Sep 17 00:00:00 2001 From: Melanie Plageman Date: Fri, 25 Oct 2024 10:43:39 -0400 Subject: [PATCH v3 1/2] Add relallfrozen to pg_class Add relallfrozen, an estimate of the number of pages marked all-frozen in the visibility map. pg_class already has relallvisible, an estimate of the number of pages in the relation marked all-visible in the visibility map. This is used primarily for planning. relallfrozen, however, is useful for estimating the outstanding number of all-visible but not all-frozen pages in the relation for the purposes of scheduling manual VACUUMs and tuning vacuum freeze parameters. In the future, this field could be used to trigger more frequent vacuums on insert-focused workloads with significant volume of frozen data. Author: Melanie Plageman Reviewed-by: Greg Sabino Mullane --- doc/src/sgml/catalogs.sgml | 20 ++++++++++++++++++ src/backend/access/heap/vacuumlazy.c | 17 ++++++++++++---- src/backend/catalog/heap.c | 2 ++ src/backend/catalog/index.c | 27 +++++++++++++++++-------- src/backend/commands/analyze.c | 12 +++++------ src/backend/commands/cluster.c | 5 +++++ src/backend/commands/vacuum.c | 6 ++++++ src/backend/statistics/relation_stats.c | 20 +++++++++++++++--- src/backend/utils/cache/relcache.c | 2 ++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_class.h | 3 +++ src/include/commands/vacuum.h | 1 + 12 files changed, 95 insertions(+), 22 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 964c819a02d..392a1f71eb0 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2066,6 +2066,26 @@ SCRAM-SHA-256$<iteration count>:&l + + + relallfrozen int4 + + + Number of pages that are marked all-frozen in the table's visibility + map. This is only an estimate used for triggering autovacuums. It is + updated by VACUUM, + ANALYZE, + and a few DDL commands such as + CREATE INDEX. + relallfrozen must be less than or equal to + relallvisible as an all-frozen page must be + all-visible. If relallvisible was updated + manually, relallfrozen will be -1 until the + next time they are updated. + + + + reltoastrelid oid diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index d82aa3d4896..776d1673bbb 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -303,7 +303,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, minmulti_updated; BlockNumber orig_rel_pages, new_rel_pages, - new_rel_allvisible; + new_rel_allvisible, + new_rel_allfrozen; PGRUsage ru0; TimestampTz starttime = 0; PgStat_Counter startreadtime = 0, @@ -558,10 +559,17 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, * pg_class.relpages to */ new_rel_pages = vacrel->rel_pages; /* After possible rel truncation */ - visibilitymap_count(rel, &new_rel_allvisible, NULL); + visibilitymap_count(rel, &new_rel_allvisible, &new_rel_allfrozen); if (new_rel_allvisible > new_rel_pages) new_rel_allvisible = new_rel_pages; + /* + * An all-frozen block _must_ be all-visible. As such, clamp the count of + * all-frozen blocks to the count of all-visible blocks. + */ + if (new_rel_allfrozen > new_rel_allvisible) + new_rel_allfrozen = new_rel_allvisible; + /* * Now actually update rel's pg_class entry. * @@ -570,7 +578,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, * scan every page that isn't skipped using the visibility map. */ vac_update_relstats(rel, new_rel_pages, vacrel->new_live_tuples, - new_rel_allvisible, vacrel->nindexes > 0, + new_rel_allvisible, new_rel_allfrozen, + vacrel->nindexes > 0, vacrel->NewRelfrozenXid, vacrel->NewRelminMxid, &frozenxid_updated, &minmulti_updated, false); @@ -3100,7 +3109,7 @@ update_relstats_all_indexes(LVRelState *vacrel) vac_update_relstats(indrel, istat->num_pages, istat->num_index_tuples, - 0, + 0, 0, false, InvalidTransactionId, InvalidMultiXactId, diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 0078a12f26e..d150d2b9a72 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -920,6 +920,7 @@ InsertPgClassTuple(Relation pg_class_desc, values[Anum_pg_class_relpages - 1] = Int32GetDatum(rd_rel->relpages); values[Anum_pg_class_reltuples - 1] = Float4GetDatum(rd_rel->reltuples); values[Anum_pg_class_relallvisible - 1] = Int32GetDatum(rd_rel->relallvisible); + values[Anum_pg_class_relallfrozen - 1] = Int32GetDatum(rd_rel->relallfrozen); values[Anum_pg_class_reltoastrelid - 1] = ObjectIdGetDatum(rd_rel->reltoastrelid); values[Anum_pg_class_relhasindex - 1] = BoolGetDatum(rd_rel->relhasindex); values[Anum_pg_class_relisshared - 1] = BoolGetDatum(rd_rel->relisshared); @@ -990,6 +991,7 @@ AddNewRelationTuple(Relation pg_class_desc, new_rel_reltup->relpages = 0; new_rel_reltup->reltuples = -1; new_rel_reltup->relallvisible = 0; + new_rel_reltup->relallfrozen = 0; /* Sequences always have a known size */ if (relkind == RELKIND_SEQUENCE) diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 9162b9f81a2..6f1bc159ecd 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -2795,8 +2795,8 @@ FormIndexDatum(IndexInfo *indexInfo, * hasindex: set relhasindex to this value * reltuples: if >= 0, set reltuples to this value; else no change * - * If reltuples >= 0, relpages and relallvisible are also updated (using - * RelationGetNumberOfBlocks() and visibilitymap_count()). + * If reltuples >= 0, relpages, relallvisible, and relallfrozen are also + * updated (using RelationGetNumberOfBlocks() and visibilitymap_count()). * * NOTE: an important side-effect of this operation is that an SI invalidation * message is sent out to all backends --- including me --- causing relcache @@ -2841,8 +2841,8 @@ index_update_stats(Relation rel, * transaction could still fail before committing. Setting relhasindex * true is safe even if there are no indexes (VACUUM will eventually fix * it). And of course the new relpages and reltuples counts are correct - * regardless. However, we don't want to change relpages (or - * relallvisible) if the caller isn't providing an updated reltuples + * regardless. However, we don't want to change relpages (or relallvisible + * and relallfrozen) if the caller isn't providing an updated reltuples * count, because that would bollix the reltuples/relpages ratio which is * what's really important. */ @@ -2888,12 +2888,18 @@ index_update_stats(Relation rel, if (reltuples >= 0 && !IsBinaryUpgrade) { BlockNumber relpages = RelationGetNumberOfBlocks(rel); - BlockNumber relallvisible; + BlockNumber relallvisible = 0; + BlockNumber relallfrozen = 0; + /* don't bother for indexes */ if (rd_rel->relkind != RELKIND_INDEX) - visibilitymap_count(rel, &relallvisible, NULL); - else /* don't bother for indexes */ - relallvisible = 0; + { + visibilitymap_count(rel, &relallvisible, &relallfrozen); + + /* Every all-frozen page must also be set all-visible in the VM */ + if (relallfrozen > relallvisible) + relallfrozen = relallvisible; + } if (rd_rel->relpages != (int32) relpages) { @@ -2910,6 +2916,11 @@ index_update_stats(Relation rel, rd_rel->relallvisible = (int32) relallvisible; dirty = true; } + if (rd_rel->relallfrozen != (int32) relallfrozen) + { + rd_rel->relallfrozen = (int32) relallfrozen; + dirty = true; + } } /* diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index 38fb4c3ef23..0928592272e 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -630,12 +630,11 @@ do_analyze_rel(Relation onerel, VacuumParams *params, */ if (!inh) { - BlockNumber relallvisible; + BlockNumber relallvisible = 0; + BlockNumber relallfrozen = 0; if (RELKIND_HAS_STORAGE(onerel->rd_rel->relkind)) - visibilitymap_count(onerel, &relallvisible, NULL); - else - relallvisible = 0; + visibilitymap_count(onerel, &relallvisible, &relallfrozen); /* * Update pg_class for table relation. CCI first, in case acquirefunc @@ -646,6 +645,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params, relpages, totalrows, relallvisible, + relallfrozen, hasindex, InvalidTransactionId, InvalidMultiXactId, @@ -662,7 +662,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params, vac_update_relstats(Irel[ind], RelationGetNumberOfBlocks(Irel[ind]), totalindexrows, - 0, + 0, 0, false, InvalidTransactionId, InvalidMultiXactId, @@ -678,7 +678,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params, */ CommandCounterIncrement(); vac_update_relstats(onerel, -1, totalrows, - 0, hasindex, InvalidTransactionId, + 0, 0, hasindex, InvalidTransactionId, InvalidMultiXactId, NULL, NULL, in_outer_xact); diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 78f96789b0e..43763550668 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -1224,6 +1224,7 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class, int32 swap_pages; float4 swap_tuples; int32 swap_allvisible; + int32 swap_allfrozen; swap_pages = relform1->relpages; relform1->relpages = relform2->relpages; @@ -1236,6 +1237,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class, swap_allvisible = relform1->relallvisible; relform1->relallvisible = relform2->relallvisible; relform2->relallvisible = swap_allvisible; + + swap_allfrozen = relform1->relallfrozen; + relform1->relallfrozen = relform2->relallfrozen; + relform2->relallfrozen = swap_allfrozen; } /* diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index ac8f5d9c259..7ed4df509c5 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -1410,6 +1410,7 @@ void vac_update_relstats(Relation relation, BlockNumber num_pages, double num_tuples, BlockNumber num_all_visible_pages, + BlockNumber num_all_frozen_pages, bool hasindex, TransactionId frozenxid, MultiXactId minmulti, bool *frozenxid_updated, bool *minmulti_updated, @@ -1459,6 +1460,11 @@ vac_update_relstats(Relation relation, pgcform->relallvisible = (int32) num_all_visible_pages; dirty = true; } + if (pgcform->relallfrozen != (int32) num_all_frozen_pages) + { + pgcform->relallfrozen = (int32) num_all_frozen_pages; + dirty = true; + } /* Apply DDL updates, but not inside an outer transaction (see above) */ diff --git a/src/backend/statistics/relation_stats.c b/src/backend/statistics/relation_stats.c index 5a2aabc921e..69892f19978 100644 --- a/src/backend/statistics/relation_stats.c +++ b/src/backend/statistics/relation_stats.c @@ -54,6 +54,10 @@ static bool relation_statistics_update(FunctionCallInfo fcinfo, int elevel); /* * Internal function for modifying statistics for a relation. + * + * Up to four pg_class columns may be updated even though only three relation + * statistics may be modified; relallfrozen is always set to -1 when + * relallvisible is updated manually. */ static bool relation_statistics_update(FunctionCallInfo fcinfo, int elevel) @@ -62,9 +66,9 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel) Relation crel; HeapTuple ctup; Form_pg_class pgcform; - int replaces[3] = {0}; - Datum values[3] = {0}; - bool nulls[3] = {0}; + int replaces[4] = {0}; + Datum values[4] = {0}; + bool nulls[4] = {0}; int ncols = 0; TupleDesc tupdesc; bool result = true; @@ -154,6 +158,16 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel) replaces[ncols] = Anum_pg_class_relallvisible; values[ncols] = Int32GetDatum(relallvisible); ncols++; + + /* + * If we are modifying relallvisible manually, it is not clear + * what relallfrozen value would make sense. Therefore, set it to + * -1, or unknown. It will be updated the next time these fields + * are updated. + */ + replaces[ncols] = Anum_pg_class_relallfrozen; + values[ncols] = Int32GetDatum(-1); + ncols++; } } diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index c326f687eb4..4bd9e10310c 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -1936,6 +1936,7 @@ formrdesc(const char *relationName, Oid relationReltype, relation->rd_rel->relpages = 0; relation->rd_rel->reltuples = -1; relation->rd_rel->relallvisible = 0; + relation->rd_rel->relallfrozen = 0; relation->rd_rel->relkind = RELKIND_RELATION; relation->rd_rel->relnatts = (int16) natts; relation->rd_rel->relam = HEAP_TABLE_AM_OID; @@ -3931,6 +3932,7 @@ RelationSetNewRelfilenumber(Relation relation, char persistence) classform->relpages = 0; /* it's empty until further notice */ classform->reltuples = -1; classform->relallvisible = 0; + classform->relallfrozen = 0; } classform->relfrozenxid = freezeXid; classform->relminmxid = minmulti; diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index fc1c125d0d0..21dfcb16d27 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202410242 +#define CATALOG_VERSION_NO 202410251 #endif diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index 0fc2c093b0d..b915bef9aa5 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -68,6 +68,9 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,Relat /* # of all-visible blocks (not always up-to-date) */ int32 relallvisible BKI_DEFAULT(0); + /* # of all-frozen blocks (not always up-to-date; -1 means "unknown") */ + int32 relallfrozen BKI_DEFAULT(0); + /* OID of toast table; 0 if none */ Oid reltoastrelid BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_class); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 759f9a87d38..c3fd2919e64 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -329,6 +329,7 @@ extern void vac_update_relstats(Relation relation, BlockNumber num_pages, double num_tuples, BlockNumber num_all_visible_pages, + BlockNumber num_all_frozen_pages, bool hasindex, TransactionId frozenxid, MultiXactId minmulti, -- 2.34.1