Bruce Momjian <bruce@xxxxxxxxxx> 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.consulting@xxxxxxxxxxx p: 312.241.7800
