Re: BUG #16098: unexplained autovacuum to prevent wraparound

On Thu, Nov 7, 2019 at 11:39 AM Alessandro Ferraresi <
alessandro(dot)ferraresi1(at)gmail(dot)com> wrote:

> That's the output of the following query to check the progress of XID to
> autovacuum_freeze_max_age:
>
> WITH max_age AS (
> SELECT 2000000000 as max_old_xid
> , setting AS autovacuum_freeze_max_age
> FROM pg_catalog.pg_settings
> WHERE name = 'autovacuum_freeze_max_age' ), per_database_stats AS (
> SELECT datname
> , m.max_old_xid::int
> , m.autovacuum_freeze_max_age::int
> , age(d.datfrozenxid) AS oldest_current_xid
> FROM pg_catalog.pg_database d
> JOIN max_age m ON (true)
> WHERE d.datallowconn ) SELECT max(oldest_current_xid) AS oldest_current_xid
> , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
> , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac FROM per_database_stats
>
>
What is your setting of vacuum_freeze_table_age? That is the point where a
regularly scheduled vacuum will get promoted to a wraparound vacuum. What
if you delete the "WHERE d.datallowcon", and then replace the last 4 lines
with "SELECT * from per_database_stats?

Cheers,

Jeff

>