Thread: MultiXactMemberFreezeThreshold can make autovacuum *less* aggressive
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
On Thu, Jun 13, 2024 at 8:29 AM Heikki Linnakangas <hlinnaka@iki.fi> wrote: > However, the value that the function calculates can sometimes be > *greater* than autovacuum_multixact_freeze_max_age. That was definitely not what I intended and is definitely bad. > In any case, I think the function > should clamp the result to autovacuum_multixact_freeze_max_age, per > attached. LGTM. -- Robert Haas EDB: http://www.enterprisedb.com
Re: MultiXactMemberFreezeThreshold can make autovacuum *less* aggressive
From
Heikki Linnakangas
Date:
On 13/06/2024 17:40, Robert Haas wrote: > On Thu, Jun 13, 2024 at 8:29 AM Heikki Linnakangas <hlinnaka@iki.fi> wrote: >> However, the value that the function calculates can sometimes be >> *greater* than autovacuum_multixact_freeze_max_age. > > That was definitely not what I intended and is definitely bad. > >> In any case, I think the function >> should clamp the result to autovacuum_multixact_freeze_max_age, per >> attached. > > LGTM. Committed and backpatched to all supported versions. Thanks! -- Heikki Linnakangas Neon (https://neon.tech)