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)
