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 Herrera http://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

On Mon, May 16, 2016 at 6:18 PM Alvaro Herrera <alvherre@2ndquadrant.com>
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?

Steve Kehlet wrote:

On Mon, May 16, 2016 at 6:18 PM Alvaro Herrera <alvherre@2ndquadrant.com>
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 Herrera http://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

On Tue, May 17, 2016 at 10:40 AM Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

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.