pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMultixact wraparound monitoring
5+ messages / 3 participants
[nested] [flat]
* Multixact wraparound monitoring
@ 2023-09-13 12:21 bruno da silva <brunogiovs@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: bruno da silva @ 2023-09-13 12:21 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hello.
I just had an outage on postgres 14 due to multixact members limit exceeded.
So the documentation says "There is a separate storage area which holds the
list of members in each multixact, which also uses a 32-bit counter and
which must also be managed."
Questions:
having a 32-bit counter on this separated storage means that there is a
global limit of multixact IDs for a database OID?
Is there a way to monitor this storage limit or counter using any pg_stat
table/view?
are foreign keys a big source of multixact IDs so not recommended on tables
with a lot of data and a lot of churn?
Thanks
--
Bruno da Silva
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multixact wraparound monitoring
@ 2023-09-14 05:14 Wyatt Alt <wyatt.alt@gmail.com>
parent: bruno da silva <brunogiovs@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Wyatt Alt @ 2023-09-14 05:14 UTC (permalink / raw)
To: bruno da silva <brunogiovs@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Wed, Sep 13, 2023 at 8:29 AM bruno da silva <brunogiovs@gmail.com> wrote:
>
> are foreign keys a big source of multixact IDs so not recommended on
> tables with a lot of data and a lot of churn?
>
I am curious to hear other answers or if anything has changed. I
experienced this problem a couple of times on PG 11. In each situation my
setup looked something like this:
create table small(id int primary key, data text);
create table large(id bigint primary key, small_id int references
small(id));
Table large is hundreds of GB or more and accepting heavy writes in batches
of 1K records, and every batch contains exactly one reference to each row
in small (every batch references the same 1K rows in small).
The only way I was able to get around problems with multixact member ID
wraparound was dropping the foreign key constraint.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multixact wraparound monitoring
@ 2023-09-14 11:23 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: bruno da silva <brunogiovs@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2023-09-14 11:23 UTC (permalink / raw)
To: bruno da silva <brunogiovs@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On 2023-Sep-13, bruno da silva wrote:
> I just had an outage on postgres 14 due to multixact members limit exceeded.
Sadly, that's not as uncommon as we would like.
> So the documentation says "There is a separate storage area which holds the
> list of members in each multixact, which also uses a 32-bit counter and
> which must also be managed."
Right.
> Questions:
> having a 32-bit counter on this separated storage means that there is a
> global limit of multixact IDs for a database OID?
A global limit of multixact members (each multixact ID can have one or
more members), across the whole instance. It is a shared resource for
all databases in an instance.
> Is there a way to monitor this storage limit or counter using any pg_stat
> table/view?
Not at present.
> are foreign keys a big source of multixact IDs so not recommended on tables
> with a lot of data and a lot of churn?
Well, ideally you shouldn't consider operating without foreign keys at
any rate, but yes, foreign keys are one of the most common causes of
multixacts being used, and removing FKs may mean a decrease in multixact
usage. (The other use case of multixact usage is tuples being locked
and updated with an intervening savepoint.)
We could have a mode that we can set on tables with little movement and
many incoming FKs, that tells the system something like "in this table,
deletes/updates are disallowed, so FKs don't need to lock rows". Or
maybe "in this table, deletes are disallowed and updates can only change
columns that aren't used by UNIQUE NOT NULL indexes, so FKs don't need
to lock rows". This might save a ton of multixact traffic.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Hay dos momentos en la vida de un hombre en los que no debería
especular: cuando puede permitírselo y cuando no puede" (Mark Twain)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multixact wraparound monitoring
@ 2023-09-14 19:35 bruno da silva <brunogiovs@gmail.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 5+ messages in thread
From: bruno da silva @ 2023-09-14 19:35 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-performance@lists.postgresql.org
This problem is more acute when the FK Table stores a small number of rows
like types or codes.
I think in those cases an enum type should be used instead of a column with
a FK.
Thanks.
On Thu, Sep 14, 2023 at 7:23 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
> On 2023-Sep-13, bruno da silva wrote:
>
> > I just had an outage on postgres 14 due to multixact members limit
> exceeded.
>
> Sadly, that's not as uncommon as we would like.
>
> > So the documentation says "There is a separate storage area which holds
> the
> > list of members in each multixact, which also uses a 32-bit counter and
> > which must also be managed."
>
> Right.
>
> > Questions:
> > having a 32-bit counter on this separated storage means that there is a
> > global limit of multixact IDs for a database OID?
>
> A global limit of multixact members (each multixact ID can have one or
> more members), across the whole instance. It is a shared resource for
> all databases in an instance.
>
> > Is there a way to monitor this storage limit or counter using any pg_stat
> > table/view?
>
> Not at present.
>
> > are foreign keys a big source of multixact IDs so not recommended on
> tables
> > with a lot of data and a lot of churn?
>
> Well, ideally you shouldn't consider operating without foreign keys at
> any rate, but yes, foreign keys are one of the most common causes of
> multixacts being used, and removing FKs may mean a decrease in multixact
> usage. (The other use case of multixact usage is tuples being locked
> and updated with an intervening savepoint.)
>
>
> We could have a mode that we can set on tables with little movement and
> many incoming FKs, that tells the system something like "in this table,
> deletes/updates are disallowed, so FKs don't need to lock rows". Or
> maybe "in this table, deletes are disallowed and updates can only change
> columns that aren't used by UNIQUE NOT NULL indexes, so FKs don't need
> to lock rows". This might save a ton of multixact traffic.
>
> --
> Álvaro Herrera Breisgau, Deutschland —
> https://www.EnterpriseDB.com/
> "Hay dos momentos en la vida de un hombre en los que no debería
> especular: cuando puede permitírselo y cuando no puede" (Mark Twain)
>
--
Bruno da Silva
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multixact wraparound monitoring
@ 2023-09-15 09:48 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: bruno da silva <brunogiovs@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2023-09-15 09:48 UTC (permalink / raw)
To: bruno da silva <brunogiovs@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On 2023-Sep-14, bruno da silva wrote:
> This problem is more acute when the FK Table stores a small number of rows
> like types or codes.
Right, because the likelihood of multiple transactions creating
new references to the same row is higher.
> I think in those cases an enum type should be used instead of a column with
> a FK.
Right, that alleviates the issue, but IMO it's a workaround whose need
is caused by a deficiency in our implementation.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-09-15 09:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-13 12:21 Multixact wraparound monitoring bruno da silva <brunogiovs@gmail.com>
2023-09-14 05:14 ` Wyatt Alt <wyatt.alt@gmail.com>
2023-09-14 11:23 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-09-14 19:35 ` bruno da silva <brunogiovs@gmail.com>
2023-09-15 09:48 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox