Re: PgUpgrade bumped my XIDs by ~50M?

Bruce Momjian <bruce(at)momjian(dot)us> writes:

> On Wed, Apr 4, 2018 at 05:29:46PM -0500, Jerry Sievers wrote:
>
>> 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?
>
> Uh, you can read how pg_upgrade handles frozen xids in pg_upgrade.c:
>
> https://doxygen.postgresql.org/pg__upgrade_8c_source.html#l00543
>
> I am not sure what would have caused such a jump. pg_upgrade brings
> over the frozen values for each table, and sets the server's frozen
> counter to match the old one.
>
> If you run 'pg_dump --binary-upgrade' you will see the frozen xids being
> transfered:
>
> -- For binary upgrade, set heap's relfrozenxid and relminmxid
> UPDATE pg_catalog.pg_class
> SET relfrozenxid = '558', relminmxid = '1'
> WHERE oid = 'public.test'::pg_catalog.regclass;
>
> Is it possible that pg_upgrade used 50M xids while upgrading?

Hi Bruce.

Don't think so, as I did just snap the safety snap and ran another
upgrade on that.

And I also compared txid_current for the upgraded snap and our running
production instance.

The freshly upgraded snap is ~50M txids behind the prod instance.

If this is a not too uncommon case of users running amok, then this time
in particular they really went off the charts :-)

Will investigate...

FYI, this is the same system that a few weeks ago issued complaints
during vacuum of an XID younger than relfrozenxid, which a system
restart did mysteriously resolve.

I hope we're going to be OK here.

Thx

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