RE: Monitoring multixact members growth

From: Vido Vlahinic <Vido(dot)Vlahinic(at)milestonegroup(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Cc: Steve Rogers <Steve(dot)Rogers(at)milestonegroup(dot)com>, Graham Lynch <Graham(dot)Lynch(at)milestonegroup(dot)com>
Subject: RE: Monitoring multixact members growth
Date: 2022-08-19 07:52:15
Message-ID: SYYP282MB1294AC64BADA3A4A3B8636C7806C9@SYYP282MB1294.AUSP282.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello everyone,

Is there a way to monitor multixact members growth per table ?

There is this query:

SELECT relname, relminmxid, mxid_age(relminmxid) AS age
FROM pg_class
WHERE relkind = 'r'
AND relnamespace::regnamespace::text = 'public'
ORDER BY 3 DESC

However it is not quite clear to me how I can interpret results from the above and e.g. conclude: my_table accumulates x more multixact members since I measured it last.

My goal here is to predict where multixact members are growing the fastest so I can perform manual VACUUM FREEZE only on those tables
(typically with multi-billion row count) when system is relatively idle as opposed to just sit and wait for wraparound protection to take over
when autovacuum_multixact_freeze_max_age threshold is reached (slowing the whole system down).

Please advise on how to achieve this.

Best regards,
Vido

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Giovanni Biscontini 2022-08-19 14:54:58 recovery_command has precedence over phisical slots?
Previous Message gzh 2022-08-19 07:14:43 Re:Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5