Re: BUG #16098: unexplained autovacuum to prevent wraparound

Here you go:

pgxxxxx=> show vacuum_freeze_table_age;
vacuum_freeze_table_age
-------------------------
150000000

pgha1nac=> 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) )
SELECT * from per_database_stats;
datname | max_old_xid | autovacuum_freeze_max_age | oldest_current_xid
-----------+-------------+---------------------------+--------------------
template0 | 2000000000 | 200000000 | 154
rdsadmin | 2000000000 | 200000000 | 3275
template1 | 2000000000 | 200000000 | 154
postgres | 2000000000 | 200000000 | 3275
pgxxxxx | 2000000000 | 200000000 | 3275

Thanks
Alessandro

Il giorno gio 7 nov 2019 alle ore 17:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
ha scritto:

> 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
>
>>