Re: finding tables about to be vacuum freezed

On Tue, May 5, 2015 at 6:40 PM, Steve Kehlet <steve(dot)kehlet(at)gmail(dot)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