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

--00000000000010db9f061e8f41a1
Content-Type: text/plain; charset="UTF-8"

I've been able to observe that the performance degradation with TRUNCATE
appears to happen when other ancillary processes are running that are also
heavy users of temporary tables. If I used an exclusive tablespace, would
that improve things?

Cheers

Jim

On Wed, 31 Jul 2024 at 19:27, Jim Vanns <jvanns@ilm.com> wrote:

> (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
>

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

<div dir=3D"ltr"><div dir=3D"ltr">I&#39;ve been able to observe that the pe=
rformance degradation=C2=A0with TRUNCATE appears to happen when other ancil=
lary=C2=A0processes are running that are also heavy users of temporary tabl=
es. If I used an exclusive tablespace, would that improve things?<div><br><=
/div><div>Cheers</div><div><br></div><div>Jim</div><div><br></div></div><di=
v class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, 31 Ju=
l 2024 at 19:27, Jim Vanns &lt;<a href=3D"mailto:jvanns@ilm.com">jvanns@ilm=
.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div>(resending to general =
since 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 at=
tempts at correcting my code/use of temporary tables in the face of=C2=A0mu=
ltixact members and have come to ask for your help! Here&#39;s a brief desc=
ription of my software;<div><br></div><div>Pool of N connection sessions, p=
ersistent for the duration of the program lifetime.</div><div>Upon each ses=
sion initialisation, a set of CREATE TEMPORARY TABLE ON COMMIT DELETE ROWS =
statements are made for bulk ingest.</div><div>Each session is acquired by =
a thread for use when ingesting=C2=A0data and therefore each temporary tabl=
e remains until the session is terminated</div><div>The thread performs a C=
OPY &lt;temp table&gt; FROM STDIN in binary format</div><div>Then an INSERT=
 INTO &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 t=
hroughput. However, upon scaling up I eventually hit this error;</div><div>=
<br></div><div>ERROR: =C2=A0multixact &quot;members&quot; limit exceeded<br=
>DETAIL: =C2=A0This command would create a multixact with 2 members, but th=
e remaining space is only enough for 0 members.<br>HINT: =C2=A0Execute a da=
tabase-wide VACUUM in database with OID 16467 with reduced vacuum_multixact=
_freeze_min_age and vacuum_multixact_freeze_table_age settings.<br></div><d=
iv><div><br></div><div>And it took me quite a while to identify that it app=
ears to be coming from the temporary table (the other &#39;main&#39; tables=
 were being autovacuumed=C2=A0OK) - which makes sense because they have a l=
ong lifetime, aren&#39;t auto vacuumed and shared by transactions (in turn)=
.</div><div><br></div><div>I first attempted to overcome this by introducin=
g an initial step of always creating the temporary table before the copy (a=
nd using on commit drop) but this lead to a terrible performance degradatio=
n.</div><div>Next, I reverted the above and instead I introduced a VACUUM s=
tep every 1000000 (configurable) ingest operations</div><div>Finally, I int=
roduced a TRUNCATE step in addition to the occasional VACUUM since the TRUN=
CATE allowed the COPY option of FREEZE.</div><div><br></div><div>The new ov=
erhead appears minimal until after several hours and again I&#39;ve hit a p=
erformance degradation=C2=A0seemingly dominated=C2=A0by the TRUNCATE.</div>=
<div><br></div><div>My questions are;</div><div><br></div><div>1) Is the VA=
CUUM 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 b=
est to just VACUUM the temporary tables occasionally?</div><div>3) Is there=
 a better way of managing all this!? Perhaps re-CREATING the TT every day o=
r something?</div><div><br></div><div>I understand that I can create a Linu=
x tmpfs partition for a tablespace for the temporary tables and that may sp=
eed 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 hel=
p,</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><spa=
n class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"=
gmail_signature"><div dir=3D"ltr">Jim Vanns<br>Principal Production Enginee=
r<br>Industrial Light &amp; Magic, London</div></div></div></blockquote></d=
iv></div>

--00000000000010db9f061e8f41a1--