Re: oldest xmin is far in the past

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