Thread: oldest xmin is far in the past

oldest xmin is far in the past

From
John Snow
Date:
Hi everyone!

Trying to make VACUUM FREEZE on PG instance and keep getting this error:

2016-03-18 05:56:51 UTC   46750 WARNING:  oldest xmin is far in the past
2016-03-18 05:56:51 UTC   46750 HINT:  Close open transactions soon to avoid wraparound problems.
2016-03-18 05:56:51 UTC   46750 DEBUG:  transaction ID wrap limit is 2654342112, limited by database with OID 1
2016-03-18 05:56:51 UTC   46750 DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 12451

Also "age" and "relfrozenxid" doesnt't change.

I will show what I'm trying to do step by step:

Executing this command:
SELECT
pg_namespace.nspname
,c.relname AS relname
--,c.oid::regclass as table_name
,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
,c.relfrozenxid
,t.relfrozenxid
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
LEFT JOIN
pg_namespace
ON pg_namespace.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY
age desc
,1,2;
Output looks like this:
nspname relname age relfrozenxid relfrozenxid
public action_flows 543567979 506858465 506858465
public advertiser_requests 543567979 506858465 506858465
public authtokens 543567979 506858465 506858465
public blacklist 543567979 506858465 506858465
public blog_categories 543567979 506858465 506858465
public blog_posts 543567979 506858465 506858465
public bp_service_codes 543567979 506858465 506858465
public browsers 543567979 506858465 506858465
Then I'm doing: VACUUM FREEZE; and nothing happens, I only get debug and warning messages as I mentioned above.

Settings on server:

namesettingunit
autovacuumon
autovacuum_analyze_scale_factor0.1
autovacuum_max_workers20
autovacuum_vacuum_cost_delay0ms
autovacuum_vacuum_cost_limit200
autovacuum_vacuum_scale_factor0.2
bgwriter_delay200ms
checkpoint_completion_target0.9
checkpoint_segments128
checkpoint_timeout1800s
client_encodingUTF8
client_min_messagesdebug1
commit_delay5000
commit_siblings15
DateStyleISO, MDY
deadlock_timeout1000ms
debug_pretty_printon
default_statistics_target100
default_text_search_configpg_catalog.english
dynamic_shared_memory_typeposix
effective_cache_size125829128kB
extra_float_digits3
fsyncon
full_page_writesoff
lc_messagesen_US.UTF-8
lc_monetaryen_US.UTF-8
lc_numericen_US.UTF-8
lc_timeen_US.UTF-8
listen_addresses*
log_autovacuum_min_duration1000ms
log_checkpointson
log_destinationstderr
log_directory/home/pgsql/data/pg_log
log_filenamepostgresql-%a.log
log_line_prefix%t %h %u %p
log_lock_waitson
log_min_duration_statement1000ms
log_min_error_statementdebug1
log_min_messagesdebug1
log_rotation_age1440min
log_rotation_size0kB
log_statementnone
log_timezoneUTC
log_truncate_on_rotationon
logging_collectoron
maintenance_work_mem2097152kB
max_connections800
max_prepared_transactions10
max_replication_slots1
max_stack_depth2048kB
max_wal_senders3
port9125
random_page_cost1.2
search_pathpublic
seq_page_cost1
shared_buffers65536008kB
synchronous_commitoff
temp_buffers163848kB
TimeZoneEurope/Moscow
track_countson
update_process_titleoff
vacuum_cost_delay1ms
vacuum_freeze_min_age75000000
vacuum_freeze_table_age200000000
vacuum_multixact_freeze_min_age5000000
vacuum_multixact_freeze_table_age150000000
wal_buffers20488kB
wal_keep_segments128
wal_levelhot_standby
work_mem65536kB

Also:
select txid_current(); - 5345750425
select xmin from stats_y2016_m3 order by ts_spawn desc limit 1; - 1050801875
why such difference?

Re: oldest xmin is far in the past

From
Tomas Vondra
Date:
Hi,

On 03/18/2016 09:42 AM, John Snow wrote:
> Hi everyone!
>
> Trying to make VACUUM FREEZE on PG instance and keep getting this error:
>
> 2016-03-18 05:56:51 UTC   46750 WARNING:  oldest xmin is far in the past
> 2016-03-18 05:56:51 UTC   46750 HINT:  Close open transactions soon to
> avoid wraparound problems.
> 2016-03-18 05:56:51 UTC   46750 DEBUG:  transaction ID wrap limit is
> 2654342112, limited by database with OID 1
> 2016-03-18 05:56:51 UTC   46750 DEBUG:  MultiXactId wrap limit is
> 2147483648, limited by database with OID 12451
>
> Also "age" and "relfrozenxid" doesnt't change.

That probably means there's an old transaction somewhere - either a 
regular one (check pg_stat_activity) or a prepared one (pg_prepared_xacts).

The meaning of "old" depends on autovacuum_freeze_max_age - what value 
is set in the session running the VACUUM FREEZE?

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: oldest xmin is far in the past

From
John Snow
Date:
There is no any long transaction neither prepared transaction.
#autovacuum_freeze_max_age = 200000000 - default value

I have 9.4.5 version. Also it all started after I've setup Slony replication(mb just a coincidence). All tables in public schema have the same "age", I believe this is weird.

How can I calculate how long DB can live in this stage?

2016-03-19 0:28 GMT+03:00 Tomas Vondra <tomas.vondra@2ndquadrant.com>:
Hi,

On 03/18/2016 09:42 AM, John Snow wrote:
Hi everyone!

Trying to make VACUUM FREEZE on PG instance and keep getting this error:

2016-03-18 05:56:51 UTC   46750 WARNING:  oldest xmin is far in the past
2016-03-18 05:56:51 UTC   46750 HINT:  Close open transactions soon to
avoid wraparound problems.
2016-03-18 05:56:51 UTC   46750 DEBUG:  transaction ID wrap limit is
2654342112, limited by database with OID 1
2016-03-18 05:56:51 UTC   46750 DEBUG:  MultiXactId wrap limit is
2147483648, limited by database with OID 12451

Also "age" and "relfrozenxid" doesnt't change.

That probably means there's an old transaction somewhere - either a regular one (check pg_stat_activity) or a prepared one (pg_prepared_xacts).

The meaning of "old" depends on autovacuum_freeze_max_age - what value is set in the session running the VACUUM FREEZE?

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: oldest xmin is far in the past

From
Tomas Vondra
Date:
Hi,

On 03/19/2016 06:29 AM, John Snow wrote:
> There is no any long transaction neither prepared transaction.

Can you show us pg_stat_activity? Particularly the xmin values for 
backends attached to the two databases mentioned in the log (1 and 12451).

FWIW the second OID is a bit weird - the first OID assigned to normal 
objects is defined as 16384, and none of the so I wonder how you managed 
to create a database with such DB?

Unless it's one of the template databases, but I got different OIDs when 
I tried a fresh initdb on 9.4.

> #autovacuum_freeze_max_age = 200000000 - default value

After looking at the code a bit more, I see it uses some additional 
configuration options:

* freeze_min_age
* vacuum_freeze_min_age
* autovacuum_freeze_max_age (we already know this one)

What values are set for those?

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: oldest xmin is far in the past

From
Jim Nasby
Date:
On 3/19/16 11:32 AM, Tomas Vondra wrote:
> Hi,
>
> On 03/19/2016 06:29 AM, John Snow wrote:
>> There is no any long transaction neither prepared transaction.
>
> Can you show us pg_stat_activity? Particularly the xmin values for
> backends attached to the two databases mentioned in the log (1 and 12451).
>
> FWIW the second OID is a bit weird - the first OID assigned to normal
> objects is defined as 16384, and none of the so I wonder how you managed
> to create a database with such DB?

On my 9.4, template1 has oid 1.

BTW, John mentioned Slony; if this is on one of the replicas then it's 
certainly understandable that all the tables have ages that are almost 
identical. That happens because the initial COPY of each table takes 
place in a single transaction, and the only other activity that's 
generating XIDs is the normal replay process. Depending on your 
settings, I'd expect that you're only generating a couple XIDs/minute, 
so even if it took 10 days to do the initial copy you'd still only have 
a span of ~30k transactions. That means autovac will suddenly want to 
freeze the whole database in one shot. It's a good idea to run a manual 
vacuum freeze after the initial copy is done to prevent this.

To answer one of your other questions, it look like all the ages are 
~500M XIDs, which means you've got another ~1B to go before this becomes 
a serious concern.

> * freeze_min_age
> * vacuum_freeze_min_age
> * autovacuum_freeze_max_age (we already know this one)
>
> What values are set for those?

Better yet, can you just run this query?

SELECT name, setting, unit, source  FROM pg_settings  WHERE name ~ 'freeze|vacuum' OR source !~ 'default|override'
;

-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com