BUG #16098: unexplained autovacuum to prevent wraparound

oldest_current_xid | percent_towards_wraparound |
percent_towards_emergency_autovac

--------------------+----------------------------+-----------------------------------
3210 | 0 |

0

What is this the output of? This doesn't look like any of the built-in
system views.

Cheers,

Jeff

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

Thanks
Alessandro

Il giorno gio 7 nov 2019 alle ore 15:46 Jeff Janes <jeff.janes@gmail.com>
ha scritto:

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

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.janes@gmail.com>
ha scritto:

On Thu, Nov 7, 2019 at 12:11 PM Alessandro Ferraresi <
alessandro.ferraresi1@gmail.com> wrote:

Here you go:

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

I can't think of any other innocent explanations, so maybe it is a bug.
But if it were easy to hit, I'd expect it to have been noticed before now.

Can you reproduce it readily?

Cheers,

Jeff

I can try reproducing it, I didn't quite get what is actually triggering
that. Let me share some more details:

Postgres instance is getting loaded of 4 big tables (not at the same time,
sequentially):

Table1: 2.5B rows
Table2: 2.2B rows
Table3: 7.7B rows
Table4: 1.3B rows

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.

pgxxxxx=> show autovacuum_multixact_freeze_max_age;
autovacuum_multixact_freeze_max_age
-------------------------------------
400000000

Thanks
Alessandro

Il giorno ven 8 nov 2019 alle ore 16:12 Jeff Janes <jeff.janes@gmail.com>
ha scritto:

On Fri, Nov 8, 2019 at 2:37 PM Alessandro Ferraresi <
alessandro.ferraresi1@gmail.com> wrote:

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.

I would think 40 parallel sessions loading data would exactly trigger
multixact, if you have foreign key constraints in place at the time. Each
parent row has be locked by each parallel session, to make sure the parent
row doesn't go away (or have the key changed) while the child insert is
happening.

Cheers,

Jeff