MultiXactMemberFreezeThreshold can make autovacuum *less* aggressive

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

Responses

Browse pgsql-hackers by date

  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