pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16098: unexplained autovacuum to prevent wraparound
8+ messages / 3 participants
[nested] [flat]

* BUG #16098: unexplained autovacuum to prevent wraparound
@ 2019-11-07 15:06 PG Bug reporting form <noreply@postgresql.org>
  2019-11-07 15:46 ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: PG Bug reporting form @ 2019-11-07 15:06 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: alessandro.ferraresi1@gmail.com

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16098: unexplained autovacuum to prevent wraparound
  2019-11-07 15:06 BUG #16098: unexplained autovacuum to prevent wraparound PG Bug reporting form <noreply@postgresql.org>
@ 2019-11-07 15:46 ` Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 16:39   ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Jeff Janes @ 2019-11-07 15:46 UTC (permalink / raw)
  To: PG Bug reporting form <noreply@postgresql.org>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>; alessandro.ferraresi1@gmail.com

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16098: unexplained autovacuum to prevent wraparound
  2019-11-07 15:06 BUG #16098: unexplained autovacuum to prevent wraparound PG Bug reporting form <noreply@postgresql.org>
  2019-11-07 15:46 ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
@ 2019-11-07 16:39   ` Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  2019-11-07 17:00     ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Alessandro Ferraresi @ 2019-11-07 16:39 UTC (permalink / raw)
  To: Jeff Janes <jeff.janes@gmail.com>; +Cc: PG Bug reporting form <noreply@postgresql.org>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

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 |
>>
>>       0
>>
>
> What is this the output of?  This doesn't look like any of the built-in
> system views.
>
> Cheers,
>
> Jeff
>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16098: unexplained autovacuum to prevent wraparound
  2019-11-07 15:06 BUG #16098: unexplained autovacuum to prevent wraparound PG Bug reporting form <noreply@postgresql.org>
  2019-11-07 15:46 ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 16:39   ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
@ 2019-11-07 17:00     ` Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 17:11       ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Jeff Janes @ 2019-11-07 17:00 UTC (permalink / raw)
  To: Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>; +Cc: PG Bug reporting form <noreply@postgresql.org>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

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

>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16098: unexplained autovacuum to prevent wraparound
  2019-11-07 15:06 BUG #16098: unexplained autovacuum to prevent wraparound PG Bug reporting form <noreply@postgresql.org>
  2019-11-07 15:46 ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 16:39   ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  2019-11-07 17:00     ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
@ 2019-11-07 17:11       ` Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  2019-11-08 16:11         ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Alessandro Ferraresi @ 2019-11-07 17:11 UTC (permalink / raw)
  To: Jeff Janes <jeff.janes@gmail.com>; +Cc: PG Bug reporting form <noreply@postgresql.org>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16098: unexplained autovacuum to prevent wraparound
  2019-11-07 15:06 BUG #16098: unexplained autovacuum to prevent wraparound PG Bug reporting form <noreply@postgresql.org>
  2019-11-07 15:46 ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 16:39   ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  2019-11-07 17:00     ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 17:11       ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
@ 2019-11-08 16:11         ` Jeff Janes <jeff.janes@gmail.com>
  2019-11-08 19:37           ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Jeff Janes @ 2019-11-08 16:11 UTC (permalink / raw)
  To: Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>; +Cc: PG Bug reporting form <noreply@postgresql.org>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

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

>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16098: unexplained autovacuum to prevent wraparound
  2019-11-07 15:06 BUG #16098: unexplained autovacuum to prevent wraparound PG Bug reporting form <noreply@postgresql.org>
  2019-11-07 15:46 ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 16:39   ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  2019-11-07 17:00     ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 17:11       ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  2019-11-08 16:11         ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
@ 2019-11-08 19:37           ` Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  2019-12-29 21:17             ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Alessandro Ferraresi @ 2019-11-08 19:37 UTC (permalink / raw)
  To: Jeff Janes <jeff.janes@gmail.com>; +Cc: PG Bug reporting form <noreply@postgresql.org>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16098: unexplained autovacuum to prevent wraparound
  2019-11-07 15:06 BUG #16098: unexplained autovacuum to prevent wraparound PG Bug reporting form <noreply@postgresql.org>
  2019-11-07 15:46 ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 16:39   ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  2019-11-07 17:00     ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  2019-11-07 17:11       ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
  2019-11-08 16:11         ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Jeff Janes <jeff.janes@gmail.com>
  2019-11-08 19:37           ` Re: BUG #16098: unexplained autovacuum to prevent wraparound Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
@ 2019-12-29 21:17             ` Jeff Janes <jeff.janes@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Jeff Janes @ 2019-12-29 21:17 UTC (permalink / raw)
  To: Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>

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

>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2019-12-29 21:17 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-07 15:06 BUG #16098: unexplained autovacuum to prevent wraparound PG Bug reporting form <noreply@postgresql.org>
2019-11-07 15:46 ` Jeff Janes <jeff.janes@gmail.com>
2019-11-07 16:39   ` Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
2019-11-07 17:00     ` Jeff Janes <jeff.janes@gmail.com>
2019-11-07 17:11       ` Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
2019-11-08 16:11         ` Jeff Janes <jeff.janes@gmail.com>
2019-11-08 19:37           ` Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
2019-12-29 21:17             ` Jeff Janes <jeff.janes@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox