Hi hackers,
Attached is a simple patch to directly use heap scan routines in
vac_update_datfrozenxid(), avoiding the multilayer overhead from the
sysscan infrastructure. The speedup can be noticeable in databases
containing a large number of relations (perhaps due to heavy partition
table usage). This was proposed in [1].
Experiment setup:
* Use -O3 optimized build without asserts, with fsync and autovacuum off,
on my laptop. Other gucs are all at defaults.
* Create tables using pgbench to inflate pg_class's to a decent size.
$ cat << EOF > bench.sql
> select txid_current() AS txid \gset
> CREATE TABLE t:txid(a int);
> EOF
$ pgbench -f ./bench.sql -t 200000 -c 100 -n bench
select pg_size_pretty(pg_relation_size('pg_class'));
pg_size_pretty
----------------
3508 MB
(1 row)
* Use instr_time to record the scan time. See attached instr_vac.diff.
* Run vacuum on any of the created empty tables in the database bench:
Results:
* main as of 68dfecbef2:
bench=# vacuum t1624;
NOTICE: scan took 796.862142 ms
bench=# vacuum t1624;
NOTICE: scan took 793.730688 ms
bench=# vacuum t1624;
NOTICE: scan took 793.963655 ms
* patch:
bench=# vacuum t1624;
NOTICE: scan took 682.283366 ms
bench=# vacuum t1624;
NOTICE: scan took 670.816975 ms
bench=# vacuum t1624;
NOTICE: scan took 683.821717 ms
Regards,
Soumyadeep (Broadcom)
[1]
https://www.postgresql.org/message-id/20221229030329.fbpiitatmowzza6c%40awork3.anarazel.de
From 320e54894a1ad45e2c25a4ee88a6409a9dc1a527 Mon Sep 17 00:00:00 2001
From: Soumyadeep Chakraborty <[email protected]>
Date: Sat, 5 Oct 2024 16:22:56 -0700
Subject: [PATCH v1 1/1] Use heap_getnext() in vac_update_datfrozenxid()
Since we are going to do a full sequential scan without a filter, we can
avoid overhead from the extra layers of sysscan.
---
src/backend/commands/vacuum.c | 9 ++++-----
1 file changed, 4 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index ac8f5d9c25..717e310054 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1588,7 +1588,7 @@ vac_update_datfrozenxid(void)
HeapTuple tuple;
Form_pg_database dbform;
Relation relation;
- SysScanDesc scan;
+ TableScanDesc scan;
HeapTuple classTup;
TransactionId newFrozenXid;
MultiXactId newMinMulti;
@@ -1638,10 +1638,9 @@ vac_update_datfrozenxid(void)
*/
relation = table_open(RelationRelationId, AccessShareLock);
- scan = systable_beginscan(relation, InvalidOid, false,
- NULL, 0, NULL);
+ scan = table_beginscan_catalog(relation, 0, NULL);
- while ((classTup = systable_getnext(scan)) != NULL)
+ while ((classTup = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
volatile FormData_pg_class *classForm = (Form_pg_class) GETSTRUCT(classTup);
TransactionId relfrozenxid = classForm->relfrozenxid;
@@ -1707,7 +1706,7 @@ vac_update_datfrozenxid(void)
}
/* we're done with pg_class */
- systable_endscan(scan);
+ table_endscan(scan);
table_close(relation, AccessShareLock);
/* chicken out if bogus data found */
--
2.43.0
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 717e310054..db3e8a4baf 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1599,6 +1599,9 @@ vac_update_datfrozenxid(void)
ScanKeyData key[1];
void *inplace_state;
+ instr_time before;
+ instr_time after;
+
/*
* Restrict this task to one backend per database. This avoids race
* conditions that would move datfrozenxid or datminmxid backward. It
@@ -1636,6 +1639,7 @@ vac_update_datfrozenxid(void)
*
* See vac_truncate_clog() for the race condition to prevent.
*/
+ INSTR_TIME_SET_CURRENT(before);
relation = table_open(RelationRelationId, AccessShareLock);
scan = table_beginscan_catalog(relation, 0, NULL);
@@ -1708,7 +1712,9 @@ vac_update_datfrozenxid(void)
/* we're done with pg_class */
table_endscan(scan);
table_close(relation, AccessShareLock);
-
+ INSTR_TIME_SET_CURRENT(after);
+ INSTR_TIME_SUBTRACT(after, before);
+ elog(NOTICE, "scan took %lf", INSTR_TIME_GET_MILLISEC(after));
/* chicken out if bogus data found */
if (bogus)
return;
