On 3/19/16 11:32 AM, Tomas Vondra wrote:

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

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

Reply via email to