public inbox for [email protected]
help / color / mirror / Atom feedFrom: Naga Appani <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: [email protected]
Subject: Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring
Date: Mon, 4 Aug 2025 16:51:30 -0500
Message-ID: <CA+QeY+D_npapLjsu0UZ-pgRGLwqx0GjAmRL_a+R9UOB7jgY=_Q@mail.gmail.com> (raw)
In-Reply-To: <CA+QeY+CFLjh0gb-j9p+0eJK=r19izhEPCBY3ns4+HCgOQojh8Q@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>
<CA+QeY+DTggHskCXOa39nag2sFds9BD-7k__zPbvL-_VVyJw7Sg@mail.gmail.com>
<CAExHW5uxBnodmpXygUQUxZAyxYvbXczadxGOVeL=0DxpkXUR-g@mail.gmail.com>
<[email protected]>
<CAExHW5vba_ecL2c27giAOOaRxoNdVX2BNQVCN4PFOZJC17NrqQ@mail.gmail.com>
<CA+QeY+CFLjh0gb-j9p+0eJK=r19izhEPCBY3ns4+HCgOQojh8Q@mail.gmail.com>
On Mon, Aug 4, 2025 at 1:16 AM Naga Appani <[email protected]> wrote:
>
> Hi Ashutosh, Michael,
>
> Thanks for the detailed reviews. I have incorporated the feedback;
> please find attached v2 and my responses inline below.
>
> On Fri, Jul 25, 2025 at 5:57 AM Ashutosh Bapat
> <[email protected]> wrote:
>
> > 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.
>
> Done -- the function is now documented under “Transaction ID and
> Snapshot Information Functions” for consistency.
>
> > The description here doesn't follow the format of the other functions
> > in this section.
>
> Updated the description in func.sgml to match the style of other
> functions. Extended usage guidance is now in maintenance.sgml.
>
> > Throwing an error causes the surrounding transaction to abort, so it
> > should be avoided in a monitoring/reporting function if possible.
>
> The function now returns NULL instead of throwing an error when counts
> can’t be read.
>
> > If ReadMultiXactCounts() returns false, MultiXactMemberFreezeThreshold() returns 0...
>
> Noted -- the docs now mention that the function can be used to
> anticipate more aggressive autovacuum behavior in such cases.
>
> > 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?
>
> Correct, that is the intent of the function. The members count
> returned by this function already provides the necessary information
> to determine the directory size, since each member entry has a fixed
> size. The constants and formulas in [0] and discussed in [1] show that
> each group stores four bytes of flags plus four TransactionIds (20
> bytes total), yielding 409 groups per 8 KB page and a fixed
> members‑to‑bytes ratio. This means ~2 billion members corresponds to
> ~10 GB (aggressive autovacuum threshold) and ~4 billion members
> corresponds to ~20 GB (wraparound).
>
> Since the function already provides the member count, including the
> physical size in its output would duplicate information and add no
> extra benefit.
>
> > The patch needs tests.
>
> Added an isolation test to cover initial state, MultiXact creation,
> counts, and oldest MultiXact reporting.
>
> On Mon, Jul 28, 2025 at 1:00 AM Ashutosh Bapat
> <[email protected]> wrote:
>
> > Let's say if the user knows that the counts are so high that a
> > wraparound is imminent, but vacuuming isn't solving the problem...
> > Here's a quick patch implementing the same. Please feel free to
> > incorporate and refine it in your patch if you like it.
>
> Thank you for sharing the patch. I have incorporated it into this
> version with minor adjustments, and it fits well with the overall
> design of the function.
>
> On Mon, Jul 28, 2025 at 4:22 AM Michael Paquier <[email protected]> wrote:
>
> > Yep, let's be consistent.
>
> Done -- placed in “Transaction ID and Snapshot Information Functions”
> for consistency.
>
> > Most likely returning NULL is the best thing we can do, as a safe fallback.
>
> Implemented -- the function now returns NULL if counts can’t be read.
>
> > The patch needs tests.
>
> Isolation tests have been added as described above.
>
> > May I also suggest a split of the multixact SQL functions into a
> > separate file, a src/backend/utils/adt/multixactfuncs.c?
>
> I agree that would be cleaner, but I’d prefer to keep the
> implementation in multixact.c for now to maintain focus on this patch
> and revisit the refactoring later.
>
> > +PG_FUNCTION_INFO_V1(pg_get_multixact_count);
>
> Removed -- now handled entirely by pg_proc.dat.
>
> > ...You may want to add an example of how one can use it for monitoring in the docs.
>
> I’ve added a usage example with sample output in the docs. If you had
> a different kind of demo in mind (e.g., creating multixacts manually
> and showing the output), please let me know.
>
> References:
> [0] https://github.com/postgres/postgres/blob/master/src/backend/access/transam/multixact.c#L130-L156
> [1] https://www.postgresql.org/message-id/flat/CACbFw60UOk6fCC02KsyT3OfU9Dnuq5roYxdw2aFisiN_p1L0bg%40mai...
>
> Best regards,
> Naga Appani
Following up on my v2 from yesterday — the recent commit [0] changed
the directory layout, which broke the patch (v2). This v3 updates the
code to work with the new structure and also fixes some formatting
issues I noticed while revisiting the changes.
The rest of the patch remains the same as v2, which incorporated
feedback from Ashutosh and Michael (see my previous email for
details).
Please find v3 attached.
References:
[0] https://github.com/postgres/postgres/commit/4e23c9ef65accde7eb3e56aa28d50ae5cf79b64b
Best regards,
Naga Appani
Attachments:
[application/octet-stream] v3-0001-Add-pg_get_multixact_stats-SQL-function-for-monit.patch (12.6K, 2-v3-0001-Add-pg_get_multixact_stats-SQL-function-for-monit.patch)
download | inline diff:
From 5b3341fa9759f131c5e368b6debcb8f208c96a12 Mon Sep 17 00:00:00 2001
From: Naga Appani <[email protected]>
Date: Mon, 4 Aug 2025 21:02:26 +0000
Subject: [PATCH v2] Add pg_get_multixact_stats() SQL function for monitoring
multixact usage
This patch adds pg_get_multixact_stats(), a SQL-callable function that returns
multixact statistics to aid in monitoring wraparound risk and vacuum behavior.
It reports:
multixacts: the number of multixact IDs created since the oldest one still needed
members: the number of multixact member entries that currently exist
oldest_multixact: the oldest multixact ID still needed by any database
The function modifies ReadMultiXactCounts() to expose the oldestMultiXactId and
returns all three values in a composite record. This allows users to monitor
multixact usage and identify potential wraparound issues, particularly useful
when combined with pg_get_multixact_members() to investigate specific multixacts.
Usage:
SELECT * FROM pg_get_multixact_stats();
Documentation is added to:
- "Transaction ID and Snapshot Information Functions" section in func.sgml
- "Multixacts and Wraparound" section in maintenance.sgml
(routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND)
Isolation tests are added to verify:
- Initial state with zero multixacts
- multixact creation with overlapping shared locks
- Correct counting of multixacts and members
- Proper tracking of oldest multixact ID
Author: Naga Appani <[email protected]>
Reviewed-by: Ashutosh Bapat <[email protected]>
Reviewed-by: Michael Paquier <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAM2BeoX%2BRasKfG6W8w4qYZZz4BnhyEQMA_y5cEDnKEY_z8o9Czg%40mail.gmail.com
---
doc/src/sgml/func/func-info.sgml | 32 ++++++++++
doc/src/sgml/maintenance.sgml | 37 ++++++++++--
src/backend/access/transam/multixact.c | 50 ++++++++++++++--
src/include/catalog/pg_proc.dat | 15 +++++
.../isolation/expected/multixact_stats.out | 59 +++++++++++++++++++
src/test/isolation/specs/multixact_stats.spec | 35 +++++++++++
6 files changed, 219 insertions(+), 9 deletions(-)
create mode 100644 src/test/isolation/expected/multixact_stats.out
create mode 100644 src/test/isolation/specs/multixact_stats.spec
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index b507bfaf64b..75ce2c6e403 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2967,6 +2967,38 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
modify key columns.
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <indexterm><primary>pg_get_multixact_stats</primary></indexterm>
+ <function>pg_get_multixact_stats</function> ()
+ <returnvalue>record</returnvalue>
+ ( <parameter>multixacts</parameter> <type>integer</type>,
+ <parameter>members</parameter> <type>bigint</type>,
+ <parameter>oldest_multixact</parameter> <type>integer</type> )
+ </para>
+
+ <para>
+ Returns statistics about current multixact usage:
+ <literal>multixacts</literal> is the number of multixact IDs assigned,
+ <literal>members</literal> is the number of multixact member entries created,
+ and <literal>oldest_multixact</literal> is the oldest MultiXact ID still in use.
+ These values can be used to monitor multixact consumption and anticipate
+ autovacuum behavior. See <xref linkend="vacuum-for-multixact-wraparound"/>
+ for further details on multixact wraparound.
+ </para>
+
+ <para>
+ <literal>SELECT * FROM pg_get_multixact_stats();</literal>
+<programlisting>
+ multixacts | members | oldest_multixact
+------------+-------------+------------------
+ 182371396 | 2826221174 | 754321
+</programlisting>
+ </para>
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..d6bd305b0b0 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,12 +813,41 @@ HINT: Execute a database-wide VACUUM in that database.
<para>
As a safety device, an aggressive vacuum scan will
occur for any table whose multixact-age is greater than <xref
- linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the
- storage occupied by multixacts members exceeds about 10GB, aggressive vacuum
+ linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if either
+ the storage occupied by multixact members exceeds about 10GB or the number
+ of members created exceeds approximately 2 billion entries, aggressive vacuum
scans will occur more often for all tables, starting with those that
- have the oldest multixact-age. Both of these kinds of aggressive
+ have the oldest multixact-age. Both of these kinds of aggressive
scans will occur even if autovacuum is nominally disabled. The members storage
- area can grow up to about 20GB before reaching wraparound.
+ area can grow up to about 20GB or approximately 4 billion entries before
+ reaching wraparound.
+ </para>
+
+ <para>
+ The <function>pg_get_multixact_stats()</function> function provides a way
+ to monitor multixact allocation and usage patterns in real time. By exposing
+ counts of multixacts, member entries, and the oldest multixact ID, it helps:
+ <orderedlist>
+ <listitem>
+ <simpara>
+ Identify unusual multixact activity from concurrent row-level locks
+ or foreign key operations
+ </simpara>
+ </listitem>
+ <listitem>
+ <simpara>
+ Monitor progress toward wraparound thresholds that trigger aggressive
+ autovacuum (approximately 2 billion members or 10GB storage)
+ </simpara>
+ </listitem>
+ <listitem>
+ <simpara>
+ Verify whether autovacuum is effectively managing multixact cleanup
+ before reaching critical thresholds
+ </simpara>
+ </listitem>
+ </orderedlist>
+ See <xref linkend="functions-info-snapshot"/> for details.
</para>
<para>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 3cb09c3d598..ec7ff959416 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2863,17 +2863,16 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
* exist. Return false if unable to determine.
*/
static bool
-ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
+ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members, MultiXactId *oldestMultiXactId)
{
MultiXactOffset nextOffset;
MultiXactOffset oldestOffset;
- MultiXactId oldestMultiXactId;
MultiXactId nextMultiXactId;
bool oldestOffsetKnown;
LWLockAcquire(MultiXactGenLock, LW_SHARED);
nextOffset = MultiXactState->nextOffset;
- oldestMultiXactId = MultiXactState->oldestMultiXactId;
+ *oldestMultiXactId = MultiXactState->oldestMultiXactId;
nextMultiXactId = MultiXactState->nextMXact;
oldestOffset = MultiXactState->oldestOffset;
oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
@@ -2883,7 +2882,7 @@ ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
return false;
*members = nextOffset - oldestOffset;
- *multixacts = nextMultiXactId - oldestMultiXactId;
+ *multixacts = nextMultiXactId - *oldestMultiXactId;
return true;
}
@@ -2922,9 +2921,10 @@ MultiXactMemberFreezeThreshold(void)
uint32 victim_multixacts;
double fraction;
int result;
+ MultiXactId oldestMultiXactId;
/* If we can't determine member space utilization, assume the worst. */
- if (!ReadMultiXactCounts(&multixacts, &members))
+ if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
return 0;
/* If member space utilization is low, no special action is required. */
@@ -3493,3 +3493,43 @@ multixactmemberssyncfiletag(const FileTag *ftag, char *path)
{
return SlruSyncFileTag(MultiXactMemberCtl, ftag, path);
}
+
+/*
+ * pg_get_multixact_stats
+ *
+ * SQL-callable function to retrieve MultiXact statistics.
+ *
+ * Returns a composite row containing:
+ * - total number of MultiXact IDs created since startup,
+ * - total number of MultiXact members created,
+ * - the oldest existing MultiXact ID.
+ *
+ * This is primarily useful for monitoring MultiXact usage and ensuring
+ * appropriate wraparound protection.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+ TupleDesc tupdesc;
+ Datum values[3];
+ bool nulls[3] = {false, false, false};
+ MultiXactOffset members;
+ MultiXactId oldestMultiXactId;
+ 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, &oldestMultiXactId))
+ PG_RETURN_NULL();
+
+ values[0] = UInt32GetDatum(multixacts);
+ values[1] = UInt32GetDatum(members);
+ values[2] = UInt32GetDatum(oldestMultiXactId);
+
+ 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 118d6da1ace..9d9e28c2770 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12576,4 +12576,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' },
+# MultiXact stats
+{
+ oid => '9001',
+ descr => 'get current multixact member and multixact ID counts and oldest multixact',
+ proname => 'pg_get_multixact_stats',
+ prorettype => 'record',
+ proargtypes => '',
+ proallargtypes => '{int4,int8,int4}',
+ proargmodes => '{o,o,o}',
+ proargnames => '{multixacts,members,oldest_multixact}',
+ provolatile => 'v',
+ proparallel => 's',
+ prosrc => 'pg_get_multixact_stats'
+},
+
]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..54e3238d727
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,59 @@
+Parsed test spec with 3 sessions
+
+starting permutation: stats_init check begin1 lock1 begin2 lock2 check commit1 commit2 check
+step stats_init:
+ CREATE TEMP TABLE stats_before AS
+ SELECT multixacts, members, oldest_multixact FROM pg_get_multixact_stats();
+
+step check:
+ SELECT
+ multixacts,
+ members,
+ oldest_multixact
+ FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+ 0| 0| 1
+(1 row)
+
+step begin1: BEGIN;
+step lock1: SELECT * FROM multixact_test WHERE id = 1 FOR SHARE;
+id|val
+--+---
+ 1| 10
+(1 row)
+
+step begin2: BEGIN;
+step lock2: SELECT * FROM multixact_test WHERE id = 1 FOR SHARE;
+id|val
+--+---
+ 1| 10
+(1 row)
+
+step check:
+ SELECT
+ multixacts,
+ members,
+ oldest_multixact
+ FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+ 1| 3| 1
+(1 row)
+
+step commit1: COMMIT;
+step commit2: COMMIT;
+step check:
+ SELECT
+ multixacts,
+ members,
+ oldest_multixact
+ FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+ 1| 3| 1
+(1 row)
+
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..53fcad38c54
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,35 @@
+setup
+{
+ CREATE TABLE multixact_test(id int PRIMARY KEY, val int);
+ INSERT INTO multixact_test VALUES (1, 10);
+}
+
+teardown
+{
+ DROP TABLE multixact_test;
+}
+
+session s1
+step begin1 { BEGIN; }
+step lock1 { SELECT * FROM multixact_test WHERE id = 1 FOR SHARE; }
+step commit1 { COMMIT; }
+
+session s2
+step begin2 { BEGIN; }
+step lock2 { SELECT * FROM multixact_test WHERE id = 1 FOR SHARE; }
+step commit2 { COMMIT; }
+
+session s3
+step stats_init {
+ CREATE TEMP TABLE stats_before AS
+ SELECT multixacts, members, oldest_multixact FROM pg_get_multixact_stats();
+}
+step check {
+ SELECT
+ multixacts,
+ members,
+ oldest_multixact
+ FROM pg_get_multixact_stats();
+}
+
+permutation stats_init check begin1 lock1 begin2 lock2 check commit1 commit2 check
--
2.47.3
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], [email protected], [email protected]
Subject: Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring
In-Reply-To: <CA+QeY+D_npapLjsu0UZ-pgRGLwqx0GjAmRL_a+R9UOB7jgY=_Q@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