Обсуждение: Multixact wraparound monitoring
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
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.
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)
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
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)