Suggestions to overcome 'multixact "members" limit exceeded' in temporary tables
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 FROM STDIN in binary format Then an INSERT INTO SELECT FROM 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 100 (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
Re: Suggestions to overcome 'multixact "members" limit exceeded' in temporary tables
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 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 FROM STDIN in binary format > Then an INSERT INTO SELECT FROM 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 > 100 (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
BitmapOr node not used in plan for ANY/IN but is for sequence of ORs ...
(sent to general users mailing list yesterday - but perhaps this is a more suitable audience?) In PG16.4, we have a table of key/pair data (around 30M rows) where there are about 7 distinct keys and each has a conditional or partial index on them (the distribution is different for each key/value pair combination). I've found that when we have a query that uses an OR then those partial indexes are used but not if the query is written to use ANY/IN, which is more convenient from a programmer POV (especially any with 3rd party query generators etc.). Naturally, the result sets returned by the queries are identical due to the filter semantics of any of the 3 solution variants. Here's a shareable, MRP; https://dbfiddle.uk/OKs_7HWv Is there any trick I can do to get the planner to make use of the conditional/partial index? Or is this simply an unoptimised code path yet to be exploited!? Cheers, Jim -- Jim Vanns Principal Production Engineer Industrial Light & Magic, London
Re: BitmapOr node not used in plan for ANY/IN but is for sequence of ORs ...
Thanks Tomas, that's useful to know. Cheers Jim On Wed, 13 Nov 2024 at 13:13, Tomas Vondra wrote: > On 11/13/24 13:08, Jim Vanns wrote: > > (sent to general users mailing list yesterday - but perhaps this is a > > more suitable audience?) > > > > In PG16.4, we have a table of key/pair data (around 30M rows) where > > there are about 7 distinct keys and each has a conditional or partial > > index on them (the distribution is different for each key/value pair > > combination). I've found that when we have a query that uses an OR then > > those partial indexes are used but not if the query is written to use > > ANY/IN, which is more convenient from a programmer POV (especially any > > with 3rd party query generators etc.). Naturally, the result sets > > returned by the queries are identical due to the filter semantics of any > > of the 3 solution variants. > > > > Here's a shareable, MRP; > > > > https://dbfiddle.uk/OKs_7HWv <https://dbfiddle.uk/OKs_7HWv> > > > > Is there any trick I can do to get the planner to make use of the > > conditional/partial index? Or is this simply an unoptimised code path > > yet to be exploited!? > > > > I believe this is "simply" not implemented, so there's no way to > convince the planner to use these partial indexes. > > The proximate cause is that the planner does not treat ANY()/IN() as > equivalent to an OR clause, and does not even consider building the > "bitmap OR" path for those queries. That's what happens at the very > beginning of generate_bitmap_or_paths(). > > Perhaps we could "expand" the ANY/IN clauses into an OR clause, so that > restriction_is_or_clause() returns "true". But I haven't tried and I'm > sure there'd be more stuff to fix to make this work. > > > regards > > -- > Tomas Vondra > > -- Jim Vanns Principal Production Engineer Industrial Light & Magic, London
