MIME-Version: 1.0
From: Jim Vanns <jvanns@ilm.com>
Date: Fri, 9 Aug 2024 11:26:49 +0100
Message-ID: <CAH7vdhNKOxe80kxS2TUQsYDZnx1+=gqSbkiPzqEDKO08uk7mow@mail.gmail.com>
Subject: Trouble understanding how to avoid/manage ERROR: multixact "members"
 limit exceeded
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006898ac061f3d9599"
Archived-At: <https://www.postgresql.org/message-id/CAH7vdhNKOxe80kxS2TUQsYDZnx1%2B%3DgqSbkiPzqEDKO08uk7mow%40mail.gmail.com>
Precedence: bulk

--0000000000006898ac061f3d9599
Content-Type: text/plain; charset="UTF-8"

Hi pggen community!

I am struggling with this error almost daily now and despite various
efforts, not succeeding in avoiding or dealing with it;

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.

Runtime details follow below, but before that; I am now seeing the above
error almost daily after approximately 12 hours of normal or expected
behaviour and throughput. Then it hits and all writes are blocked etc. and
the service is largely unusable/unable to recover. Restarting PG does allow
autovacuum processes to kick in with aggressive vacuuming to handle the
multixact freezing, but that isn't a suitable solution! Although having
read sources that now explain why multixact XIDs exist and when they're
used, I am not able to properly figure out how to plan for it or configure
postgresql appropriately to handle it given our workload.

My questions are;

1) How should I be managing this? Although not new to PG, I am new to this
particular problem.
2) How can I confirm what processes/tables are contributing to this
multixact "members" limit?
3) What are the units for
vacuum_multixact_freeze_min_age/vacuum_multixact_freeze_table_age and how
should I be
    setting them appropriately for my rates etc.? I can't really find
anything that explains this clearly.
4) How can I check that autovacuum workers are specifically able to freeze
multixact XIDs and thus avoid this?
5) Can I check if autovacuum is actually succeeding in its work?

Schema (brief):
10 tables
1 table (the 'hottest) partitioned by 2h ranges; so an extra 12x
partitions/relations a day, though only the most recent one might be
considered 'active'

System (brief):
PG: 15.5 w/ TimescaleDB 2.14 extension
Write heavy workload;
Mean Txn/s (low):   8k
Mean Txn/s (high): 10k
Mean rows/s: 100k
Concurrency: 32 threads (local socket sessions) for 'upserts' via primary
service plus auxiliary processes (background workers/autovacuum workers
etc.)

Pattern (brief):
COPY (binary) to session-local temporary tables (ON COMMIT DELETE)
INSERT FROM tt TO main_table(s)
UPDATE FROM tt TO main_table(s)
VACUUM tt (every 100k txns)

Config (excerpt):
# - Transactions - (based on a period mean of ~8k txn/s)
# See/ref;
# www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
#
www.percona.com/blog/tuning-autovacuum-in-postgresql-and-autovacuum-internals
# blog.sentry.io/transaction-id-wraparound-in-postgres
#
https://blog.danslimmon.com/2023/12/11/concurrent-locks-and-multixacts-in-postgres
#
https://pganalyze.com/blog/5mins-postgres-multiXact-ids-foreign-keys-performance
# *What unit is 'age' here? No. of. transactions?*
vacuum_freeze_min_age = 28800000 # 1h @ 8k/s
vacuum_multixact_freeze_min_age = 28800000 # 1h @ 8k/s
autovacuum_freeze_max_age = 157600000 # 2h @ 8k/s
autovacuum_multixact_freeze_max_age = 57600000 # 2h @ 8k/s
vacuum_multixact_freeze_table_age = 115200000 # 4h @ 8k/s

# We don't UPDATE or DELETE often; we never DELETE and only perform
# UPDATE operations every 6h due to internal cache(s). So we set this to
# zero so the formula doesn't consider it a major factor
autovacuum_vacuum_threshold = 0 # In tuples/rows UPDATEd or DELETEd
autovacuum_vacuum_insert_threshold = 5000 # In tuples/rows INSERTed

autovacuum_vacuum_scale_factor = 0.1 # 10%
autovacuum_analyze_scale_factor = 0.1 # 10%
autovacuum_vacuum_insert_scale_factor = 0.1 # 10%

autovacuum_naptime = 60
autovacuum_max_workers = 8

# Give autovacuum more credits to ensure a better chance at scanning
autovacuum_vacuum_cost_limit = 2000 # 10x the default

Thanks for your help and any guidance/knowledge you can share!

Jim

-- 
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

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

<div dir=3D"ltr">Hi pggen community!<div><br></div><div>I am struggling wit=
h this error almost daily now and despite various efforts, not succeeding i=
n avoiding or dealing with it;</div><div><br></div><div>ERROR: =C2=A0multix=
act &quot;members&quot; limit exceeded</div><div>DETAIL: =C2=A0This command=
 would create a multixact with 2 members, but the remaining space is only e=
nough for 0 members.</div><div>HINT: =C2=A0Execute a database-wide VACUUM i=
n database with OID 16467 with reduced vacuum_multixact_freeze_min_age and =
vacuum_multixact_freeze_table_age settings.<br clear=3D"all"><div><br></div=
><div>Runtime details follow below, but before that; I am now seeing the ab=
ove error almost daily after approximately 12 hours of normal or expected b=
ehaviour and throughput. Then it hits and all writes are blocked etc. and t=
he service is largely unusable/unable to recover. Restarting PG does allow =
autovacuum processes to kick in with aggressive vacuuming to handle the mul=
tixact freezing, but that isn&#39;t a suitable=C2=A0solution! Although havi=
ng read sources that now explain why multixact XIDs exist and when they&#39=
;re used, I am not able to properly figure out how to plan for it or config=
ure postgresql appropriately to handle it given our workload.</div><div><br=
></div><div>My questions are;</div><div><br></div><div>1) How should I be m=
anaging this? Although not new to PG, I am new to this particular problem.<=
/div><div>2) How can I confirm what processes/tables are contributing to th=
is multixact &quot;members&quot; limit?</div><div>3) What are the units for=
 vacuum_multixact_freeze_min_age/vacuum_multixact_freeze_table_age and how =
should I be=C2=A0</div><div>=C2=A0 =C2=A0 setting them appropriately for my=
 rates etc.? I can&#39;t really find anything that explains this clearly.</=
div><div>4) How can I check that autovacuum workers are specifically able t=
o freeze multixact XIDs and thus avoid this?</div><div>5) Can I check if au=
tovacuum is actually succeeding in its work?</div><div><br></div><div>Schem=
a (brief):</div><div>10 tables</div><div>1 table (the &#39;hottest) partiti=
oned=C2=A0by 2h ranges; so an extra 12x partitions/relations a day, though =
only the most recent one might be considered &#39;active&#39;</div><div><br=
></div><div>System (brief):</div><div>PG: 15.5 w/ TimescaleDB 2.14 extensio=
n<br></div><div>Write heavy workload;<br></div><div>Mean Txn/s (low):=C2=A0=
 =C2=A08k</div><div>Mean Txn/s (high): 10k</div><div>Mean rows/s: 100k</div=
><div>Concurrency: 32 threads (local socket sessions) for &#39;upserts&#39;=
 via primary service plus auxiliary=C2=A0processes (background workers/auto=
vacuum workers etc.)</div><div><br></div><div>Pattern (brief):</div><div>CO=
PY (binary) to session-local temporary tables (ON COMMIT DELETE)</div><div>=
INSERT FROM tt TO main_table(s)</div><div>UPDATE FROM tt TO main_table(s)</=
div><div>VACUUM tt (every 100k txns)</div><div><br></div><div>Config (excer=
pt):</div><div># - Transactions - (based on a period mean of ~8k txn/s)<br>=
# See/ref;<br># <a href=3D"http://www.postgresql.org/docs/15/routine-vacuum=
ing.html#VACUUM-FOR-WRAPAROUND">www.postgresql.org/docs/15/routine-vacuumin=
g.html#VACUUM-FOR-WRAPAROUND</a><br># <a href=3D"http://www.percona.com/blo=
g/tuning-autovacuum-in-postgresql-and-autovacuum-internals">www.percona.com=
/blog/tuning-autovacuum-in-postgresql-and-autovacuum-internals</a><br># <a =
href=3D"http://blog.sentry.io/transaction-id-wraparound-in-postgres">blog.s=
entry.io/transaction-id-wraparound-in-postgres</a></div><div># <a href=3D"h=
ttps://blog.danslimmon.com/2023/12/11/concurrent-locks-and-multixacts-in-po=
stgres">https://blog.danslimmon.com/2023/12/11/concurrent-locks-and-multixa=
cts-in-postgres</a><br># <a href=3D"https://pganalyze.com/blog/5mins-postgr=
es-multiXact-ids-foreign-keys-performance">https://pganalyze.com/blog/5mins=
-postgres-multiXact-ids-foreign-keys-performance</a><br></div><div># <b>Wha=
t unit is &#39;age&#39; here? No. of. transactions?</b><br>vacuum_freeze_mi=
n_age =3D 28800000 # 1h @ 8k/s<br>vacuum_multixact_freeze_min_age =3D 28800=
000 # 1h @ 8k/s<br>autovacuum_freeze_max_age =3D 157600000 # 2h @ 8k/s<br>a=
utovacuum_multixact_freeze_max_age =3D 57600000 # 2h @ 8k/s<br>vacuum_multi=
xact_freeze_table_age =3D 115200000 # 4h @ 8k/s<br><br># We don&#39;t UPDAT=
E or DELETE often; we never DELETE and only perform<br># UPDATE operations =
every 6h due to internal cache(s). So we set this=C2=A0to</div><div># zero =
so the formula doesn&#39;t consider it a major factor<br>autovacuum_vacuum_=
threshold =3D 0 # In tuples/rows UPDATEd or DELETEd<br>autovacuum_vacuum_in=
sert_threshold =3D 5000 # In tuples/rows INSERTed<br><br>autovacuum_vacuum_=
scale_factor =3D 0.1 # 10%<br>autovacuum_analyze_scale_factor =3D 0.1 # 10%=
<br>autovacuum_vacuum_insert_scale_factor =3D 0.1 # 10%<br><br></div><div>a=
utovacuum_naptime =3D 60<br></div><div>autovacuum_max_workers =3D 8</div><d=
iv><br># Give autovacuum more credits to ensure a better chance at scanning=
</div><div>autovacuum_vacuum_cost_limit =3D 2000 # 10x the default<br></div=
><div><br></div><div>Thanks for your help and any guidance/knowledge you ca=
n share!</div><div><br></div><div>Jim<br></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr">Jim Vanns<br>=
Principal Production Engineer<br>Industrial Light &amp; Magic, London</div>=
</div></div></div>

--0000000000006898ac061f3d9599--