On Thu, Dec 25, 2025 at 10:30:37AM +0900, Michael Paquier wrote: > I am wondering if it would not be cleaner and less confusing to do > things slightly differently (sorry I did not pay much attention to > that previously): > - Change GetMultiXactInfo() to return two offsets, nextOffset and > oldestOffset. > - Use uint64 for members and recalculate the difference in > MultiXactMemberFreezeThreshold() and the function code. Heikki has > just switched multixact offsets to be 64 bits, yippee. > - Redefine MultiXactMemberStorageSize() so as it does not take a > number of members in input, but as the amount of space taken between > two offsets. At least that would be more consistent with all the > other inline functions of multixact.h that rely on MultiXactOffset > inputs. Using a int64 is still OK I guess, there may be a case to > detect "negative" numbers and give a change to the users of the new > inline function to notice that they did a computation wrong, rather > than hiding a signedness problem.
So, here is what I have in mind, split into independent pieces: - Remove the existing type confusion with GetMultiXactInfo(), due to how things have always been done in MultiXactMemberFreezeThreshold(). - Add macro MultiXactOffsetStorageSize(), to calculate the amount of space used between two offsets. - The main patch, with adjustments in comments, the test (no non-ASCII characters in that, please). One thing that was really surprising is that you did not consider ROLE_PG_READ_ALL_STATS. We expect all the stats information to be hidden if a role is not granted access to them, and this function should be no exception especially as it relates to disk space usage like database or tablespace size functions. Anyway, attached are all these updated pieces. The doc edits are what I have mentioned upthread, close to what you have suggested to me offline. Comments? -- Michael
From dbe13b3d61c03f5d8d6773021e87c626ab04b3b0 Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Mon, 29 Dec 2025 11:39:56 +0900 Subject: [PATCH v14 1/3] Rework GetMultiXactInfo() This routine returned a number of offsets as a MultiXactOffset, but it is not actually an offset, just a number to define their range. This was confusing. This type confusion comes from the original implementation of MultiXactMemberFreezeThreshold(). --- src/include/access/multixact.h | 2 +- src/backend/access/transam/multixact.c | 16 ++++++++-------- 2 files changed, 9 insertions(+), 9 deletions(-) diff --git a/src/include/access/multixact.h b/src/include/access/multixact.h index 6433fe163641..d22abbb72512 100644 --- a/src/include/access/multixact.h +++ b/src/include/access/multixact.h @@ -109,7 +109,7 @@ extern bool MultiXactIdIsRunning(MultiXactId multi, bool isLockOnly); extern void MultiXactIdSetOldestMember(void); extern int GetMultiXactIdMembers(MultiXactId multi, MultiXactMember **members, bool from_pgupgrade, bool isLockOnly); -extern void GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members, +extern void GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *nextOffset, MultiXactId *oldestMultiXactId, MultiXactOffset *oldestOffset); extern bool MultiXactIdPrecedes(MultiXactId multi1, MultiXactId multi2); diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c index 34956a5a6634..0d6f594e2a06 100644 --- a/src/backend/access/transam/multixact.c +++ b/src/backend/access/transam/multixact.c @@ -2461,25 +2461,23 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result) * * Returns information about the current MultiXact state, as of: * multixacts: Number of MultiXacts (nextMultiXactId - oldestMultiXactId) - * members: Number of member entries (nextOffset - oldestOffset) + * nextOffset: Next-to-be-assigned offset * oldestMultiXactId: Oldest MultiXact ID still in use * oldestOffset: Oldest offset still in use */ void -GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members, +GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *nextOffset, MultiXactId *oldestMultiXactId, MultiXactOffset *oldestOffset) { - MultiXactOffset nextOffset; MultiXactId nextMultiXactId; LWLockAcquire(MultiXactGenLock, LW_SHARED); - nextOffset = MultiXactState->nextOffset; + *nextOffset = MultiXactState->nextOffset; *oldestMultiXactId = MultiXactState->oldestMultiXactId; nextMultiXactId = MultiXactState->nextMXact; *oldestOffset = MultiXactState->oldestOffset; LWLockRelease(MultiXactGenLock); - *members = nextOffset - *oldestOffset; *multixacts = nextMultiXactId - *oldestMultiXactId; } @@ -2514,16 +2512,18 @@ GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members, int MultiXactMemberFreezeThreshold(void) { - MultiXactOffset members; uint32 multixacts; uint32 victim_multixacts; double fraction; int result; MultiXactId oldestMultiXactId; MultiXactOffset oldestOffset; + MultiXactOffset nextOffset; + uint64 members; - /* Read the current offsets and members usage. */ - GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset); + /* Read the current offsets and multixact usage. */ + GetMultiXactInfo(&multixacts, &nextOffset, &oldestMultiXactId, &oldestOffset); + members = nextOffset - oldestOffset; /* If member space utilization is low, no special action is required. */ if (members <= MULTIXACT_MEMBER_LOW_THRESHOLD) -- 2.51.0
From 4ef3b82b91eaee132cfed886f6a1a1455bb084a3 Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Mon, 29 Dec 2025 11:41:35 +0900 Subject: [PATCH v14 2/3] Add MultiXactOffsetStorageSize() This calculates the amount of space taken by two multixact offsets, useful on its own to know the amount of space multixacts may use. This will be used by an upcoming patch. --- src/include/access/multixact_internal.h | 10 ++++++++++ 1 file changed, 10 insertions(+) diff --git a/src/include/access/multixact_internal.h b/src/include/access/multixact_internal.h index f2d6539e8a67..65dc2be148db 100644 --- a/src/include/access/multixact_internal.h +++ b/src/include/access/multixact_internal.h @@ -121,4 +121,14 @@ MXOffsetToMemberOffset(MultiXactOffset offset) member_in_group * sizeof(TransactionId); } +/* Storage space consumed by a range of offsets, in bytes */ +static inline int64 +MultiXactOffsetStorageSize(MultiXactOffset new_offset, + MultiXactOffset old_offset) +{ + Assert(new_offset >= old_offset); + return (int64) ((new_offset - old_offset) / MULTIXACT_MEMBERS_PER_MEMBERGROUP) * + MULTIXACT_MEMBERGROUP_SIZE; +} + #endif /* MULTIXACT_INTERNAL_H */ -- 2.51.0
From a34c851f3421d5023f896f28549e77fd6d760100 Mon Sep 17 00:00:00 2001 From: Naga Appani <[email protected]> Date: Wed, 24 Dec 2025 21:06:16 +0000 Subject: [PATCH v14 3/3] Add pg_get_multixact_stats() function for monitoring MultiXact usage Expose multixact state via a new SQL-callable function pg_get_multixact_stats(), returning: - num_mxids : number of MultiXact IDs in use - num_members : number of member entries in use - members_size : bytes used by num_members in pg_multixact/members directory - oldest_multixact : oldest MultiXact ID still needed This patch adds pg_get_multixact_stats() function - SQL-callable interface to GetMultiXactInfo() - Includes isolation tests for monitoring invariants Documentation updates: - func-info.sgml: add function entry - maintenance.sgml: mention monitoring multixact usage Build and catalog: - Add function to existing multixactfuncs.c - pg_proc.dat entry 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/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com --- src/include/catalog/pg_proc.dat | 10 ++ src/backend/utils/adt/multixactfuncs.c | 53 +++++++++ .../isolation/expected/multixact-stats.out | 89 ++++++++++++++ src/test/isolation/isolation_schedule | 1 + src/test/isolation/specs/multixact-stats.spec | 111 ++++++++++++++++++ src/test/regress/expected/misc_functions.out | 29 +++++ src/test/regress/sql/misc_functions.sql | 15 +++ doc/src/sgml/func/func-info.sgml | 33 ++++++ doc/src/sgml/maintenance.sgml | 39 +++++- 9 files changed, 375 insertions(+), 5 deletions(-) create mode 100644 src/test/isolation/expected/multixact-stats.out create mode 100644 src/test/isolation/specs/multixact-stats.spec diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index fd9448ec7b98..6caea6c8281e 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12612,4 +12612,14 @@ 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' }, +# Get multixact usage +{ oid => '9001', descr => 'get current multixact usage statistics', + proname => 'pg_get_multixact_stats', + provolatile => 'v', proparallel => 's', prorettype => 'record', + proargtypes => '', + proallargtypes => '{int8,int8,int8,xid}', + proargmodes => '{o,o,o,o}', + proargnames => '{num_mxids,num_members,members_size,oldest_multixact}', + prosrc => 'pg_get_multixact_stats'}, + ] diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c index a428e140bc4b..b39db200a391 100644 --- a/src/backend/utils/adt/multixactfuncs.c +++ b/src/backend/utils/adt/multixactfuncs.c @@ -15,7 +15,12 @@ #include "postgres.h" #include "access/multixact.h" +#include "access/multixact_internal.h" +#include "access/htup_details.h" +#include "catalog/pg_authid_d.h" #include "funcapi.h" +#include "miscadmin.h" +#include "utils/acl.h" #include "utils/builtins.h" /* @@ -85,3 +90,51 @@ pg_get_multixact_members(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funccxt); } + +/* + * pg_get_multixact_stats + * + * Returns statistics about current multixact usage. + */ +Datum +pg_get_multixact_stats(PG_FUNCTION_ARGS) +{ + TupleDesc tupdesc; + Datum values[4]; + bool nulls[4]; + uint64 members; + MultiXactId oldestMultiXactId; + uint32 multixacts; + MultiXactOffset oldestOffset; + MultiXactOffset nextOffset; + int64 membersBytes; + + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("return type must be a row type"))); + + GetMultiXactInfo(&multixacts, &nextOffset, &oldestMultiXactId, &oldestOffset); + members = nextOffset - oldestOffset; + + membersBytes = MultiXactOffsetStorageSize(nextOffset, oldestOffset); + + if (!has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS)) + { + /* + * Only superusers and roles with privileges of pg_read_all_stats can + * see details. + */ + memset(nulls, true, sizeof(bool) * tupdesc->natts); + } + else + { + values[0] = UInt32GetDatum(multixacts); + values[1] = Int64GetDatum(members); + values[2] = Int64GetDatum(membersBytes); + values[3] = UInt32GetDatum(oldestMultiXactId); + memset(nulls, false, sizeof(nulls)); + } + + return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)); +} diff --git a/src/test/isolation/expected/multixact-stats.out b/src/test/isolation/expected/multixact-stats.out new file mode 100644 index 000000000000..27a6510c4ad5 --- /dev/null +++ b/src/test/isolation/expected/multixact-stats.out @@ -0,0 +1,89 @@ +Parsed test spec with 2 sessions + +starting permutation: snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit +step snap0: + CREATE TEMP TABLE snap0 AS + SELECT num_mxids, num_members, oldest_multixact + FROM pg_get_multixact_stats(); + +step s1_begin: BEGIN; +step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; +?column? +-------- + 1 +(1 row) + +step snap1: + CREATE TEMP TABLE snap1 AS + SELECT num_mxids, num_members, oldest_multixact + FROM pg_get_multixact_stats(); + +step s2_begin: BEGIN; +step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; +?column? +-------- + 1 +(1 row) + +step snap2: + CREATE TEMP TABLE snap2 AS + SELECT num_mxids, num_members, oldest_multixact + FROM pg_get_multixact_stats(); + +step check_while_pinned: + SELECT r.assertion, r.ok + FROM snap0 s0 + JOIN snap1 s1 ON TRUE + JOIN snap2 s2 ON TRUE, + LATERAL unnest( + ARRAY[ + 'is_init_mxids', + 'is_init_members', + 'is_init_oldest_mxid', + 'is_init_oldest_off', + 'is_oldest_mxid_nondec_01', + 'is_oldest_mxid_nondec_12', + 'is_oldest_off_nondec_01', + 'is_oldest_off_nondec_12', + 'is_members_increased_ge1', + 'is_mxids_nondec_01', + 'is_mxids_nondec_12', + 'is_members_nondec_01', + 'is_members_nondec_12' + ], + ARRAY[ + (s2.num_mxids IS NOT NULL), + (s2.num_members IS NOT NULL), + (s2.oldest_multixact IS NOT NULL), + + (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)), + (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)), + + (s2.num_members >= COALESCE(s1.num_members, 0) + 1), + + (s1.num_mxids >= COALESCE(s0.num_mxids, 0)), + (s2.num_mxids >= COALESCE(s1.num_mxids, 0)), + (s1.num_members >= COALESCE(s0.num_members, 0)), + (s2.num_members >= COALESCE(s1.num_members, 0)) + ] + ) AS r(assertion, ok); + +assertion |ok +------------------------+-- +is_init_mxids |t +is_init_members |t +is_init_oldest_mxid |t +is_init_oldest_off |t +is_oldest_mxid_nondec_01|t +is_oldest_mxid_nondec_12|t +is_oldest_off_nondec_01 |t +is_oldest_off_nondec_12 |t +is_members_increased_ge1|t +is_mxids_nondec_01 |t +is_mxids_nondec_12 | +is_members_nondec_01 | +is_members_nondec_12 | +(13 rows) + +step s1_commit: COMMIT; +step s2_commit: COMMIT; diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index f2e067b1fbc5..01ff1c6586fe 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -63,6 +63,7 @@ test: delete-abort-savept-2 test: aborted-keyrevoke test: multixact-no-deadlock test: multixact-no-forget +test: multixact-stats test: lock-committed-update test: lock-committed-keyupdate test: update-locked-tuple diff --git a/src/test/isolation/specs/multixact-stats.spec b/src/test/isolation/specs/multixact-stats.spec new file mode 100644 index 000000000000..6c1dd94958d1 --- /dev/null +++ b/src/test/isolation/specs/multixact-stats.spec @@ -0,0 +1,111 @@ +# Test for pg_get_multixact_stats() +# +# We create exactly one fresh MultiXact on a brand-new table. While it is +# pinned by two open transactions, we check patterns of this function that +# VACUUM/FREEZE cannot violate: +# 1) "members" increased by ≥ 1 when the second session locked the row, +# 2) (num_mxids / num_members) did not decrease compared to earlier snapshots +# 3) "oldest_*" fields never decreases. +# +# This test does not do checks patterns after releasing locks, as freezing +# and/or truncation may shrink the multixact ranges calculated. + +setup +{ + CREATE TABLE mxq(id int PRIMARY KEY, v int); + INSERT INTO mxq VALUES (1, 42); +} + +teardown +{ + DROP TABLE mxq; +} + +# Two sessions that lock the same tuple, leading to one MultiXact with +# at least 2 members. +session "s1" +setup { SET client_min_messages = warning; SET lock_timeout = '5s'; } +step s1_begin { BEGIN; } +step s1_lock { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; } +step s1_commit { COMMIT; } + +session "s2" +setup { SET client_min_messages = warning; SET lock_timeout = '5s'; } +step s2_begin { BEGIN; } +step s2_lock { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; } +step s2_commit { COMMIT; } + +# Save multixact state *BEFORE* any locking; some of these may be NULLs if +# multixacts have not initialized yet. +step snap0 { + CREATE TEMP TABLE snap0 AS + SELECT num_mxids, num_members, oldest_multixact + FROM pg_get_multixact_stats(); +} + +# Save multixact state after s1 has locked the row. +step snap1 { + CREATE TEMP TABLE snap1 AS + SELECT num_mxids, num_members, oldest_multixact + FROM pg_get_multixact_stats(); +} + +# Save multixact state after s2 joins to lock the same row, leading to +# a multixact with at least 2 members. +step snap2 { + CREATE TEMP TABLE snap2 AS + SELECT num_mxids, num_members, oldest_multixact + FROM pg_get_multixact_stats(); +} + +# Pretty, deterministic key/value outputs based of boolean checks: +# is_init_mxids : num_mxids is non-NULL +# is_init_members : num_members is non-NULL +# is_init_oldest_mxid : oldest_multixact is non-NULL +# is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0->snap1) +# is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1->snap2) +# is_members_increased_ge1 : members increased by at least 1 when s2 joined +# is_mxids_nondec_01 : num_mxids did not decrease (snap0->snap1) +# is_mxids_nondec_12 : num_mxids did not decrease (snap1->snap2) +# is_members_nondec_01 : num_members did not decrease (snap0->snap1) +# is_members_nondec_12 : num_members did not decrease (snap1->snap2) +step check_while_pinned { + SELECT r.assertion, r.ok + FROM snap0 s0 + JOIN snap1 s1 ON TRUE + JOIN snap2 s2 ON TRUE, + LATERAL unnest( + ARRAY[ + 'is_init_mxids', + 'is_init_members', + 'is_init_oldest_mxid', + 'is_init_oldest_off', + 'is_oldest_mxid_nondec_01', + 'is_oldest_mxid_nondec_12', + 'is_oldest_off_nondec_01', + 'is_oldest_off_nondec_12', + 'is_members_increased_ge1', + 'is_mxids_nondec_01', + 'is_mxids_nondec_12', + 'is_members_nondec_01', + 'is_members_nondec_12' + ], + ARRAY[ + (s2.num_mxids IS NOT NULL), + (s2.num_members IS NOT NULL), + (s2.oldest_multixact IS NOT NULL), + + (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)), + (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)), + + (s2.num_members >= COALESCE(s1.num_members, 0) + 1), + + (s1.num_mxids >= COALESCE(s0.num_mxids, 0)), + (s2.num_mxids >= COALESCE(s1.num_mxids, 0)), + (s1.num_members >= COALESCE(s0.num_members, 0)), + (s2.num_members >= COALESCE(s1.num_members, 0)) + ] + ) AS r(assertion, ok); +} + +permutation snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index d7d965d884a1..6c03b1a79d75 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -999,3 +999,32 @@ SELECT test_relpath(); SELECT pg_replication_origin_create('regress_' || repeat('a', 505)); ERROR: replication origin name is too long DETAIL: Replication origin names must be no longer than 512 bytes. +-- pg_get_multixact_stats tests +CREATE ROLE regress_multixact_funcs; +-- Access granted for superusers. +SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats(); + null_result +------------- + f +(1 row) + +-- Access revoked. +SET ROLE regress_multixact_funcs; +SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats(); + null_result +------------- + t +(1 row) + +RESET ROLE; +-- Access granted for users with pg_monitor rights. +GRANT pg_monitor TO regress_multixact_funcs; +SET ROLE regress_multixact_funcs; +SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats(); + null_result +------------- + f +(1 row) + +RESET ROLE; +DROP ROLE regress_multixact_funcs; diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql index 0fc20fbb6b40..35b7983996c4 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -459,3 +459,18 @@ SELECT test_relpath(); -- pg_replication_origin.roname limit SELECT pg_replication_origin_create('regress_' || repeat('a', 505)); + +-- pg_get_multixact_stats tests +CREATE ROLE regress_multixact_funcs; +-- Access granted for superusers. +SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats(); +-- Access revoked. +SET ROLE regress_multixact_funcs; +SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats(); +RESET ROLE; +-- Access granted for users with pg_monitor rights. +GRANT pg_monitor TO regress_multixact_funcs; +SET ROLE regress_multixact_funcs; +SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats(); +RESET ROLE; +DROP ROLE regress_multixact_funcs; diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml index d4508114a48e..175f18315cd4 100644 --- a/doc/src/sgml/func/func-info.sgml +++ b/doc/src/sgml/func/func-info.sgml @@ -2975,6 +2975,39 @@ 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>num_mxids</parameter> <type>integer</type>, + <parameter>num_members</parameter> <type>bigint</type>, + <parameter>members_size</parameter> <type>bigint</type>, + <parameter>oldest_multixact</parameter> <type>xid</type> ) + </para> + <para> + Returns statistics about current multixact usage: + <literal>num_mxids</literal> is the total number of multixact IDs + currently present in the system, <literal>num_members</literal> is + the total number of multixact member entries currently present in + the system, <literal>members_size</literal> is the storage occupied + by <literal>num_members</literal> in the + <literal>pg_multixact/members</literal> directory, + <literal>oldest_multixact</literal> is the oldest multixact ID still + in use. + </para> + <para> + The function reports statistics at the time it is invoked. Values may + vary between calls, even within a single transaction. + </para> + <para> + To use this function, you must have privileges of the + <literal>pg_read_all_stats</literal> role. + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 08e6489afb8e..7c958b062731 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 the number + of multixact member entries created exceeds approximately 2 billion + entries (occupying roughly 10GB in the + <literal>pg_multixact/members</literal> directory), 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 - scans will occur even if autovacuum is nominally disabled. The members storage - area can grow up to about 20GB before reaching wraparound. + have the oldest multixact-age. Both of these kinds of aggressive + scans will occur even if autovacuum is nominally disabled. At approximately + 4 billion entries (occupying roughly 20GB in the + <literal>pg_multixact/members</literal> directory), even more aggressive + vacuum scans are triggered to reclaim member storage space. + </para> + + <para> + The <function>pg_get_multixact_stats()</function> function described in + <xref linkend="functions-pg-snapshot"/> provides a way to monitor + multixact allocation and usage patterns in real time, for example: + <programlisting> +=# SELECT *, pg_size_pretty(members_size) members_size_pretty + FROM pg_catalog.pg_get_multixact_stats(); + num_mxids | num_members | members_size | oldest_multixact | members_size_pretty +-----------+-------------+--------------+------------------+--------------------- + 311740299 | 2785241176 | 13926205880 | 2 | 13 GB +(1 row) + </programlisting> + This output shows a system with significant multixact activity: about + 312 million multixact IDs and about 2.8 billion member entries consuming + 13 GB of storage space. + A spike in <literal>num_mxids</literal> might indicate multiple sessions + running <literal>UPDATE</literal> statements with foreign key checks, + concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent + use of savepoints causing lock contention. + If <literal>oldest_multixact</literal> value remains unchanged while + <literal>num_members</literal> grows, it could indicate that long-running + transactions are preventing cleanup, or autovacuum is + not keeping up with the workload. </para> <para> -- 2.51.0
signature.asc
Description: PGP signature
