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

On Tue, 11 Mar 2025 at 14:37, Naga Appani <nagnrik@gmail.com> wrote:
>
>
>
> On Mon, Mar 10, 2025 at 10:43 AM Naga Appani <nagnrik@gmail.com> wrote:
>>
>> Hi,
>>

Hi

> =================
> Proposal
> =================
> The internal ReadMultiXactCounts() function, implemented in multixact.c, directly calculates the number of MultiXact
membersby reading live state from shared memory. This approach avoids the performance issues of the current
filesystem-basedestimation 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
MultiXactmember usage. This could be particularly useful for integrating with tools like Amazon RDS Performance
Insightsand 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!



--
Best regards,
Kirill Reshke

On Tue, Mar 11, 2025 at 4:48 AM Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> On Tue, 11 Mar 2025 at 14:37, Naga Appani <nagnrik@gmail.com> wrote:
> >
> >
> >
> > On Mon, Mar 10, 2025 at 10:43 AM Naga Appani <nagnrik@gmail.com> wrote:
> >>
> >> Hi,
> >>
>
> Hi
>
> > =================
> > Proposal
> > =================
> > The internal ReadMultiXactCounts() function, implemented in multixact.c, directly calculates the number of
MultiXactmembers by reading live state from shared memory. This approach avoids the performance issues of the current
filesystem-basedestimation 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
monitorMultiXact member usage. This could be particularly useful for integrating with tools like Amazon RDS Performance
Insightsand 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. The only
workaround today involves scanning the pg_multixact/members directory
on disk, current workaround uses stat() calls over potentially
millions of small segment files, adds I/O overhead, and is unsuitable
for periodic monitoring or integration into observability platforms.

Unlike the approach originally proposed or discussed in this thread,
this patch does not expose the internal ReadMultiXactCounts() function
directly. Instead, it wraps it internally (without changing its
visibility) to make the data available via a new SQL function.

This patch adds:

    pg_get_multixact_count()

It returns a composite of:
    - multixacts: number of MultiXact IDs that currently exist
    - members:    number of MultiXact member entries currently exist

Implementation
--------------
- Defined in multixact.c
- Calls ReadMultiXactCounts()
- Returns a composite record (multixacts, members)
- Includes documentation

Use cases
---------
This function enables users to:
- Monitor member usage to anticipate aggressive vacuum and avoid wraparound risk
- Track long-lived workloads that accumulate MultiXacts
- Power lightweight monitoring/diagnostics tools without scanning the filesystem
- Log and analyze MultiXact growth over time

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.

This patch aims to fill a long-standing observability gap.

Patch attached.

Best regards,
Naga Appani
Postgres Database Engineer
Amazon Web Services

>
>
>
> --
> Best regards,
> Kirill Reshke