Re: preventing ERROR: multixact "members" limit exceeded - Mailing list pgsql-general

From Alvaro Herrera
Subject Re: preventing ERROR: multixact "members" limit exceeded
Date
Msg-id 20160517174055.GA158915@alvherre.pgsql
Whole thread Raw
In response to Re: preventing ERROR: multixact "members" limit exceeded  (Steve Kehlet <steve.kehlet@gmail.com>)
Responses Re: preventing ERROR: multixact "members" limit exceeded  (Steve Kehlet <steve.kehlet@gmail.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Guyren Howe
Date:
Subject: Re: Thoughts on "Love Your Database"
Next
From: Viswanath
Date:
Subject: Re: Update or Delete causes canceling of long running slave queries