On Tue, Oct 22, 2024 at 3:12 PM Melanie Plageman
<melanieplage...@gmail.com> wrote:
>
> The attached patch does this.
I realized that I broke relation_statistics_update(). Attached v2 is fixed.
> I've estimated the unfrozen percentage of the table by adding a new
> field to pg_class, relallfrozen, which is updated in the same places
> as relallvisible.
While my relallfrozen column correctly appears in pg_class, I noticed
that it seems like catalog/pg_class_d.h did not have my column added
(this file is auto-generated), despite my adding relallfrozen to
catalog/pg_class.h. Is there something else I have to do when adding a
new column to pg_class?
> At the end of the benchmark, the patched version of Postgres had
> emitted twice as many FPIs as master.
This was meant to say the reverse -- _master_ did twice as many FPIs
as the patch
- Melanie
From 1bd8bbf4a62013e52180d17bc065eea826f784ff Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Fri, 4 Oct 2024 14:29:47 -0400
Subject: [PATCH v2 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.
---
doc/src/sgml/catalogs.sgml | 15 ++++++++++++++
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, 90 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 964c819a02d..174ef29ed6d 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2066,6 +2066,21 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relallfrozen</structfield> <type>int4</type>
+ </para>
+ <para>
+ 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 <link linkend="sql-vacuum"><command>VACUUM</command></link>,
+ <link linkend="sql-analyze"><command>ANALYZE</command></link>, and a few DDL commands such as
+ <link linkend="sql-createindex"><command>CREATE INDEX</command></link>.
+ Every all-frozen page must also be marked all-visible.
+ </para></entry>
+ </row>
+
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>reltoastrelid</structfield> <type>oid</type>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index d82aa3d4896..dff4cd08a99 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;
+ /*
+ * Every block marked all-frozen in the VM must also be marked
+ * all-visible.
+ */
+ 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 12822d0b140..2386009a1d7 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 b1eb8a9bbaf..bc7092dc1ca 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;
HeapTuple newtup;
@@ -160,6 +164,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 391bf04bf5d..6d241f449bb 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202410222
+#define CATALOG_VERSION_NO 202410223
#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
From e29e41ac11b614aafb58412e042c50f37e33ef06 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Fri, 4 Oct 2024 17:06:04 -0400
Subject: [PATCH v2 2/2] Trigger more frequent autovacuums with relallfrozen
Calculate the insert threshold for triggering an autovacuum of a
relation based on the number of unfrozen pages. By only considering the
"active" (unfrozen) portion of the table when calculating how many
tuples to add to the insert threshold, we can trigger more frequent
vacuums of insert-heavy tables and increase the chances of vacuuming
those pages when they still reside in shared buffers.
---
doc/src/sgml/config.sgml | 4 +--
src/backend/postmaster/autovacuum.c | 26 +++++++++++++++++--
src/backend/utils/misc/postgresql.conf.sample | 4 +--
3 files changed, 28 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 934ef5e4691..a303f06e4c9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8683,10 +8683,10 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</term>
<listitem>
<para>
- Specifies a fraction of the table size to add to
+ Specifies a fraction of the active (unfrozen) table size to add to
<varname>autovacuum_vacuum_insert_threshold</varname>
when deciding whether to trigger a <command>VACUUM</command>.
- The default is 0.2 (20% of table size).
+ The default is 0.2 (20% of active table size).
This parameter can only be set in the <filename>postgresql.conf</filename>
file or on the server command line;
but the setting can be overridden for individual tables by
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index dc3cf87abab..9bd96794ae9 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2922,7 +2922,12 @@ relation_needs_vacanalyze(Oid relid,
{
bool force_vacuum;
bool av_enabled;
- float4 reltuples; /* pg_class.reltuples */
+
+ /* From pg_class */
+ float4 reltuples;
+ int32 relpages;
+ int32 relallfrozen;
+ int32 relallvisible;
/* constants from reloptions or GUC variables */
int vac_base_thresh,
@@ -3030,6 +3035,11 @@ relation_needs_vacanalyze(Oid relid,
*/
if (PointerIsValid(tabentry) && AutoVacuumingActive())
{
+ float4 pcnt_unfrozen = 1;
+
+ relpages = classForm->relpages;
+ relallfrozen = classForm->relallfrozen;
+ relallvisible = classForm->relallvisible;
reltuples = classForm->reltuples;
vactuples = tabentry->dead_tuples;
instuples = tabentry->ins_since_vacuum;
@@ -3039,8 +3049,20 @@ relation_needs_vacanalyze(Oid relid,
if (reltuples < 0)
reltuples = 0;
+ if (reltuples == 0 || relpages < 0)
+ relpages = 0;
+
+ if (relallvisible > relpages)
+ relallvisible = relpages;
+
+ if (relallfrozen > relallvisible)
+ relallfrozen = relallvisible;
+
+ if (relpages > 0)
+ pcnt_unfrozen = 1 - ((float4) relallfrozen / relpages);
+
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
- vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
+ vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples * pcnt_unfrozen;
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
/*
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 667e0dc40a2..242155c4230 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -669,8 +669,8 @@
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
-#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over table
- # size before insert vacuum
+#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over active
+ # table size before insert vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
--
2.34.1
