Horton

Thread

  1. Multixact wraparound monitoring

    bruno vieira da silva <brunogiovs@gmail.com> — 2023-09-13T12:21:40Z

    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

  2. Re: Multixact wraparound monitoring

    Wyatt Alt <wyatt.alt@gmail.com> — 2023-09-14T05:14:28Z

    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.

  3. Re: Multixact wraparound monitoring

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-09-14T11:23:41Z

    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)

  4. Re: Multixact wraparound monitoring

    bruno vieira da silva <brunogiovs@gmail.com> — 2023-09-14T19:35:10Z

    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

  5. Re: Multixact wraparound monitoring

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-09-15T09:48:47Z

    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)