Thread

Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring

Michael Paquier <michael@paquier.xyz> — 2025-12-25T00:45:33Z

On Wed, Dec 24, 2025 at 06:09:14PM -0600, Naga Appani wrote:
> I’ve updated the patch as suggested.
> 
> The member storage size calculation has been refactored into a static
> inline function, MultiXactMemberStorageSize(), in
> src/include/access/multixact_internal.h.
> 
> Please find v13 attached.

Seems basically sensible here for the structure, including the hints
and recommendations for the GUCs.

+     of multixact member entries created exceeds approximately 2^31 entries
[...]
+     This output shows a system with significant multixact activity: about ~312 million
+     multixact IDs and ~2.8 billion member entries consuming 13 GB of storage space.

The documentation could be improved more.  The power '^' and tilde
symbols are not used for references.  If any, I'd encourage using
wordings like "2 billion" entries for all these paragraphs across the
board.  For the tilde part, you would mean "at least" or "at most"
rather than the boundaries implied with the tilde (aka we should not
expect the reader the mental effort to translate and  understand what
these symbols mean, especially for non-native English speaker).

+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.

Saying that, this paragraph does not seem that useful to me,
especially the last sentence which is evasive and can apply to
anything related to monitoring.

The second hint is more useful, but perhaps we should mention which
GUC(s) should be touched to make num_members go lower?  As a whole,
the orderedlist does not seem strongly necessary to me: the third
item is evasive, the first and second items describe problematic
patterns and what could cause them.  As a whole, for the docs
part, the new additions in the existing paragraph of maintenance.sgml
are OK for me.  The first part of the new paragraph added also
provides some direct information about how useful this new function is
to evaluate the amount of disk space used.  I'd like to think that we
should just complete it the two facts about num_mxids and num_members
you are listing, with two sentences appended at the end of the new
paragraph rather than a list of items.

If we don't completely agree about the "hint" part, we could split the
patch in two for now: let's add the function first, then discuss more
about what kind of tweaks and patterns we want to document as a set of
follow-up changes.  It does not change the fact that the function is
useful for disk-space monitoring purposes.  The patterns and hints are
a second different matter.
--
Michael