public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v9 8/8] pg_dump: partitioned index depend on its partitions
10+ messages / 3 participants
[nested] [flat]
* [PATCH v9 8/8] pg_dump: partitioned index depend on its partitions
@ 2020-11-25 23:34 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Justin Pryzby @ 2020-11-25 23:34 UTC (permalink / raw)
This is required for restoring clustered parent index, which is marked INVALID
until indexes have been built on all its child tables, and it's prohibited to
CLUSTER ON an INVALID index
See also: 8cff4f5348d075e063100071013f00a900c32b0f
---
src/backend/commands/tablecmds.c | 6 +++---
src/bin/pg_dump/common.c | 8 ++++++++
2 files changed, 11 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 559fa1d2e5..eb56890311 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -17497,6 +17497,9 @@ validatePartitionedIndex(Relation partedIdx, Relation partedTbl)
table_close(idxRel, RowExclusiveLock);
}
+ /* make sure we see the validation we just did */
+ CommandCounterIncrement();
+
/*
* If this index is in turn a partition of a larger index, validating it
* might cause the parent to become valid also. Try that.
@@ -17508,9 +17511,6 @@ validatePartitionedIndex(Relation partedIdx, Relation partedTbl)
Relation parentIdx,
parentTbl;
- /* make sure we see the validation we just did */
- CommandCounterIncrement();
-
parentIdxId = get_partition_parent(RelationGetRelid(partedIdx));
parentTblId = get_partition_parent(RelationGetRelid(partedTbl));
parentIdx = relation_open(parentIdxId, AccessExclusiveLock);
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 1a261a5545..cdfba058fc 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -429,6 +429,12 @@ flagInhIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
attachinfo[k].parentIdx = parentidx;
attachinfo[k].partitionIdx = index;
+ /*
+ * We want dependencies from parent to partition (so that the
+ * partition index is created first)
+ */
+ addObjectDependency(&parentidx->dobj, index->dobj.dumpId);
+
/*
* We must state the DO_INDEX_ATTACH object's dependencies
* explicitly, since it will not match anything in pg_depend.
@@ -446,6 +452,8 @@ flagInhIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
*/
addObjectDependency(&attachinfo[k].dobj, index->dobj.dumpId);
addObjectDependency(&attachinfo[k].dobj, parentidx->dobj.dumpId);
+ // addObjectDependency(&parentidx->dobj, attachinfo[k].dobj.dumpId);
+
addObjectDependency(&attachinfo[k].dobj,
index->indextable->dobj.dumpId);
addObjectDependency(&attachinfo[k].dobj,
--
2.17.0
--fmvA4kSBHQVZhkR6--
^ permalink raw reply [nested|flat] 10+ messages in thread
* Trigger more frequent autovacuums of heavy insert tables
@ 2024-10-22 19:12 Melanie Plageman <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Melanie Plageman @ 2024-10-22 19:12 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Robert Haas <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; David Rowley <[email protected]>
Hi,
Because of the way autovacuum_vacuum_[insert]_scale_factor works,
autovacuums trigger less frequently as the relation gets larger.
See this math in relation_needs_vacanalyze:
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
For an insert-only table, nearly all the unvacuumed pages will be
eligible to be set all-visible and many will be eligible to be set
all-frozen.
Because normal vacuums can skip all-visible pages, proactively setting
these pages all-visible by vacuuming them sooner often reduces IO
overhead, as they are more likely to still be in shared buffers the
sooner they are vacuumed after last being touched.
Vacuuming these pages more proactively and setting them frozen also
helps amortize the work of aggressive vacuums -- which often
negatively impact the performance of the system.
By considering only the unfrozen portion of the table when calculating
the vacuum insert threshold, we can trigger vacuums more proactively
on insert-heavy tables. This changes the definition of
insert_scale_factor to a percentage of "active" table size. The
attached patch does this.
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.
As an example of this patch in action, I designed a benchmark in which
a table is bulk-loaded with 1 GB of data with COPY FREEZE. Then I run
a custom pgbench with four clients inserting 10 tuples per transaction
into the table for 1_000_000 transactions each.
Note that I configured Postgres to try and observe the effects of this
patch on a compressed timeline. At the bottom of the mail, I've
included details on all of the GUCs I set and why.
Over the course of the same number of transactions, master triggered 8
autovacuums of the table and the patch triggered 16.
With the patch, despite doing twice as many vacuums, autovacuum
workers did 10% fewer reads and 93% fewer writes.
At the end of the benchmark, the patched version of Postgres had
emitted twice as many FPIs as master.
More frequent vacuums means each vacuum scans fewer pages, but, more
interestingly, the first vacuum after a checkpoint is much more
efficient. With the patch, the first vacuum after a checkpoint emits
half as many FPIs. You can see that only 18 pages were newly dirtied.
So, with the patch, the pages being vacuumed are usually still in
shared buffers and still dirty.
Master
------
2024-10-22 13:53:14.293 EDT [3594] LOG: checkpoint starting: time
2024-10-22 13:53:27.849 EDT [3964] LOG: automatic vacuum of table "history"
pages: 0 removed, 753901 remain, 151589 scanned (20.11% of total)
I/O timings: read: 77.962 ms, write: 92.879 ms
avg read rate: 95.840 MB/s, avg write rate: 96.852 MB/s
buffer usage: 268318 hits, 35133 reads, 35504 dirtied
WAL usage: 218320 records, 98672 full page images, 71314906 bytes
Patch
-----
2024-10-22 13:48:43.951 EDT [1471] LOG: checkpoint starting: time
2024-10-22 13:48:59.741 EDT [1802] LOG: automatic vacuum of table "history"
pages: 0 removed, 774375 remain, 121367 scanned (15.67% of total)
I/O timings: read: 2.974 ms, write: 4.434 ms
avg read rate: 1.363 MB/s, avg write rate: 0.126 MB/s
buffer usage: 242817 hits, 195 reads, 18 dirtied
WAL usage: 121389 records, 49216 full page images, 34408291 bytes
While it is true that timing will change significantly from run to
run, I observed over many runs that the more frequent vacuums of the
table led to less overall overhead due to vacuuming pages before they
are evicted from shared buffers.
Below is a detailed description of the benchmark and Postgres configuration:
Benchmark
=========
Set these GUCs:
-- initial table data should fill shared buffers
shared_buffers=1GB
-- give us a chance to try and vacuum the table a bunch of times
autovacuum_naptime=2
-- all checkpoints should be triggered by timing
max/min_wal_size=150GB
-- let's get at least 1 checkpoint during the short benchmark
checkpoint_timeout='2min'
-- let's not be bottlenecked on WAL I/O
wal_buffers='128MB'
wal_compression='zstd'
-- let's get a lot of inserts done quickly
synchronous_commit='off'
-- let's not take too many breaks for vacuum delay
vacuum_cost_limit = 2000
-- so we can see what happened
log_checkpoints = on
log_autovacuum_min_duration=0
-- so we can get more stats
track_wal_io_timing=on
track_io_timing = on
First I created the table that you will see later in DDL and loaded it
by running pgbench in the same way as I do in the benchmark until
there was 1 GB of table data. Then I copied that out to a file
'history.data'
I included an index because the more up-to-date visibility map would
benefit index-only scans -- which you could add to the benchmark if
you want.
DDL
--
BEGIN;
DROP TABLE IF EXISTS history;
CREATE TABLE history(
id BIGINT,
client_id INT NOT NULL,
mtime TIMESTAMPTZ DEFAULT NOW(),
data TEXT);
COPY history FROM 'history.data' WITH (freeze on);
CREATE INDEX ON history(id);
COMMIT;
pgbench \
--random-seed=0 \
--no-vacuum \
-M prepared \
-c 4 \
-j 4 \
-t 1000000 \
-R 27000 \
-f- <<EOF
INSERT INTO history(id, client_id, data)
VALUES
(:client_id, :client_id, repeat('a', 90)),
(:client_id, :client_id, repeat('b', 90)),
(:client_id, :client_id, repeat('c', 90)),
(:client_id, :client_id, repeat('d', 90)),
(:client_id, :client_id, repeat('e', 90)),
(:client_id, :client_id, repeat('f', 90)),
(:client_id, :client_id, repeat('g', 90)),
(:client_id, :client_id, repeat('h', 90)),
(:client_id, :client_id, repeat('i', 90)),
(:client_id, :client_id, repeat('j', 90));
EOF
- Melanie
Attachments:
[application/octet-stream] v1-0002-Trigger-more-frequent-autovacuums-with-relallfroz.patch (4.2K, ../../CAAKRu_aj-P7YyBz_cPNwztz6ohP+vWis=iz3YcomkB3NpYA--w@mail.gmail.com/2-v1-0002-Trigger-more-frequent-autovacuums-with-relallfroz.patch)
download | inline diff:
From 6de3d9c7c1b1203a1b7680b74bef70fa7ab6b653 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
Date: Fri, 4 Oct 2024 17:06:04 -0400
Subject: [PATCH v1 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 934ef5e469..a303f06e4c 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 dc3cf87aba..9bd96794ae 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 667e0dc40a..242155c423 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.45.2
[application/octet-stream] v1-0001-Add-relallfrozen-to-pg_class.patch (13.6K, ../../CAAKRu_aj-P7YyBz_cPNwztz6ohP+vWis=iz3YcomkB3NpYA--w@mail.gmail.com/3-v1-0001-Add-relallfrozen-to-pg_class.patch)
download | inline diff:
From 564851f3d0450c604732ecf468ce46457d36d989 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
Date: Fri, 4 Oct 2024 14:29:47 -0400
Subject: [PATCH v1 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 | 9 +++++++++
src/backend/utils/cache/relcache.c | 2 ++
src/include/catalog/pg_class.h | 3 +++
src/include/commands/vacuum.h | 1 +
11 files changed, 81 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 964c819a02..174ef29ed6 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 d82aa3d489..dff4cd08a9 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 0078a12f26..d150d2b9a7 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 12822d0b14..2386009a1d 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 38fb4c3ef2..0928592272 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 78f96789b0..4376355066 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 ac8f5d9c25..7ed4df509c 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 1a6d1640c3..1b1f02546f 100644
--- a/src/backend/statistics/relation_stats.c
+++ b/src/backend/statistics/relation_stats.c
@@ -160,6 +160,15 @@ 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
+ * NULL. It will be updated the next time these fields are
+ * updated.
+ */
+ replaces[ncols] = Anum_pg_class_relallfrozen;
+ nulls[ncols] = true;
}
}
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index c326f687eb..4bd9e10310 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/pg_class.h b/src/include/catalog/pg_class.h
index 0fc2c093b0..659ee68620 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) */
+ 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 759f9a87d3..c3fd2919e6 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.45.2
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger more frequent autovacuums of heavy insert tables
@ 2024-10-23 21:21 Melanie Plageman <[email protected]>
parent: Melanie Plageman <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Melanie Plageman @ 2024-10-23 21:21 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Robert Haas <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; David Rowley <[email protected]>
On Tue, Oct 22, 2024 at 3:12 PM Melanie Plageman
<[email protected]> 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
Attachments:
[text/x-patch] v2-0001-Add-relallfrozen-to-pg_class.patch (14.9K, ../../CAAKRu_bQWDciNUXtnvpLQyMmv9Q8H+yPoe=FwcvyL=hACT5rWw@mail.gmail.com/2-v2-0001-Add-relallfrozen-to-pg_class.patch)
download | inline diff:
From 1bd8bbf4a62013e52180d17bc065eea826f784ff Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
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
[text/x-patch] v2-0002-Trigger-more-frequent-autovacuums-with-relallfroz.patch (4.2K, ../../CAAKRu_bQWDciNUXtnvpLQyMmv9Q8H+yPoe=FwcvyL=hACT5rWw@mail.gmail.com/3-v2-0002-Trigger-more-frequent-autovacuums-with-relallfroz.patch)
download | inline diff:
From e29e41ac11b614aafb58412e042c50f37e33ef06 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger more frequent autovacuums of heavy insert tables
@ 2025-02-07 17:37 Nathan Bossart <[email protected]>
parent: Melanie Plageman <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Nathan Bossart @ 2025-02-07 17:37 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: pgsql-hackers; Robert Haas <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; David Rowley <[email protected]>
On Tue, Oct 22, 2024 at 03:12:53PM -0400, Melanie Plageman wrote:
> By considering only the unfrozen portion of the table when calculating
> the vacuum insert threshold, we can trigger vacuums more proactively
> on insert-heavy tables. This changes the definition of
> insert_scale_factor to a percentage of "active" table size. The
> attached patch does this.
I think this is a creative idea. My first reaction is to question whether
it makes send to have two strategies for this sort of thing:
autovacuum_vacuum_max_threshold for updates/deletes and this for inserts.
Perhaps we don't want to more aggressively clean up bloat (except for the
very largest tables via the hard cap), but we do want to more aggressively
mark newly-inserted tuples frozen. I'm curious what you think.
> 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.
Wouldn't relallvisible be sufficient here? We'll skip all-visible pages
unless this is an anti-wraparound vacuum, at which point I would think the
insert threshold goes out the window.
> More frequent vacuums means each vacuum scans fewer pages, but, more
> interestingly, the first vacuum after a checkpoint is much more
> efficient. With the patch, the first vacuum after a checkpoint emits
> half as many FPIs. You can see that only 18 pages were newly dirtied.
> So, with the patch, the pages being vacuumed are usually still in
> shared buffers and still dirty.
Are you aware of any scenarios where your proposed strategy might make
things worse? From your test results, it sounds like these vacuums ought
to usually be relatively efficient, so sending insert-only tables to the
front of the line is normally okay, but maybe that's not always true.
--
nathan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger more frequent autovacuums of heavy insert tables
@ 2025-02-07 19:21 Melanie Plageman <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Melanie Plageman @ 2025-02-07 19:21 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: pgsql-hackers; Robert Haas <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; David Rowley <[email protected]>
On Fri, Feb 7, 2025 at 12:37 PM Nathan Bossart <[email protected]> wrote:
>
> On Tue, Oct 22, 2024 at 03:12:53PM -0400, Melanie Plageman wrote:
> > By considering only the unfrozen portion of the table when calculating
> > the vacuum insert threshold, we can trigger vacuums more proactively
> > on insert-heavy tables. This changes the definition of
> > insert_scale_factor to a percentage of "active" table size. The
> > attached patch does this.
>
> I think this is a creative idea.
Indeed. I can't take much credit for it -- Andres suggested this
direction during an off-list conversation where I was complaining
about how difficult it was to benchmark my vacuum eager scanning patch
set [1] because normal vacuums were so rarely triggered for
insert-only tables after the first aggressive vacuum.
> My first reaction is to question whether
> it makes send to have two strategies for this sort of thing:
> autovacuum_vacuum_max_threshold for updates/deletes and this for inserts.
> Perhaps we don't want to more aggressively clean up bloat (except for the
> very largest tables via the hard cap), but we do want to more aggressively
> mark newly-inserted tuples frozen. I'm curious what you think.
The goal with insert-only tables is to set the whole page frozen in
the VM. So, the number of pages is more important than the total
number of tuples inserted. Whereas, with updates/deletes, it seems
like the total amount of garbage (# tuples) needing cleaning is more
important.
My intuition (maybe wrong) is that it is more common to have a bunch
of pages with a single (or few) updates/deletes than it is to have a
bunch of pages with a single insert. This patch is mostly meant to
trigger vacuums sooner on large insert-only or bulk loaded tables.
Though, it is more common to have a cluster of hot pages than
uniformly distributed updates and deletes...
> > 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.
>
> Wouldn't relallvisible be sufficient here? We'll skip all-visible pages
> unless this is an anti-wraparound vacuum, at which point I would think the
> insert threshold goes out the window.
It's a great question. There are a couple reasons why I don't think so.
I think this might lead to triggering vacuums too often for
insert-mostly tables. For those tables, the pages that are not
all-visible will largely be just those with data that is new since the
last vacuum. And if we trigger vacuums based off of the % not
all-visible, we might decrease the number of cases where we are able
to vacuum inserted data and freeze it the first time it is vacuumed --
thereby increasing the total amount of work.
As for your point about us skipping all-visible pages except in
anti-wraparound vacuums -- that's not totally true. Autovacuums
triggered by the insert or update/delete thresholds and not by
autovacuum_freeze_max_age can also be aggressive (that's based on
vacuum_freeze_table_age). Aggressive vacuums scan all-visible pages.
And we actually want to trigger more normal aggressive (non-anti-wrap)
vacuums because anti-wraparound vacuums are not canceled by
conflicting lock requests (like those needed by DDL) -- see
PROC_VACUUM_FOR_WRAPAROUND in ProcSleep().
We also scan a surprising number of all-visible pages in practice due
to SKIP_PAGES_THRESHOLD. I was pretty taken aback while testing [1]
how many all-visible pages we scan due to this optimization. And, I'm
planning on merging [1] in the next few days, so this will also
increase the number of all-visible pages scanned during normal
vacuums.
> > More frequent vacuums means each vacuum scans fewer pages, but, more
> > interestingly, the first vacuum after a checkpoint is much more
> > efficient. With the patch, the first vacuum after a checkpoint emits
> > half as many FPIs. You can see that only 18 pages were newly dirtied.
> > So, with the patch, the pages being vacuumed are usually still in
> > shared buffers and still dirty.
>
> Are you aware of any scenarios where your proposed strategy might make
> things worse? From your test results, it sounds like these vacuums ought
> to usually be relatively efficient, so sending insert-only tables to the
> front of the line is normally okay, but maybe that's not always true.
So, of course they aren't exactly at the front of the line since we
autovacuum based on the order in pg_class. But, I suppose if you spend
a bunch of time vacuuming an insert-mostly table you previously would
have skipped instead of some other table -- that is effectively
prioritizing the insert-mostly tables.
For insert-only/mostly tables, what you are ideally doing is vacuuming
more frequently and handling a small number of pages each vacuum of
the relation, so it has a low performance impact. I suppose if you
only have a few autovacuum workers and an equal number of massive
insert-only tables, you could end up starving other actively updated
tables of vacuum resources. But, those insert-only tables would have
to be vacuumed eventually -- and I imagine that the impact of a
massive aggressive vacuum of all of the data in those tables would be
more disruptive than some extra bloat in your other tables.
I'd be interested if other people with more field experience can
imagine starvation scenarios that would be much worse with this patch.
What kinds of starvation scenarios do you normally see?
In terms of specific, dramatic differences in behavior (since this
wouldn't be hidden behind a guc) people might be surprised by how soon
tables start being vacuumed after a huge COPY FREEZE.
- Melanie
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger more frequent autovacuums of heavy insert tables
@ 2025-02-07 20:38 Nathan Bossart <[email protected]>
parent: Melanie Plageman <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Nathan Bossart @ 2025-02-07 20:38 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: pgsql-hackers; Robert Haas <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; David Rowley <[email protected]>
On Fri, Feb 07, 2025 at 02:21:07PM -0500, Melanie Plageman wrote:
> On Fri, Feb 7, 2025 at 12:37 PM Nathan Bossart <[email protected]> wrote:
>> My first reaction is to question whether
>> it makes send to have two strategies for this sort of thing:
>> autovacuum_vacuum_max_threshold for updates/deletes and this for inserts.
>> Perhaps we don't want to more aggressively clean up bloat (except for the
>> very largest tables via the hard cap), but we do want to more aggressively
>> mark newly-inserted tuples frozen. I'm curious what you think.
>
> The goal with insert-only tables is to set the whole page frozen in
> the VM. So, the number of pages is more important than the total
> number of tuples inserted. Whereas, with updates/deletes, it seems
> like the total amount of garbage (# tuples) needing cleaning is more
> important.
I think this is a reasonable position. To be clear, I don't have a problem
with having different strategies, or even with swapping
autovacuum_vacuum_max_threshold with a similar change, if it's the right
thing to do. I just want to be able to articulate why they're different.
>> Wouldn't relallvisible be sufficient here? We'll skip all-visible pages
>> unless this is an anti-wraparound vacuum, at which point I would think the
>> insert threshold goes out the window.
>
> It's a great question. There are a couple reasons why I don't think so.
>
> I think this might lead to triggering vacuums too often for
> insert-mostly tables. For those tables, the pages that are not
> all-visible will largely be just those with data that is new since the
> last vacuum. And if we trigger vacuums based off of the % not
> all-visible, we might decrease the number of cases where we are able
> to vacuum inserted data and freeze it the first time it is vacuumed --
> thereby increasing the total amount of work.
Rephrasing to make sure I understand correctly: you're saying that using
all-frozen would trigger less frequent insert vacuums, which would give us
a better chance of freezing more than more frequent insert vacuums
triggered via all-visible? My suspicion is that the difference would tend
to be quite subtle in practice, but I have no concrete evidence to back
that up.
--
nathan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger more frequent autovacuums of heavy insert tables
@ 2025-02-07 20:57 Melanie Plageman <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Melanie Plageman @ 2025-02-07 20:57 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: pgsql-hackers; Robert Haas <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; David Rowley <[email protected]>
On Fri, Feb 7, 2025 at 3:38 PM Nathan Bossart <[email protected]> wrote:
>
> On Fri, Feb 07, 2025 at 02:21:07PM -0500, Melanie Plageman wrote:
> > On Fri, Feb 7, 2025 at 12:37 PM Nathan Bossart <[email protected]> wrote:
> >>
> >> Wouldn't relallvisible be sufficient here? We'll skip all-visible pages
> >> unless this is an anti-wraparound vacuum, at which point I would think the
> >> insert threshold goes out the window.
> >
> > It's a great question. There are a couple reasons why I don't think so.
> >
> > I think this might lead to triggering vacuums too often for
> > insert-mostly tables. For those tables, the pages that are not
> > all-visible will largely be just those with data that is new since the
> > last vacuum. And if we trigger vacuums based off of the % not
> > all-visible, we might decrease the number of cases where we are able
> > to vacuum inserted data and freeze it the first time it is vacuumed --
> > thereby increasing the total amount of work.
>
> Rephrasing to make sure I understand correctly: you're saying that using
> all-frozen would trigger less frequent insert vacuums, which would give us
> a better chance of freezing more than more frequent insert vacuums
> triggered via all-visible? My suspicion is that the difference would tend
> to be quite subtle in practice, but I have no concrete evidence to back
> that up.
You understood me correctly.
As for relallfrozen, one of the justifications for adding it to
pg_class is actually for the visibility it would provide. We have no
way of knowing how many all-visible but not all-frozen pages there are
on users' systems without pg_visibility. If users had this
information, they could potentially tune their freeze-related settings
more aggressively. Regularly reading the whole visibility map with
pg_visibilitymap_summary() is pretty hard to justify on most
production systems. But querying pg_class every 10 minutes or
something is much more reasonable.
- Melanie
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger more frequent autovacuums of heavy insert tables
@ 2025-02-07 21:05 Nathan Bossart <[email protected]>
parent: Melanie Plageman <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Nathan Bossart @ 2025-02-07 21:05 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: pgsql-hackers; Robert Haas <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; David Rowley <[email protected]>
On Fri, Feb 07, 2025 at 03:57:49PM -0500, Melanie Plageman wrote:
> As for relallfrozen, one of the justifications for adding it to
> pg_class is actually for the visibility it would provide. We have no
> way of knowing how many all-visible but not all-frozen pages there are
> on users' systems without pg_visibility. If users had this
> information, they could potentially tune their freeze-related settings
> more aggressively. Regularly reading the whole visibility map with
> pg_visibilitymap_summary() is pretty hard to justify on most
> production systems. But querying pg_class every 10 minutes or
> something is much more reasonable.
If we need it anyway, then I have no objections to using a freeze-related
metric for a freeze-related feature.
Okay, I'll actually look at the patches next...
--
nathan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger more frequent autovacuums of heavy insert tables
@ 2025-02-17 16:11 Nathan Bossart <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Nathan Bossart @ 2025-02-17 16:11 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: pgsql-hackers; Robert Haas <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; David Rowley <[email protected]>
On Fri, Feb 07, 2025 at 03:05:09PM -0600, Nathan Bossart wrote:
> Okay, I'll actually look at the patches next...
Ugh, it's already been 10 days since I said that. A couple of thoughts on
0001:
I'm not sure I understand the reason for capping relallfrozen to
relallvisible. From upthread, I gather this is mostly to deal with manual
statistics manipulation, but my first reaction is that we should just let
those values be bogus. Is there something that fundamentally requires
relallfrozen to be <= relallvisible? These are only estimates, so I don't
think it would be that surprising for them to defy this expectation.
Should we allow manipulating relallfrozen like we do relallvisible? My
assumption is that would even be required for the ongoing statistics
import/export work.
Upthread, you mentioned that you weren't seeing relallfrozen in
pg_class_d.h. I checked on my machine and see it there as expected. Are
you still missing it?
--
nathan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger more frequent autovacuums of heavy insert tables
@ 2025-02-19 21:36 Melanie Plageman <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Melanie Plageman @ 2025-02-19 21:36 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: pgsql-hackers; Robert Haas <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; David Rowley <[email protected]>
On Mon, Feb 17, 2025 at 11:11 AM Nathan Bossart
<[email protected]> wrote:
>
> On Fri, Feb 07, 2025 at 03:05:09PM -0600, Nathan Bossart wrote:
> > Okay, I'll actually look at the patches next...
Thanks for taking a look!
> I'm not sure I understand the reason for capping relallfrozen to
> relallvisible. From upthread, I gather this is mostly to deal with manual
> statistics manipulation, but my first reaction is that we should just let
> those values be bogus. Is there something that fundamentally requires
> relallfrozen to be <= relallvisible? These are only estimates, so I don't
> think it would be that surprising for them to defy this expectation.
I wasn't quite sure what to do here. I see your perspective: for
example, reltuples can't possibly be more than relpages but we don't
do any validation of that. My rationale wasn't exactly principled, so
I'll change it to not cap relallfrozen.
This makes me think I should also not cap relallfrozen when using it
in relation_needs_vacanalyze(). There I cap it to relallvisible and
relallvisible is capped to relpages. One of the ideas behind letting
people modify these stats in pg_class is that they can change a single
field to see what the effect on their system is, right?
> Should we allow manipulating relallfrozen like we do relallvisible? My
> assumption is that would even be required for the ongoing statistics
> import/export work.
Why would it be required for the statistics import/export work?
> Upthread, you mentioned that you weren't seeing relallfrozen in
> pg_class_d.h. I checked on my machine and see it there as expected. Are
> you still missing it?
I see it now. No idea what was happening.
- Melanie
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-02-19 21:36 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-25 23:34 [PATCH v9 8/8] pg_dump: partitioned index depend on its partitions Justin Pryzby <[email protected]>
2024-10-22 19:12 Trigger more frequent autovacuums of heavy insert tables Melanie Plageman <[email protected]>
2024-10-23 21:21 ` Re: Trigger more frequent autovacuums of heavy insert tables Melanie Plageman <[email protected]>
2025-02-07 17:37 ` Re: Trigger more frequent autovacuums of heavy insert tables Nathan Bossart <[email protected]>
2025-02-07 19:21 ` Re: Trigger more frequent autovacuums of heavy insert tables Melanie Plageman <[email protected]>
2025-02-07 20:38 ` Re: Trigger more frequent autovacuums of heavy insert tables Nathan Bossart <[email protected]>
2025-02-07 20:57 ` Re: Trigger more frequent autovacuums of heavy insert tables Melanie Plageman <[email protected]>
2025-02-07 21:05 ` Re: Trigger more frequent autovacuums of heavy insert tables Nathan Bossart <[email protected]>
2025-02-17 16:11 ` Re: Trigger more frequent autovacuums of heavy insert tables Nathan Bossart <[email protected]>
2025-02-19 21:36 ` Re: Trigger more frequent autovacuums of heavy insert tables Melanie Plageman <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox