Feedback-ID: i0fe9450f:Fastmail
Date: Thu, 25 Dec 2025 09:45:33 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Naga Appani <nagnrik@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
	Tomas Vondra <tomas@vondra.me>, Xuneng Zhou <xunengzhou@gmail.com>,
	torikoshia <torikoshia@oss.nttdata.com>,
	Kirill Reshke <reshkekirill@gmail.com>,
	pgsql-hackers@postgresql.org
Subject: Re: [Proposal] Expose internal MultiXact member count function for
 efficient monitoring
Message-ID: <aUyJLUMbZumRr0BB@paquier.xyz>
References: 
 <CABPTF7WfTDdhZbHBgoqo=uGP_DZO-xO1bX5BfkwjgHK9jU+tOA@mail.gmail.com>
 <95850ce1-2d5e-4271-92ea-c2a02e36b303@vondra.me>
 <CA+QeY+Aja_=j1EuY87L06KaPO4EJqqkS4B+Vg9AsWnGM1d_VRA@mail.gmail.com>
 <CAExHW5vDJ526OXv+Hk-=TxPoiL7RR+k+xSF2tyNqtaJHC5aoDQ@mail.gmail.com>
 <CA+QeY+CF2q2M51k5t4rZZ2SNeEq8ORf3Dr8T1+jm72VsHRJfjw@mail.gmail.com>
 <CAExHW5shToKbeiw1eRGeiSyVeCYZUJyw2KUtBKYFn0J3dCGzbA@mail.gmail.com>
 <CA+QeY+CqRjUj93_kV0YmMBqfkgo4WGEoiYOaP_wCMoJ8FOKpog@mail.gmail.com>
 <aUIvP9iZ1F6VSOYp@paquier.xyz>
 <CAExHW5t-di9g+S-7pdXxCA8z3AtCWYn_=Ou9fY0aMg7K0wA2KA@mail.gmail.com>
 <CA+QeY+DO2y-3JfK68Wqerv2wmG43B7aoAhsWa7YrsKZo_ayOtg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="70YTOJc8tR3bpLh2"
Content-Disposition: inline
In-Reply-To: 
 <CA+QeY+DO2y-3JfK68Wqerv2wmG43B7aoAhsWa7YrsKZo_ayOtg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aUyJLUMbZumRr0BB%40paquier.xyz>
Precedence: bulk


--70YTOJc8tR3bpLh2
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Dec 24, 2025 at 06:09:14PM -0600, Naga Appani wrote:
> I=E2=80=99ve updated the patch as suggested.
>=20
> The member storage size calculation has been refactored into a static
> inline function, MultiXactMemberStorageSize(), in
> src/include/access/multixact_internal.h.
>=20
> Please find v13 attached.

Seems basically sensible here for the structure, including the hints
and recommendations for the GUCs.

+     of multixact member entries created exceeds approximately 2^31 entries
[...]
+     This output shows a system with significant multixact activity: about=
 ~312 million
+     multixact IDs and ~2.8 billion member entries consuming 13 GB of stor=
age space.

The documentation could be improved more.  The power '^' and tilde
symbols are not used for references.  If any, I'd encourage using
wordings like "2 billion" entries for all these paragraphs across the
board.  For the tilde part, you would mean "at least" or "at most"
rather than the boundaries implied with the tilde (aka we should not
expect the reader the mental effort to translate and  understand what
these symbols mean, especially for non-native English speaker).

+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking=
 or
+        foreign key operations can lead to increased I/O and CPU overhead =
during
+        vacuum operations. Monitoring these stats helps tune autovacuum fr=
equency
+        and transaction patterns.

Saying that, this paragraph does not seem that useful to me,
especially the last sentence which is evasive and can apply to
anything related to monitoring.

The second hint is more useful, but perhaps we should mention which
GUC(s) should be touched to make num_members go lower?  As a whole,
the orderedlist does not seem strongly necessary to me: the third
item is evasive, the first and second items describe problematic
patterns and what could cause them.  As a whole, for the docs
part, the new additions in the existing paragraph of maintenance.sgml
are OK for me.  The first part of the new paragraph added also
provides some direct information about how useful this new function is
to evaluate the amount of disk space used.  I'd like to think that we
should just complete it the two facts about num_mxids and num_members
you are listing, with two sentences appended at the end of the new
paragraph rather than a list of items.

If we don't completely agree about the "hint" part, we could split the
patch in two for now: let's add the function first, then discuss more
about what kind of tweaks and patterns we want to document as a set of
follow-up changes.  It does not change the fact that the function is
useful for disk-space monitoring purposes.  The patterns and hints are
a second different matter.
--
Michael

--70YTOJc8tR3bpLh2
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmlMiS0ACgkQnvQgOdby
QH03uA//TzaxJtJgtdIbmQEI3sFSOx09kiaPX+5EWsbMtECSZew6IOFn9wZ/vP+B
jgcrLBDkv3dYb238Xl73OEB3mxORj9aVbHD5AkgbY5TEU8vE2W2QeMPm1EiWsGmq
l2kpcOrXuP3W6wcJz+562d06cdQB1UFbswsPs9pvnQFvcxvXbfz4neN92gSASjZd
02Ya62ik3HcyFBl14NPUCOLP3qbfUtDdOVXxFmt3zF9LJqsV19HrPeVz1H05ETkn
aOuSFAiRSNdAl5Fg+X34jpxqIexCe2ySM1rutZdEU5gqjY5criwN9TMqM8fBgboj
nwTce3e9ckrHloO7PMbzZg4/1plWal5DZ/9Q1D9qW15lJmBtsVaM3MuYvvq5Kd9r
TUEd4RjUYS4tMl3mP60IdqG3uQCZrOBac2koB5fLooTLHMpq9gSMs0f5iUftEOMO
PbF1C+2n4VDpelwusJpOKmZrDewgPzFWKZ8cvhgLC9zwdA1dSZaxmQf9Uf07MCQS
/b2IK1e4Pvloog36CT5CPZ0Wf31TpjdRymZ6BomAtLs1eA+EI1hUyw7wtCdCV2I4
HmMFvt5P2leNc35VDHv7dPooUaYkCziYtKwzvgtLeLURaMwnhp5NQFmRGUnBmeVe
lfLsITVFaygd3UUM4T9AUBRDeRRNRm0J7BhliAptbfulFU304Nw=
=cEM0
-----END PGP SIGNATURE-----

--70YTOJc8tR3bpLh2--