Re: Impact of multixact "members" limit exceeded

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