PgUpgrade bumped my XIDs by ~50M?

From: Jerry Sievers <gsievers19(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: PgUpgrade bumped my XIDs by ~50M?
Date: 2018-04-04 22:29:46
Message-ID: 87sh8a7gh1.fsf@jsievers.enova.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We have a large >20TB system just pg_upgraded from 9.5 to 9.6 as per the
versions shown below.

The system does <5M transactions/day based on sum(commit + abort) from
pg_stat_database.

Autovac is running all possible threads now and upon investigating I see
that thousands of tables are now above the freeze threshold. Same
tables all appear ~50M xids older than they did yesterday and the
upgrade was less than 24 hours ago.

I have a "safety" snap made of the system before upgrade that can be
used for inspection.

Any ideas why the age jump?

select age(l.relfrozenxid), l.oid::regclass::text as relation, l.relkind, l.relpages, r.oid::regclass::text as "toast for"
from pg_class l
left join pg_class r on l.oid = r.reltoastrelid
where l.relkind in ('r', 't')
order by age desc, relation
limit :limit

ii postgresql-9.5 9.5.12-1.pgdg16.04+1 amd64 object-relational SQL database, version 9.5 server
ii postgresql-9.6 9.6.8-1.pgdg16.04+1 amd64 object-relational SQL database, version 9.6 server

Thanks

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2018-04-04 23:28:27 Re: PgUpgrade bumped my XIDs by ~50M?
Previous Message Alban Hertroys 2018-04-04 09:43:11 Re: Concatenate of values in hierarchical data