pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #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>
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:46 Jeff Janes <jeff.janes@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
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 16:39 Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
parent: 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 17:00 Jeff Janes <jeff.janes@gmail.com>
parent: 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 17:11 Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
parent: 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-08 16:11 Jeff Janes <jeff.janes@gmail.com>
parent: 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-08 19:37 Alessandro Ferraresi <alessandro.ferraresi1@gmail.com>
parent: 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-12-29 21:17 Jeff Janes <jeff.janes@gmail.com>
parent: Alessandro Ferraresi <alessandro.ferraresi1@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