MultiXactMemberFreezeThreshold can make autovacuum *less* aggressive
От | Heikki Linnakangas |
---|---|
Тема | MultiXactMemberFreezeThreshold can make autovacuum *less* aggressive |
Дата | |
Msg-id | 85fb354c-f89f-4d47-b3a2-3cbd461c90a3@iki.fi обсуждение исходный текст |
Ответы |
Re: MultiXactMemberFreezeThreshold can make autovacuum *less* aggressive
|
Список | 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)
Вложения
В списке pgsql-hackers по дате отправления: