From: | Heikki Linnakangas <hlinnaka(at)iki(dot)fi> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | MultiXactMemberFreezeThreshold can make autovacuum *less* aggressive |
Date: | 2024-06-13 12:28:57 |
Message-ID: | 85fb354c-f89f-4d47-b3a2-3cbd461c90a3@iki.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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)
Attachment | Content-Type | Size |
---|---|---|
test-MultiXactMemberFreezeThreshold-inputs.c | text/x-csrc | 1.9 KB |
0001-Clamp-result-of-MultiXactMemberFreezeThreshold.patch | text/x-patch | 1.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Masahiko Sawada | 2024-06-13 12:44:05 | Re: Logical Replication of sequences |
Previous Message | Bertrand Drouvot | 2024-06-13 11:56:26 | Re: Track the amount of time waiting due to cost_delay |