MIME-Version: 1.0
References: <CAH7vdhP=7WHj_kgzkinVkpBHGTP521KhVg2g_mPu6imB_kdABA@mail.gmail.com>
In-Reply-To: <CAH7vdhP=7WHj_kgzkinVkpBHGTP521KhVg2g_mPu6imB_kdABA@mail.gmail.com>
From: Jim Vanns <jvanns@ilm.com>
Date: Wed, 31 Jul 2024 19:27:13 +0100
Message-ID: <CAH7vdhN3AonFJsdevnnAgW=0hyy=9J+1Gs+qsgDcD+G9EaNW-w@mail.gmail.com>
Subject: Fwd: Suggestions to overcome 'multixact "members" limit exceeded' in
 temporary tables
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000eaae7e061e8f3e2f"
Archived-At: <https://www.postgresql.org/message-id/CAH7vdhN3AonFJsdevnnAgW%3D0hyy%3D9J%2B1Gs%2BqsgDcD%2BG9EaNW-w%40mail.gmail.com>
Precedence: bulk

--000000000000eaae7e061e8f3e2f
Content-Type: text/plain; charset="UTF-8"

(resending to general since I believe I originally sent it to hackers by
mistake)

I've reached the limit of my understanding and attempts at correcting my
code/use of temporary tables in the face of multixact members and have come
to ask for your help! Here's a brief description of my software;

Pool of N connection sessions, persistent for the duration of the program
lifetime.
Upon each session initialisation, a set of CREATE TEMPORARY TABLE ON COMMIT
DELETE ROWS statements are made for bulk ingest.
Each session is acquired by a thread for use when ingesting data and
therefore each temporary table remains until the session is terminated
The thread performs a COPY <temp table> FROM STDIN in binary format
Then an INSERT INTO <main table> SELECT FROM <temp table> WHERE...

This has been working great for a while and with excellent throughput.
However, upon scaling up I eventually hit this error;

ERROR:  multixact "members" limit exceeded
DETAIL:  This command would create a multixact with 2 members, but the
remaining space is only enough for 0 members.
HINT:  Execute a database-wide VACUUM in database with OID 16467 with
reduced vacuum_multixact_freeze_min_age and
vacuum_multixact_freeze_table_age settings.

And it took me quite a while to identify that it appears to be coming from
the temporary table (the other 'main' tables were being autovacuumed OK) -
which makes sense because they have a long lifetime, aren't auto vacuumed
and shared by transactions (in turn).

I first attempted to overcome this by introducing an initial step of always
creating the temporary table before the copy (and using on commit drop) but
this lead to a terrible performance degradation.
Next, I reverted the above and instead I introduced a VACUUM step every
1000000 (configurable) ingest operations
Finally, I introduced a TRUNCATE step in addition to the occasional VACUUM
since the TRUNCATE allowed the COPY option of FREEZE.

The new overhead appears minimal until after several hours and again I've
hit a performance degradation seemingly dominated by the TRUNCATE.

My questions are;

1) Is the VACUUM necessary if I use TRUNCATE + COPY FREEZE (on the
temporary table)?
2) Is there really any benefit to using FREEZE here or is it best to just
VACUUM the temporary tables occasionally?
3) Is there a better way of managing all this!? Perhaps re-CREATING the TT
every day or something?

I understand that I can create a Linux tmpfs partition for a tablespace for
the temporary tables and that may speed up the TRUNCATE but that seems like
a hack and I'd rather not do it at all if it's avoidable.

Thanks for your help,

Jim

PS. PG version in use is 15.4 if that matters here

-- 
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

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

<div dir=3D"ltr"><div class=3D"gmail_quote"><div>(resending to general sinc=
e I believe I originally sent it to hackers by mistake)</div><div><br></div=
><div dir=3D"ltr">I&#39;ve reached the limit of my understanding and attemp=
ts at correcting my code/use of temporary tables in the face of=C2=A0multix=
act members and have come to ask for your help! Here&#39;s a brief descript=
ion of my software;<div><br></div><div>Pool of N connection sessions, persi=
stent for the duration of the program lifetime.</div><div>Upon each session=
 initialisation, a set of CREATE TEMPORARY TABLE ON COMMIT DELETE ROWS stat=
ements are made for bulk ingest.</div><div>Each session is acquired by a th=
read for use when ingesting=C2=A0data and therefore each temporary table re=
mains until the session is terminated</div><div>The thread performs a COPY =
&lt;temp table&gt; FROM STDIN in binary format</div><div>Then an INSERT INT=
O &lt;main table&gt; SELECT FROM &lt;temp table&gt; WHERE...</div><div><br>=
</div><div>This has been working great for a while and with excellent throu=
ghput. However, upon scaling up I eventually hit this error;</div><div><br>=
</div><div>ERROR: =C2=A0multixact &quot;members&quot; limit exceeded<br>DET=
AIL: =C2=A0This command would create a multixact with 2 members, but the re=
maining space is only enough for 0 members.<br>HINT: =C2=A0Execute a databa=
se-wide VACUUM in database with OID 16467 with reduced vacuum_multixact_fre=
eze_min_age and vacuum_multixact_freeze_table_age settings.<br></div><div><=
div><br></div><div>And it took me quite a while to identify that it appears=
 to be coming from the temporary table (the other &#39;main&#39; tables wer=
e being autovacuumed=C2=A0OK) - which makes sense because they have a long =
lifetime, aren&#39;t auto vacuumed and shared by transactions (in turn).</d=
iv><div><br></div><div>I first attempted to overcome this by introducing an=
 initial step of always creating the temporary table before the copy (and u=
sing on commit drop) but this lead to a terrible performance degradation.</=
div><div>Next, I reverted the above and instead I introduced a VACUUM step =
every 1000000 (configurable) ingest operations</div><div>Finally, I introdu=
ced a TRUNCATE step in addition to the occasional VACUUM since the TRUNCATE=
 allowed the COPY option of FREEZE.</div><div><br></div><div>The new overhe=
ad appears minimal until after several hours and again I&#39;ve hit a perfo=
rmance degradation=C2=A0seemingly dominated=C2=A0by the TRUNCATE.</div><div=
><br></div><div>My questions are;</div><div><br></div><div>1) Is the VACUUM=
 necessary if I use TRUNCATE=C2=A0+ COPY FREEZE (on the temporary table)?</=
div><div>2) Is there really any benefit to using FREEZE here or is it best =
to just VACUUM the temporary tables occasionally?</div><div>3) Is there a b=
etter way of managing all this!? Perhaps re-CREATING the TT every day or so=
mething?</div><div><br></div><div>I understand that I can create a Linux tm=
pfs partition for a tablespace for the temporary tables and that may speed =
up the TRUNCATE but that seems like a hack and I&#39;d rather not do it at =
all if it&#39;s avoidable.</div><div><br></div><div>Thanks for your help,</=
div><div><br></div><div>Jim</div><div><br></div><div>PS. PG version in use =
is 15.4 if that matters here</div><div><br></div></div></div></div><span cl=
ass=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmai=
l_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr">Jim Vanns<=
br>Principal Production Engineer<br>Industrial Light &amp; Magic, London</d=
iv></div></div>

--000000000000eaae7e061e8f3e2f--