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

On Tue, Jun 10, 2025 at 7:50 PM Naga Appani <nagnrik(at)gmail(dot)com> wrote:
>
> On Tue, Mar 11, 2025 at 4:48 AM Kirill Reshke <reshkekirill(at)gmail(dot)com> wrote:
> >
> > On Tue, 11 Mar 2025 at 14:37, Naga Appani <nagnrik(at)gmail(dot)com> wrote:
> > >
> > >
> > >
> > > On Mon, Mar 10, 2025 at 10:43 AM Naga Appani <nagnrik(at)gmail(dot)com> wrote:
> > >>
> > >> Hi,
> > >>
> >
> > Hi
> >
> > > =================
> > > Proposal
> > > =================
> > > The internal ReadMultiXactCounts() function, implemented in multixact.c, directly calculates the number of MultiXact members by reading live state from shared memory. This approach avoids the performance issues of the current filesystem-based estimation methods.
> >
> > This proposal looks sane. It is indeed helpful to keep an eye out for
> > multixact usage in systems that are heavily loaded.
> >
> > > By exposing ReadMultiXactCounts() for external use, we can provide PostgreSQL users with an efficient way to monitor MultiXact member usage. This could be particularly useful for integrating with tools like Amazon RDS Performance Insights and Amazon CloudWatch to provide enhanced database insights and proactive managed monitoring for users.
> > >
> > > Please let me know if this approach is acceptable, so I’ll go ahead and submit a patch.
> >
> > Let's give it a try!
>
> Hi,
>
> As a follow-up, I’m submitting a patch that introduces a SQL-callable
> function to retrieve MultiXact usage metrics. Although the motivation
> has been discussed earlier in this thread, I’m including a brief recap
> below to provide context for the patch itself.
>
> While wraparound due to MultiXacts (MXID) is less frequent than XID
> wraparound, it can still lead to aggressive/wraparound vacuum behavior
> or downtime in certain workloads — especially those involving foreign
> keys, shared row locks, or long-lived transactions. Currently, users
> have no SQL-level visibility into MultiXact member consumption, which
> makes it hard to proactively respond before issues arise.

I see mxid_age() will just give mxid consumption but not members
consumption. So just that function is not enough.

>
> Sample output
> -------------
> multixacts | members
> ------------+------------
> 182371396 | 2826221174
> (1 row)
>
> Performance comparison
> ----------------------
> While performance is not the primary motivation for this patch, it
> becomes important in monitoring scenarios where frequent polling is
> expected. The proposed function executes in sub-millisecond time and
> avoids any filesystem I/O, making it well-suited for lightweight,
> periodic monitoring.
>
> Implementation | Used size | MultiXact members
> | Time (ms) | Relative cost
> -------------------------------------+-----------+-------------------+-----------+----------------
> Community (pg_ls_multixactdir) | 8642 MB | 1.8 billion |
> 96.879 | 1.00 (baseline)
> Linux (du command) | 8642 MB | 1.8 billion |
> 96 | 1.00
> Proposal (ReadMultiXactCounts-based) | N/A | 1.99 billion |
> 0.167 | ~580x faster
>
> Documentation
> -------------
> - A new section is added to func.sgml to group multixact-related functions
> - A reference to this new function is included in the "Multixacts and
> Wraparound" subsection of maintenance.sgml
>
> To keep related functions grouped together, we can consider moving
> mxid_age() into the new section as well unless there are objections to
> relocating it from the current section.

In [1], we decided to document pg_get_multixact_member() in section
"Transaction ID and Snapshot Information Functions". I think the
discussion in the email thread applies to this function as well.

+ <sect2 id="functions-info-multixact-information">
+ <title>MultiXact Information Functions</title>
+

+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <indexterm><primary>pg_get_multixact_count</primary></indexterm>
+ <function>pg_get_multixact_count</function> ()
+ <returnvalue>record</returnvalue>
+ </para>
+ <para>
+ Returns a record with the fields
<structfield>multixacts</structfield> and
<structfield>members</structfield>:
+ <itemizedlist>
+ <listitem>
+ <para><structfield>multixacts</structfield>: Number of
MultiXacts assigned.
+ PostgreSQL initiates aggressive autovacuum when this
value grows beyond the threshold
+ defined by
<varname>autovacuum_multixact_freeze_max_age</varname>, which is based
on
+ the age of <literal>datminmxid</literal>. For more details, see
+ <ulink
url="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND">
+ Routine Vacuuming: Multixact Wraparound</ulink>.</para>
+ </listitem>
+ <listitem>
+ <para><structfield>members</structfield>: Number of
MultiXact member entries created.
+ These are stored in files under the
<filename>pg_multixact/members</filename> subdirectory.
+ Wraparound occurs after approximately 4.29 billion
entries (~20 GiB). PostgreSQL initiates
+ aggressive autovacuum when the number of members created
exceeds approximately 2.145 billion
+ or when storage consumption in
<filename>pg_multixact/members</filename> approaches 10 GiB.</para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </entry>

The description here doesn't follow the format of the other functions
in this section. We usually explain the inputs and outputs of the
function but not how to use the outputs. In this case, you might want
to just refer to Multixact Wraparound section under Routine Vacuuming
chapter rather than describing the autovacuum behaviour. You can do
that by inserting <xref linkend="vacuum-for-multixact-wraparound"/>
instead of a full URL. These links are appropriately resolved when
creating HTML to version specific links. The URL you have used will
always point to "Current" version.

+ <para>
+ The <function><link
linkend="functions-multixact-information">pg_get_multixact_count</link></function>
+ function provides a way to check how many multixacts and member
entries have been allocated. This can
+ be useful for identifying unusual multixact activity, monitoring
progress toward wraparound, anticipating
+ system-wide aggressive autovacuum as usage approaches critical
thresholds, or verifying whether autovacuum
+ is keeping up with demand.
+ </para>
+

This is the right place to go in details of how the function can be
used; not the function documentation itself. I am yet to make up
whether we need the whole description. I think the first line is
enough and goes well with the rest of the section.

+
+ if (!ReadMultiXactCounts(&multixacts, &members))
+ ereport(ERROR,
+ (errmsg("could not read multixact counts")));

Throwing an error causes the surrounding transaction to abort, so it
should be avoided in a monitoring/reporting function if possible. In
this case for example, we could throw a warning instead or report NULL
values.

If ReadMultiXactCounts() returns false,
MultiXactMemberFreezeThreshold() returns 0, which will cause the
autovacuum to be more aggressive. I think it will be good to highlight
that in the function description since that's one of the objectives of
this function: to know when the autovacuum is going to be more
aggressive.

+
+ values[0] = UInt32GetDatum(multixacts);
+ values[1] = UInt32GetDatum(members);
+
+ tuple = heap_form_tuple(tupdesc, values, nulls);
+ PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}

In PG14+, the transaction wraparound is triggered if the size of the
directory exceeds 10GB. This function does not help monitoring that
condition. So a user will need to use du or pg_ls_multixactdir()
anyway, which defeats the purpose of this function being more
efficient than those methods. Am I correct? Can we also report the
size of the directory in this function?

The patch needs tests.

[1] https://www.postgresql.org/message-id/aF8b_fp_9Va58vB9%40nathan

--
Best Wishes,
Ashutosh Bapat