Thread: oldest xmin is far in the past

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

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

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