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
