> 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 <alvhe...@2ndquadrant.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 >
