(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.
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
Principal Production Engineer
Industrial Light & Magic, London
