Suggestions to overcome 'multixact "members" limit exceeded' in temporary tables

From: Jim Vanns <jvanns(at)ilm(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Suggestions to overcome 'multixact "members" limit exceeded' in temporary tables
Date: 2024-07-31 14:16:45
Message-ID: CAH7vdhP=7WHj_kgzkinVkpBHGTP521KhVg2g_mPu6imB_kdABA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Vanns 2024-07-31 15:41:51 Re: Suggestions to overcome 'multixact "members" limit exceeded' in temporary tables
Previous Message Alban Hertroys 2024-07-31 06:40:24 Re: Trigger usecase

Browse pgsql-hackers by date

  From Date Subject
Next Message Nathan Bossart 2024-07-31 15:19:01 Re: improve performance of pg_dump with many sequences
Previous Message Tom Lane 2024-07-31 14:11:07 Re: New compiler warnings in buildfarm