Обсуждение: 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)

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

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)