Обсуждение: BUG #16098: unexplained autovacuum to prevent wraparound

 oldest_current_xid | percent_towards_wraparound |
percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
               3210 |                          0 |                         
      0

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

On Thu, Nov 7, 2019 at 11:39 AM Alessandro Ferraresi <alessandro.ferraresi1@gmail.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

Here you go:

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

Autovacuum to prevent wrap around kicks in on those table, I was actually wondering if this is somehow related to autovacuum_multixact_freeze_max_age parameter and how. That behavior should be referred to row locking on multiple transaction but this database is still static and not accessible yet, meaning there shouldn't be row level concurrency. For completeness, the load for each table is executed by 40 parallel sessions running the copy command.