Hello!
I have a question where I'm trying to determine the seriousness of the problem.
I repacked a table to give space back to the OS and that was fine. However since then I just noted that I see multixact "members" limit exceeded every time there is an insert on that table. A Vacuum has yet to complete on this table.
However looking into the tables we seem to have correct data in it and table age is not that high. So there does not seem that any data is corrupted or getting or getting overwritten and that new data is written fine into the table.
Vacuum is behind and I know that is an issue, this is a very transaction heavy database with long running transactions that does not release and I'm working on getting the vacuum through.
When looking in the the pg lists it seems that everyone agrees that this is not a good thing.
Álvaro Herrera:https://www.postgresql.org/message-id/20160517174055.GA158915%40alvherre.pgsql
"In 9.4, not really. In 9.5 there's a function mxid_age() that gives you
the age of a multixact, so you'd grab the oldest from
pg_database.datminmxid and compute the age of that one. Going from the
oldest multi to the oldest offset cannot be done without an additional
function, however. It's much easier to keep track of the oldest file in
$PGDATA/pg_multixact/members/; what you really need to care about is the
size of the "hole" between the newest and the oldest files there. Once
newest starts to stomp on oldest, you're screwed."
Now I'm not sure what this Hole is as new files have stopped being written altogether.
I also read this answer by Anders Fredund:
https://www.postgresql.org/message-id/20170809175728.opnxie26gtpsrjhk%40alap3.anarazel.de
"> We started feeding it several weeks ago and everything went smoothly until
> we hit this issue:
>
> 2017-08-09 05:21:50.946 WIB >DETAIL: This command would create a multixact
> with 2 members, but the remaining space is only enough for 0 members.
> 2017-08-09 05:21:50.946 WIB >HINT: Execute a database-wide VACUUM in
> database with OID 20101 with reduced vacuum_multixact_freeze_min_age and
> vacuum_multixact_freeze_table_age settings.
Ugh, that's not good."
But i still can't figure out what the actual impact would be?
As for Vacuum settings they are as follows:
vacuum_freeze_min_age = 10000000
autovacuum_freeze_max_age = 600000000
vacuum_freeze_table_age = 400000000
vacuum_cost_delay = 10
vacuum_cost_limit = 2000
autovacuum_vacuum_cost_limit = 200
autovacuum_vacuum_cost_delay = 20
vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age are the default 5/150 million.
Best regards
Daniel
I have a question where I'm trying to determine the seriousness of the problem.
I repacked a table to give space back to the OS and that was fine. However since then I just noted that I see multixact "members" limit exceeded every time there is an insert on that table. A Vacuum has yet to complete on this table.
However looking into the tables we seem to have correct data in it and table age is not that high. So there does not seem that any data is corrupted or getting or getting overwritten and that new data is written fine into the table.
Vacuum is behind and I know that is an issue, this is a very transaction heavy database with long running transactions that does not release and I'm working on getting the vacuum through.
When looking in the the pg lists it seems that everyone agrees that this is not a good thing.
Álvaro Herrera:https://www.postgresql.org/message-id/20160517174055.GA158915%40alvherre.pgsql
"In 9.4, not really. In 9.5 there's a function mxid_age() that gives you
the age of a multixact, so you'd grab the oldest from
pg_database.datminmxid and compute the age of that one. Going from the
oldest multi to the oldest offset cannot be done without an additional
function, however. It's much easier to keep track of the oldest file in
$PGDATA/pg_multixact/members/; what you really need to care about is the
size of the "hole" between the newest and the oldest files there. Once
newest starts to stomp on oldest, you're screwed."
Now I'm not sure what this Hole is as new files have stopped being written altogether.
I also read this answer by Anders Fredund:
https://www.postgresql.org/message-id/20170809175728.opnxie26gtpsrjhk%40alap3.anarazel.de
"> We started feeding it several weeks ago and everything went smoothly until
> we hit this issue:
>
> 2017-08-09 05:21:50.946 WIB >DETAIL: This command would create a multixact
> with 2 members, but the remaining space is only enough for 0 members.
> 2017-08-09 05:21:50.946 WIB >HINT: Execute a database-wide VACUUM in
> database with OID 20101 with reduced vacuum_multixact_freeze_min_age and
> vacuum_multixact_freeze_table_age settings.
Ugh, that's not good."
But i still can't figure out what the actual impact would be?
As for Vacuum settings they are as follows:
vacuum_freeze_min_age = 10000000
autovacuum_freeze_max_age = 600000000
vacuum_freeze_table_age = 400000000
vacuum_cost_delay = 10
vacuum_cost_limit = 2000
autovacuum_vacuum_cost_limit = 200
autovacuum_vacuum_cost_delay = 20
vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age are the default 5/150 million.
Best regards
Daniel
