Re: finding tables about to be vacuum freezed

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Tue, May 5, 2015 at 6:40 PM, Steve Kehlet <steve.kehlet@xxxxxxxxx> 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