On Tue, Mar 11, 2025 at 4:48 AM Kirill Reshke <reshkekir...@gmail.com> wrote: > > On Tue, 11 Mar 2025 at 14:37, Naga Appani <nagn...@gmail.com> wrote: > > > > > > > > On Mon, Mar 10, 2025 at 10:43 AM Naga Appani <nagn...@gmail.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. 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
v1-0001-Add-pg_get_multixact_count-function-and-related-d.patch
Description: Binary data
