Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded
On Tue, May 17, 2016 at 10:40 AM Alvaro Herrerawrote: > 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. Thank you Alvaro. We're beginning to plan our rollout of 9.5.
Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded
Steve Kehlet wrote: > On Mon, May 16, 2016 at 6:18 PM Alvaro Herrera> wrote: > > > Not really. Your best bet is to reduce the > > autovacuum_multixact_freeze_min_age limit, so that vacuums are able to > > get rid of multixacts sooner (and/or reduce > > autovacuum_multixact_freeze_table_age, so that whole-table vacuuming > > takes place earlier). > > Thank you very much. I will adjust those settings. Is there a way, > something similar to keeping an eye on `age(relfrozenxid)`, that I can > watch this and keep an eye on it before it becomes a problem? 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. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded
On Mon, May 16, 2016 at 6:18 PM Alvaro Herrerawrote: > Not really. Your best bet is to reduce the > autovacuum_multixact_freeze_min_age limit, so that vacuums are able to > get rid of multixacts sooner (and/or reduce > autovacuum_multixact_freeze_table_age, so that whole-table vacuuming > takes place earlier). Thank you very much. I will adjust those settings. Is there a way, something similar to keeping an eye on `age(relfrozenxid)`, that I can watch this and keep an eye on it before it becomes a problem?
Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded
Steve Kehlet wrote: > Now it's just about preventing this. Our best guess at this point is the > autovacuums aren't working fast enough. Sure enough this instance has our > old values for: > autovacuum_vacuum_cost_delay: 20ms > autovacuum_vacuum_cost_limit: 200 > > We've since started using: > autovacuum_vacuum_cost_delay: 10ms > autovacuum_vacuum_cost_limit: 2000 > > We'll be updating those settings as soon as possible. > > Just looking for some expert eyes on this problem. Are we on the track > track? I.e. is making the autovacuumer run more aggressively our best bet > to avoid this issue? Not really. Your best bet is to reduce the autovacuum_multixact_freeze_min_age limit, so that vacuums are able to get rid of multixacts sooner (and/or reduce autovacuum_multixact_freeze_table_age, so that whole-table vacuuming takes place earlier). You may need to decrease the cost_delay too (and/or increase the cost_limit) in order for autovac to be able to keep up, but really that's more a side effect because of a possible need for autovac to do more in the same period of time. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] preventing ERROR: multixact "members" limit exceeded
This is Postgres 9.4.4. Custom settings are [in this gist]( https://gist.github.com/skehlet/47c7f92daa0bd3d1a3aee2bb001da140). This is a new one for me, one of our bigger (~2.2TB) databases started having the following error: > Caused by: org.postgresql.util.PSQLException: 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 33554 with reduced vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age settings. We followed those VACUUM instructions, and now everything's back to normal. Now it's just about preventing this. Our best guess at this point is the autovacuums aren't working fast enough. Sure enough this instance has our old values for: autovacuum_vacuum_cost_delay: 20ms autovacuum_vacuum_cost_limit: 200 We've since started using: autovacuum_vacuum_cost_delay: 10ms autovacuum_vacuum_cost_limit: 2000 We'll be updating those settings as soon as possible. Just looking for some expert eyes on this problem. Are we on the track track? I.e. is making the autovacuumer run more aggressively our best bet to avoid this issue? Thank you, Steve