The purpose of MultiXactMemberFreezeThreshold() is to make the effective
autovacuum_multixact_freeze_max_age smaller, if the multixact members
SLRU is approaching wraparound. Per comment there:
* To prevent that, if more than a threshold portion of the members space is
* used, we effectively reduce autovacuum_multixact_freeze_max_age and
* to a value just less than the number of multixacts in use. We hope that
* this will quickly trigger autovacuuming on the table or tables with the
* oldest relminmxid, thus allowing datminmxid values to advance and removing
* some members.
However, the value that the function calculates can sometimes be
*greater* than autovacuum_multixact_freeze_max_age. To get an overview
of how it behaves, I wrote the attached stand-alone C program to test it
with different inputs:
If members < MULTIXACT_MEMBER_SAFE_THRESHOLD, it just returns
autovacuum_multixact_freeze_max_age, which is 200 million by default:
multixacts: 1000000, members 1000000000 -> 200000000
multixacts: 1000000, members 2000000000 -> 200000000
multixacts: 1000000, members 2100000000 -> 200000000
Above MULTIXACT_MEMBER_SAFE_THRESHOLD, the members-based calculated
kicks in:
multixacts: 1000000, members 2200000000 -> 951091
multixacts: 1000000, members 2300000000 -> 857959
multixacts: 1000000, members 2500000000 -> 671694
multixacts: 1000000, members 3000000000 -> 206033
multixacts: 1000000, members 3100000000 -> 112901
multixacts: 1000000, members 3500000000 -> 0
multixacts: 1000000, members 4000000000 -> 0
However, if multixacts is also large the returned value is also quite large:
multixacts: 1000000000, members 2200000000 -> 951090335
That's larger than the default autovacuum_multixact_freeze_max_age! If
you had set it to a lower non-default value, it's even worse.
I noticed this after I used pg_resetwal to reset next-multixid and
next-mxoffset to a high value for testing purposes. Not sure how easy it
is to reach that situation normally. In any case, I think the function
should clamp the result to autovacuum_multixact_freeze_max_age, per
attached.
--
Heikki Linnakangas
Neon (https://neon.tech)
#include <stdint.h>
#include <stdio.h>
typedef uint32_t uint32;
typedef uint32 MultiXactOffset;
#define MaxMultiXactOffset ((MultiXactOffset) 0xFFFFFFFF)
#define MULTIXACT_MEMBER_SAFE_THRESHOLD (MaxMultiXactOffset / 2)
#define MULTIXACT_MEMBER_DANGER_THRESHOLD \
(MaxMultiXactOffset - MaxMultiXactOffset / 4)
#define autovacuum_multixact_freeze_max_age 200000000
static int
test_MultiXactMemberFreezeThreshold(uint32 multixacts, MultiXactOffset members)
{
uint32 victim_multixacts;
double fraction;
/* If member space utilization is low, no special action is required. */
if (members <= MULTIXACT_MEMBER_SAFE_THRESHOLD)
return autovacuum_multixact_freeze_max_age;
/*
* Compute a target for relminmxid advancement. The number of multixacts
* we try to eliminate from the system is based on how far we are past
* MULTIXACT_MEMBER_SAFE_THRESHOLD.
*/
fraction = (double) (members - MULTIXACT_MEMBER_SAFE_THRESHOLD) /
(MULTIXACT_MEMBER_DANGER_THRESHOLD - MULTIXACT_MEMBER_SAFE_THRESHOLD);
victim_multixacts = multixacts * fraction;
/* fraction could be > 1.0, but lowest possible freeze age is zero */
if (victim_multixacts > multixacts)
return 0;
return multixacts - victim_multixacts;
}
static int
testit(uint32 multixacts, MultiXactOffset members)
{
int result = test_MultiXactMemberFreezeThreshold(multixacts, members);
printf("multixacts: %10u, members %10u -> %10d\n", multixacts, members, result);
return result;
}
int main(int argc, char **argv)
{
testit( 1000000UL, 1000000000UL);
testit( 1000000UL, 2000000000UL);
testit( 1000000UL, 2100000000UL);
testit( 1000000UL, 2200000000UL);
testit( 1000000UL, 2300000000UL);
testit( 1000000UL, 2500000000UL);
testit( 1000000UL, 3000000000UL);
testit( 1000000UL, 3100000000UL);
testit( 1000000UL, 3500000000UL);
testit( 1000000UL, 4000000000UL);
testit(1000000000UL, 2200000000UL);
}
From 25c18f6918c064e36865e8969fda8300a2864eca Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <heikki.linnakan...@iki.fi>
Date: Wed, 12 Jun 2024 23:38:41 +0300
Subject: [PATCH 1/1] Clamp result of MultiXactMemberFreezeThreshold
The purpose of the function is to reduce the effective
autovacuum_multixact_freeze_max_age, to make multixid freezing more
aggressive, if the multixact members SLRU is approaching
wraparound. The returned value should thereore never be greater than
plain autovacuum_multixact_freeze_max_age is.
Discussion: xxx
---
src/backend/access/transam/multixact.c | 9 ++++++++-
1 file changed, 8 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 54c916e034..a4e732a3f5 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2932,6 +2932,7 @@ MultiXactMemberFreezeThreshold(void)
uint32 multixacts;
uint32 victim_multixacts;
double fraction;
+ int result;
/* If we can't determine member space utilization, assume the worst. */
if (!ReadMultiXactCounts(&multixacts, &members))
@@ -2953,7 +2954,13 @@ MultiXactMemberFreezeThreshold(void)
/* fraction could be > 1.0, but lowest possible freeze age is zero */
if (victim_multixacts > multixacts)
return 0;
- return multixacts - victim_multixacts;
+ result = multixacts - victim_multixacts;
+
+ /*
+ * Clamp to autovacuum_multixact_freeze_max_age, so that we never make
+ * autovacuum less aggressive than it would otherwise be.
+ */
+ return Min(result, autovacuum_multixact_freeze_max_age);
}
typedef struct mxtruncinfo
--
2.39.2
