Re: Impact of multixact "members" limit exceeded

> Did you observe whether the vacuumed tables' relminmxid advance? If it
> didn't, then those vacuums are a waste of time.
>
> Note that freeze_table_age affects when vacuum does a full table scan
> vs. when it skips pages due to visibility map. A vacuum that does a
> partial scan does *not* advance the relfrozenxid / relminmxid; only
> full-table scan vacuums can do that. (Except in 9.6.) If the
> relminmxid ages are younger than your 100 million table_age, vacuum
> won't do a full-table scan.
>

Yes they did, they all advanced, and no they are not younger than 100
million. They were all over the default value of 150 as are the remaining
2, relminxid/relfrozenid advanced, relminxid to match NextMultiXactID and
age went down. But good point to keep in mind.

Best regards

Daniel

On Thu, Jun 14, 2018 at 7:15 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> On 2018-Jun-14, Daniel Lagerman wrote:
>
> > > Set vacuum_multixact_freeze_table_age to 0 (causing all vacuums to
> scan
> > > tables fully) and vacuum_multixact_freeze_min_age to some value like
> one
> > > million (so that they remove most of the oldest multixacts, leaving
> > > just the frontmost one million). Then vacuum the tables with the
> > > highest multixact ages. Make sure the datminmxid advances in all
> > > databases (incl. postgres template0 template1); once it has advanced,
> > > the oldest member files are removed. Don't waste time processing
> tables
> > > with datminmxid higher than the minimum, as that won't free up any
> > > member space.
> >
> > Thanks, we opted to go with 1 million min and 100 million table age, the
> > tables in question are all way over that anyway. We completed vacuum on
> all
> > but two which are larger tables and I'm running Vacuum there right now
> > after I also bumped the maintenance work mem a bit. As this is 9.4 I
> can't
> > see the progress except that I noted that initialy it uses less CPU and
> > once it seems to finnaly start working it uses more CPU 10%->50% but that
> > is fine. So my hope is that the Vacuum completes over night.
>
> Did you observe whether the vacuumed tables' relminmxid advance? If it
> didn't, then those vacuums are a waste of time.
>
> Note that freeze_table_age affects when vacuum does a full table scan
> vs. when it skips pages due to visibility map. A vacuum that does a
> partial scan does *not* advance the relfrozenxid / relminmxid; only
> full-table scan vacuums can do that. (Except in 9.6.) If the
> relminmxid ages are younger than your 100 million table_age, vacuum
> won't do a full-table scan.
>
> Cheers
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>