BUG #16098: unexplained autovacuum to prevent wraparound

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16098: unexplained autovacuum to prevent wraparound

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      16098
Logged by:          Alessandro Ferraresi
Email address:      [hidden email]
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

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16098: unexplained autovacuum to prevent wraparound

Jeff Janes




 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
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16098: unexplained autovacuum to prevent wraparound

Alessandro Ferraresi
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 <[hidden email]> ha scritto:




 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
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16098: unexplained autovacuum to prevent wraparound

Jeff Janes
On Thu, Nov 7, 2019 at 11:39 AM Alessandro Ferraresi <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16098: unexplained autovacuum to prevent wraparound

Alessandro Ferraresi

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 <[hidden email]> ha scritto:
On Thu, Nov 7, 2019 at 11:39 AM Alessandro Ferraresi <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16098: unexplained autovacuum to prevent wraparound

Jeff Janes
On Thu, Nov 7, 2019 at 12:11 PM Alessandro Ferraresi <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16098: unexplained autovacuum to prevent wraparound

Alessandro Ferraresi
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 <[hidden email]> ha scritto:
On Thu, Nov 7, 2019 at 12:11 PM Alessandro Ferraresi <[hidden email]> 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