Обсуждение: BUG #16098: unexplained autovacuum to prevent wraparound
The following bug has been logged on the website: Bug reference: 16098 Logged by: Alessandro Ferraresi Email address: alessandro.ferraresi1@gmail.com PostgreSQL version: 10.6 Operating system: RHEL 7 Description: Hello there, I'm experiencing a weird behavior on 10.6 where I see autovacuum running to prevent wraparound even if autovacuum_freeze_max_age is far away. The database is brand-new and some tables have been loaded (using copy command), below some details/parameters: pgxxxx=> show autovacuum_freeze_max_age; autovacuum_freeze_max_age --------------------------- 200000000 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac --------------------+----------------------------+----------------------------------- 3210 | 0 | 0 The tables involved have these custom parameters: Options: fillfactor=80, autovacuum_enabled=true, autovacuum_vacuum_scale_factor=0.00, autovacuum_analyze_scale_factor=0.00, autovacuum_vacuum_threshold=500000, autovacuum_analyze_threshold=50000 From pg stat activity: age | state | backend_type query -----------------------+--------+---------------------------+--------------------------------------------------------------------------------- 00:18:32.899444 | active | autovacuum worker | autovacuum: VACUUM haas.table1 (to prevent wraparound) 00:26:57.660416 | active | autovacuum worker | autovacuum: VACUUM haas.table2 (to prevent wraparound) 00:26:54.848344 | active | autovacuum worker | autovacuum: VACUUM haas.table3 (to prevent wraparound) What could be the root cause of this? I believe this is a bug as I can't find any other explanation. Thanks Alessandro
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:
oldest_current_xid | percent_towards_wraparound |
percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
3210 | 0 |
0What is this the output of? This doesn't look like any of the built-in system views.Cheers,Jeff
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
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
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 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
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
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 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
-------------------------
150000000I 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
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