| 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: | Whole Thread | Raw Message | 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 |