public inbox for [email protected]
help / color / mirror / Atom feedSuggestions to overcome 'multixact "members" limit exceeded' in temporary tables
2+ messages / 1 participants
[nested] [flat]
* Suggestions to overcome 'multixact "members" limit exceeded' in temporary tables
@ 2024-07-31 14:16 Jim Vanns <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Jim Vanns @ 2024-07-31 14:16 UTC (permalink / raw)
To: PostgreSQL-development <[email protected]>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Suggestions to overcome 'multixact "members" limit exceeded' in temporary tables
@ 2024-07-31 15:41 Jim Vanns <[email protected]>
parent: Jim Vanns <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Jim Vanns @ 2024-07-31 15:41 UTC (permalink / raw)
To: PostgreSQL-development <[email protected]>
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 15:16, Jim Vanns <[email protected]> wrote:
> 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
>
--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-07-31 15:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-31 14:16 Suggestions to overcome 'multixact "members" limit exceeded' in temporary tables Jim Vanns <[email protected]>
2024-07-31 15:41 ` Jim Vanns <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox