public inbox for [email protected]
help / color / mirror / Atom feedFrom: Naga Appani <[email protected]>
To: Kirill Reshke <[email protected]>
Cc: [email protected]
Subject: Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring
Date: Mon, 9 Jun 2025 16:52:15 -0500
Message-ID: <CA+QeY+DTggHskCXOa39nag2sFds9BD-7k__zPbvL-_VVyJw7Sg@mail.gmail.com> (raw)
In-Reply-To: <CALdSSPi3Gh08NtcCn44uVeUAYGOT74sU6uei_06qUTa5rMK43g@mail.gmail.com>
References: <CA+QeY+AAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg@mail.gmail.com>
<CA+QeY+CuTcKSFmw3H-_OtCv+bv0T4uTSFgaAY1N9ssL-WaiiSA@mail.gmail.com>
<CALdSSPi3Gh08NtcCn44uVeUAYGOT74sU6uei_06qUTa5rMK43g@mail.gmail.com>
On Tue, Mar 11, 2025 at 4:48 AM Kirill Reshke <[email protected]> wrote:
>
> On Tue, 11 Mar 2025 at 14:37, Naga Appani <[email protected]> wrote:
> >
> >
> >
> > On Mon, Mar 10, 2025 at 10:43 AM Naga Appani <[email protected]> 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
Attachments:
[application/octet-stream] v1-0001-Add-pg_get_multixact_count-function-and-related-d.patch (7.5K, 2-v1-0001-Add-pg_get_multixact_count-function-and-related-d.patch)
download | inline diff:
From 0f9bff594eccf2f7aea288e73f8c147edf884857 Mon Sep 17 00:00:00 2001
From: Naga Appani <[email protected]>
Date: Fri, 6 Jun 2025 05:18:15 +0000
Subject: [PATCH v1] Add pg_get_multixact_count function and related
Add pg_get_multixact_count() SQL function for monitoring MultiXact usage
PostgreSQL exposes mxid_age() to track MultiXact ID wraparound risk,
but there is currently no SQL-accessible way to monitor MultiXact member
consumption, which can independently trigger aggressive vacuuming or
wraparound protection. The only workaround today involves scanning the
pg_multixact/members directory, which is I/O intensive and unsuitable
for monitoring tools.
This patch adds pg_get_multixact_count(), a SQL-callable function that
returns a composite record with two fields:
- multixacts: number of MultiXact IDs that currently exist
- members: number of MultiXact member entries that currently exist
The function calls ReadMultiXactCounts() and returns the values using the
standard record output convention.
Documentation has been added to func.sgml under a new section titled
"MultiXact Information Functions", with a cross-reference in maintenance.sgml
to help users track MultiXact usage relative to autovacuum thresholds.
Author: Naga Appani <[email protected]>
Reviewed-by:
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
doc/src/sgml/func.sgml | 58 ++++++++++++++++++++++++++
doc/src/sgml/maintenance.sgml | 8 ++++
src/backend/access/transam/multixact.c | 30 +++++++++++++
src/include/catalog/pg_proc.dat | 15 +++++++
4 files changed, 111 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f..bdb64f83c23 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28496,6 +28496,64 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-info-multixact-information">
+ <title>MultiXact Information Functions</title>
+
+ <para>
+ The function shown in <xref linkend="functions-multixact-information"/>
+ exposes internal MultiXact counters used by
+ <productname>PostgreSQL</productname>'s locking and transaction management subsystems.
+ It is primarily intended for monitoring and diagnostic purposes, such as analyzing
+ MultiXact consumption patterns or anticipating wraparound-related maintenance.
+ </para>
+
+ <table id="functions-multixact-information">
+ <title>MultiXact Information Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">Function</para>
+ <para>Description</para>
+ </entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <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>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
</sect1>
<sect1 id="functions-admin">
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 600e4b3f2f3..a445d1b061c 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -818,6 +818,14 @@ HINT: Execute a database-wide VACUUM in that database.
area can grow up to about 20GB before reaching wraparound.
</para>
+ <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>
+
<para>
Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 3c06ac45532..ed29746eaa9 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -3585,3 +3585,33 @@ multixactmemberssyncfiletag(const FileTag *ftag, char *path)
{
return SlruSyncFileTag(MultiXactMemberCtl, ftag, path);
}
+
+/*
+ * Returns the current count of multixact members and multixact IDs
+ */
+PG_FUNCTION_INFO_V1(pg_get_multixact_count);
+
+Datum
+pg_get_multixact_count(PG_FUNCTION_ARGS)
+{
+ TupleDesc tupdesc;
+ Datum values[2];
+ bool nulls[2] = {false, false};
+ MultiXactOffset members;
+ uint32 multixacts;
+ HeapTuple tuple;
+
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ ereport(ERROR,
+ (errmsg("return type must be a row type")));
+
+ if (!ReadMultiXactCounts(&multixacts, &members))
+ ereport(ERROR,
+ (errmsg("could not read multixact counts")));
+
+ values[0] = UInt32GetDatum(multixacts);
+ values[1] = UInt32GetDatum(members);
+
+ tuple = heap_form_tuple(tupdesc, values, nulls);
+ PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d3d28a263fa..09115ad1b35 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12556,4 +12556,19 @@
proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
prosrc => 'pg_get_aios' },
+# Returns current counts of multixact members and multixact IDs
+{
+ oid => '9001',
+ descr => 'get current multixact member and multixact ID counts',
+ proname => 'pg_get_multixact_count',
+ prorettype => 'record',
+ proargtypes => '',
+ proallargtypes => '{int4,int8}',
+ proargmodes => '{o,o}',
+ proargnames => '{multixacts,members}',
+ provolatile => 'v',
+ proparallel => 's',
+ prosrc => 'pg_get_multixact_count'
+},
+
]
--
2.47.1
view thread (42+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring
In-Reply-To: <CA+QeY+DTggHskCXOa39nag2sFds9BD-7k__zPbvL-_VVyJw7Sg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox