On Tue, May 5, 2015 at 6:40 PM, Steve Kehlet <steve.keh...@gmail.com> wrote:
> Hello, recently one of my tables needed a vacuum (to prevent wraparound) > and of course it happened at a really bad time, so since then I've been > learning about how Transaction ID Wraparound works and its associated > parameters. > > I'm trying this query to see how close my tables are to hitting the > vacuum_freeze_table_age threshold (150M in my case): > > SELECT > relname, > age(relfrozenxid) as xid_age, > ROUND(100.0 * age(relfrozenxid) / > current_setting('vacuum_freeze_table_age')::numeric, 1) || '%' AS "% til > vacuum freeze" > FROM > pg_class > WHERE relkind = 'r'; > > For now, assume my tables have no storage parameters that override the > defaults. > > I was surprised at the results, almost all my tables look like: > > my_table | 160589343 | 107.1% > > Or about 160m transactions old. I would have thought with my current > settings: > vacuum_freeze_min_age = 50m > vacuum_freeze_table_age = 150m > autovacuum_freeze_max_age = 200m > > that the autovacuumer would have already forced a vacuum freeze on all > these tables. According to the docs, "a whole table sweep is forced if the > table hasn't been fully scanned for vacuum_freeze_table_age minus > vacuum_freeze_min_age transactions" which would be 100m transactions. > > I'm guessing my understanding here is wrong. What did I miss? > vacuum_freeze_table_age controls when it promotes a vacuum *which is already going to occur* so that it scans the whole table. It doesn't specially schedule a vacuum to occur. When those tables see enough activity to pass autovacuum_vacuum_scale_factor then the vacuum that occurs will get promoted to be a full scan. If they never see that amount of activity, then the tables will continue to age until autovacuum_freeze_max_age, at which point a vacuum will get launched specifically for the purpose of advancing relfrozenxid. Cheers, Jeff