MIME-Version: 1.0
References: 
 <CA+QeY+AAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg@mail.gmail.com>
In-Reply-To: 
 <CA+QeY+AAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg@mail.gmail.com>
From: Naga Appani <nagnrik@gmail.com>
Date: Tue, 11 Mar 2025 00:15:28 -0500
Message-ID: 
 <CA+QeY+CuTcKSFmw3H-_OtCv+bv0T4uTSFgaAY1N9ssL-WaiiSA@mail.gmail.com>
Subject: Re: [Proposal] Expose internal MultiXact member count function for
 efficient monitoring
To: pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f3559e06300a2da7"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BQeY%2BCuTcKSFmw3H-_OtCv%2Bbv0T4uTSFgaAY1N9ssL-WaiiSA%40mail.gmail.com>
Precedence: bulk

--000000000000f3559e06300a2da7
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 10, 2025 at 10:43=E2=80=AFAM Naga Appani <nagnrik@gmail.com> wr=
ote:

> Hi,
>
> I would like to propose exposing an internal PostgreSQL function called
> ReadMultiXactCounts() to allow for efficient monitoring of MultiXact
> member usage. This function provides an accurate, real-time view of
> MultiXact activity by directly retrieving the actual member count, rather
> than relying on storage-based calculations.
>
> *Current Challenges: *The existing approach we are currently using to
> estimate MultiXact member usage has several drawbacks:
>
>    - *Filesystem scanning overhead: *These functions recursively scan the
>    pg_multixact directory, iterating over potentially thousands or
>    millions of files, and retrieving file sizes using stat() calls, which
>    introduces significant I/O overhead.
>    - *Potential performance bottleneck:* On systems with high transaction
>    throughput generating large numbers of MultiXact members, the
>    filesystem-based approach scales poorly due to the latency of stat() c=
alls,
>    especially on network-based filesystems like RDS/Aurora.
>    - *Not a real-time or memory-efficient solution:* The current approach
>    does not provide a direct, in-memory view of MultiXact activity.
>
> *Proposed Solution*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 th=
e
> performance issues of the current filesystem-based estimation methods.
> ................
> ...............
>


My apologies for re-posting. This is my first time writing to the hackers
list, and I accidentally used HTML formatting. Below is the original
request in plain text:

***************************************************************************=
***************************************************************************=
********
I would like to propose exposing an internal PostgreSQL function called
ReadMultiXactCounts()[1] to allow for efficient monitoring of MultiXact
member usage. This function provides an accurate, real-time view of
MultiXact activity by directly retrieving the actual member count, rather
than relying on storage-based calculations.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Current Challenges
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
The existing approach we are currently using to estimate MultiXact member
usage has several drawbacks:
- Filesystem scanning overhead: These functions recursively scan the
pg_multixact directory, iterating over potentially thousands or millions of
files, and retrieving file sizes using stat() calls, which introduces
significant I/O overhead.
- Potential performance bottleneck: On systems with high transaction
throughput generating large numbers of MultiXact members, the
filesystem-based approach scales poorly due to the latency of stat() calls,
especially on network-based filesystems like RDS/Aurora.
- Not a real-time or memory-efficient solution: The current approach does
not provide a direct, in-memory view of MultiXact activity.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Proposal
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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.

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.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Performance comparison
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
The performance comparison between the current and proposed approaches
shows a significant improvement, with the proposed solution taking only a
fraction of a millisecond to retrieve the MultiXact member count, compared
to tens or hundreds of milliseconds for the current filesystem-based
approach.  And as more members are generated, the gap widens.

Following is the comparison of performance between calculating storage of
MultiXact members directory and retrieving the count of members.

Implementation                                       |  Used size  |
MultiXact members
----------------------------------------------------+-------------+--------=
----------
EC2 community (RDS version of pg_ls_multixactdir)   |   8642 MB  | 1.8
billion
Linux du command                                    |   8642 MB  | 1.8
billion
Proposal (ReadMultiXactCounts)                      |   8642 MB  | 1.8
billion

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Sample runs
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Using "du -h"
--------------------
postgres=3D# \! time du -h /rdsdbdata/db/17.4/data/pg_multixact/members
13G     /rdsdbdata/db/17.4/data/pg_multixact/members

real    0m0.285s    <=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D  time taken
user    0m0.050s    <=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D  time taken
sys     0m0.140s

Using RDS's  pg_ls_multixactdir ():
------------------------------------------------------------
postgres=3D# SELECT
    pg_size_pretty(coalesce(sum(size), 0)) AS members_size
FROM
    pg_ls_multixactdir ()
WHERE
    name LIKE 'pg_multixact/members%';
 members_size
--------------
 13 GB
(1 row)

Time: 226.533 ms <=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D  time taken

Using proposed function:
----------------------------------------
postgres=3D# SELECT to_char(pg_get_multixact_members_count(),
'999,999,999,999') AS members_count;
  members_count
------------------
    2,745,823,171
(1 row)

Time: 0.142 ms   <=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D  time taken
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

I believe exposing ReadMultiXactCounts() would be a valuable addition to
the PostgreSQL ecosystem, providing users with a more reliable and
efficient way to monitor MultiXact usage. Appreciate your feedback or
discussion on this proposal.

Please let me know if this approach is acceptable, so I=E2=80=99ll go ahead=
 and
submit a patch.

Thank you!

References:
[1]
https://github.com/postgres/postgres/blob/master/src/backend/access/transam=
/multixact.c#L2925-L2948


>
> Thank you!
>
> Best regards,
> Naga Appani
> Postgres Database Engineer
> Amazon Web Services
>

--000000000000f3559e06300a2da7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Mar 10,=
 2025 at 10:43=E2=80=AFAM Naga Appani &lt;<a href=3D"mailto:nagnrik@gmail.c=
om">nagnrik@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex"><div dir=3D"ltr">Hi,<div><br>I would like to propose e=
xposing an internal PostgreSQL function called=C2=A0<code>ReadMultiXactCoun=
ts()</code>=C2=A0to allow for efficient monitoring of MultiXact member usag=
e. This function provides an accurate, real-time view of MultiXact activity=
 by directly retrieving the actual member count, rather than relying on sto=
rage-based calculations.<br><b><br></b><div><b>Current Challenges:=C2=A0</b=
>The existing approach we are currently using to estimate MultiXact member =
usage has several drawbacks:<br><ul><li><b>Filesystem scanning overhead:=C2=
=A0</b>These functions recursively scan the=C2=A0<code>pg_multixact</code>=
=C2=A0directory, iterating over potentially thousands or millions of files,=
 and retrieving file sizes using=C2=A0<code>stat()</code>=C2=A0calls, which=
 introduces significant I/O overhead.</li><li><b>Potential performance bott=
leneck:</b>=C2=A0On systems with high transaction throughput generating lar=
ge numbers of MultiXact members, the filesystem-based approach scales poorl=
y due to the latency of=C2=A0<code>stat()</code>=C2=A0calls, especially on =
network-based filesystems like RDS/Aurora.</li><li><b>Not a real-time or me=
mory-efficient solution:</b>=C2=A0The current approach does not provide a d=
irect, in-memory view of MultiXact activity.</li></ul><b>Proposed Solution<=
/b>The internal=C2=A0<code>ReadMultiXactCounts()</code>=C2=A0function, impl=
emented in=C2=A0<code>multixact.c</code>, 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 me=
thods.</div><div>................</div><div>...............</div></div></di=
v></blockquote><div><br></div><div><br></div>My apologies for re-posting. T=
his is my first time writing to the hackers list, and I accidentally used H=
TML formatting. Below is the original request in plain text:<br><br>*******=
***************************************************************************=
***************************************************************************=
*<br>I would like to propose exposing an internal PostgreSQL function calle=
d ReadMultiXactCounts()[1] to allow for efficient monitoring of MultiXact m=
ember usage. This function provides an accurate, real-time view of MultiXac=
t activity by directly retrieving the actual member count, rather than rely=
ing on storage-based calculations.<br><br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D<br>Current Challenges<br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D<br>The existing approach we are currently using to estimate=
 MultiXact member usage has several drawbacks:<br>- Filesystem scanning ove=
rhead: These functions recursively scan the pg_multixact directory, iterati=
ng over potentially thousands or millions of files, and retrieving file siz=
es using stat() calls, which introduces significant I/O overhead.<br>- Pote=
ntial performance bottleneck: On systems with high transaction throughput g=
enerating large numbers of MultiXact members, the filesystem-based approach=
 scales poorly due to the latency of stat() calls, especially on network-ba=
sed filesystems like RDS/Aurora.<br>- Not a real-time or memory-efficient s=
olution: The current approach does not provide a direct, in-memory view of =
MultiXact activity.<br><br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D<br>Proposal<br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br>T=
he internal ReadMultiXactCounts() function, implemented in multixact.c, dir=
ectly 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.<br><br>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 in=
tegrating with tools like Amazon RDS Performance Insights and Amazon CloudW=
atch to provide enhanced database insights and proactive managed monitoring=
 for users.<br><br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D<br>Performance comparison<br>=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br>The performance comp=
arison between the current and proposed approaches shows a significant impr=
ovement, with the proposed solution taking only a fraction of a millisecond=
 to retrieve the MultiXact member count, compared to tens or hundreds of mi=
lliseconds for the current filesystem-based approach.=C2=A0 And as more mem=
bers are generated, the gap widens.<br><br>Following is the comparison of p=
erformance between calculating storage of MultiXact members directory and r=
etrieving the count of members.<br><br>Implementation =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0Used size =C2=A0| MultiXact =
members<br>----------------------------------------------------+-----------=
--+------------------<br>EC2 community (RDS version of pg_ls_multixactdir) =
=C2=A0 | =C2=A0 8642 MB =C2=A0| 1.8 billion<br>Linux du command =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 8642 MB =C2=A0| 1.8 billi=
on<br>Proposal (ReadMultiXactCounts) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 8642 MB =C2=A0| 1.8 billion<=
br><br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br>Sample runs=
<br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br>Using &quot;du=
 -h&quot;<br>--------------------<br>postgres=3D# \! time du -h /rdsdbdata/=
db/17.4/data/pg_multixact/members<br>13G =C2=A0 =C2=A0 /rdsdbdata/db/17.4/d=
ata/pg_multixact/members<br><br>real =C2=A0 =C2=A00m0.285s =C2=A0 =C2=A0&lt=
;=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D =C2=A0time taken<br>user =C2=A0 =C2=A00m0.050s =C2=A0 =C2=
=A0&lt;=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D =C2=A0time taken<br>sys =C2=A0 =C2=A0 0m0.140s<br><br=
>Using RDS&#39;s =C2=A0pg_ls_multixactdir ():<br>--------------------------=
----------------------------------<br>postgres=3D# SELECT<br>=C2=A0 =C2=A0 =
pg_size_pretty(coalesce(sum(size), 0)) AS members_size<br>FROM<br>=C2=A0 =
=C2=A0 pg_ls_multixactdir ()<br>WHERE<br>=C2=A0 =C2=A0 name LIKE &#39;pg_mu=
ltixact/members%&#39;;<br>=C2=A0members_size<br>--------------<br>=C2=A013 =
GB<br>(1 row)<br><br>Time: 226.533 ms &lt;=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =C2=A0time taken<br>=
<br>Using proposed function:<br>----------------------------------------<br=
>postgres=3D# SELECT to_char(pg_get_multixact_members_count(), &#39;999,999=
,999,999&#39;) AS members_count;<br>=C2=A0 members_count<br>---------------=
---<br>=C2=A0 =C2=A0 2,745,823,171<br>(1 row)<br><br>Time: 0.142 ms =C2=A0 =
&lt;=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D =C2=A0time taken</div><div class=3D"gmail_quote gmail_qu=
ote_container">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br><br><=
/div><div class=3D"gmail_quote gmail_quote_container">I believe exposing Re=
adMultiXactCounts() would be a valuable addition to the PostgreSQL ecosyste=
m, providing users with a more reliable and efficient way to monitor MultiX=
act usage. Appreciate your feedback or discussion on this proposal.<br><br>=
Please let me know if this approach is acceptable, so I=E2=80=99ll go ahead=
 and submit a patch.<br><br>Thank you!<br><br>References:<br><div>[1] <a hr=
ef=3D"https://github.com/postgres/postgres/blob/master/src/backend/access/t=
ransam/multixact.c#L2925-L2948">https://github.com/postgres/postgres/blob/m=
aster/src/backend/access/transam/multixact.c#L2925-L2948</a>=C2=A0</div><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><div><=
br></div><div>Thank you!</div><div><br>Best regards,=C2=A0<br></div><div>Na=
ga Appani</div><div>Postgres Database Engineer</div><div>Amazon Web Service=
s</div></div></div>
</blockquote></div></div>

--000000000000f3559e06300a2da7--