Hi, Peter!
Sorry! For a some time i forgot about this thread and forgot to
thank you for your answer.
Thereby now its clear for me that this patch allows the autovacuum to win some
time between OldestXmin and nextXID that could not be used before.
I think, it maybe especially useful for high-load applications.
Digging depeer, i found some inconsistency between current docs and
the real behavior and would like to bring this to your attention.
Now the doc says that an aggressive vacuum scan will occur for any
table whose multixact-age is greater than autovacuum_multixact_freeze_max_age.
But really vacuum_get_cutoffs() will return true when
multixact-age is greater or equal than autovacuum_multixact_freeze_max_age
if relminmxid is not equal to one.
If relminmxid is equal to one then vacuum_get_cutoffs() return true even
multixact-age is less by one then autovacuum_multixact_freeze_max_age.
For instance, if relminmxid = 1 and multixact_freeze_table_age
= 100,
vacuum will start to be aggressive from the age of 99
(when ReadNextMultiXactId()
= 100).
The patch attached attempts to fix this and tries to use semantic like in the
doc.
The similar fix was made for common xacts too.
Additional check for relminxid allows to disable agressive scan
at all if it is invalid. But i'm not sure if such a check is needed here.
Please take it into account.
With kindly regards,
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
commit 40ea108a5be00659fe6799897035e1f9abf122a8
Author: Anton A. Melnikov <a.melni...@postgrespro.ru>
Date: Fri Apr 5 08:08:56 2024 +0300
Make vacuum scan aggressive for any table whose xact/multixact-age is strictly greater than xact/multixact_freeze_table_age.
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index b589279d49f..e397a394a88 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1083,6 +1083,8 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
MultiXactId nextMXID,
safeOldestMxact,
aggressiveMXIDCutoff;
+ int32 xact_age;
+ int32 multixact_age;
/* Use mutable copies of freeze age parameters */
freeze_min_age = params->freeze_min_age;
@@ -1093,6 +1095,10 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
/* Set pg_class fields in cutoffs */
cutoffs->relfrozenxid = rel->rd_rel->relfrozenxid;
cutoffs->relminmxid = rel->rd_rel->relminmxid;
+ /* ??? Should we do this check here? */
+ /* Avoid agressive scan if relminmxid is invalid. */
+ if (!MultiXactIdIsValid(cutoffs->relminmxid))
+ cutoffs->relminmxid = INT_MAX;
/*
* Acquire OldestXmin.
@@ -1200,8 +1206,8 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
aggressiveXIDCutoff = nextXID - freeze_table_age;
if (!TransactionIdIsNormal(aggressiveXIDCutoff))
aggressiveXIDCutoff = FirstNormalTransactionId;
- if (TransactionIdPrecedesOrEquals(rel->rd_rel->relfrozenxid,
- aggressiveXIDCutoff))
+ xact_age = (int32) (nextMXID - cutoffs->relfrozenxid);
+ if (xact_age > freeze_table_age)
return true;
/*
@@ -1221,8 +1227,9 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
aggressiveMXIDCutoff = nextMXID - multixact_freeze_table_age;
if (aggressiveMXIDCutoff < FirstMultiXactId)
aggressiveMXIDCutoff = FirstMultiXactId;
- if (MultiXactIdPrecedesOrEquals(rel->rd_rel->relminmxid,
- aggressiveMXIDCutoff))
+
+ multixact_age = (int32) (nextMXID - cutoffs->relminmxid);
+ if (multixact_age > multixact_freeze_table_age)
return true;
/* Non-aggressive VACUUM */
