From: | Jerry Sievers <gsievers19(at)comcast(dot)net> |
---|---|
To: | Jerry Sievers <gsievers19(at)comcast(dot)net> |
Cc: | Bruce Momjian <bruce(at)momjian(dot)us>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: PgUpgrade bumped my XIDs by ~50M? |
Date: | 2018-04-05 18:39:01 |
Message-ID: | 87sh89qz0a.fsf@jsievers.enova.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Jerry Sievers <gsievers19(at)comcast(dot)net> writes:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>
>> On Wed, Apr 4, 2018 at 07:13:36PM -0500, Jerry Sievers wrote:
>>
>>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>> > 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.
>>
>> Are the objects 50M behind or is txid_current 50M different? Higher or
>> lower?
>
> txid_current is another 12M higher then a few hours ago. Still waiting
> to hear from my reporting team if they changed anything.
Reporting team claims nothing changed.
I still have 150 tables ready for autovac just based on freeze age
value. Autovac is running at our as-config'd max worker count of 20
w/all threads busy as expected.
If I can assume stats such as pg_stat_database start initially cleared
after an upgrade...
Please see that pg_stat_database showing about the number of
transactions that I'd expect for this system and ~1.5 day duration.
How have some objects apparently aged by ~100M transactions (by now at
last check) since the upgrade ??
Thanks
postgres=# select sum(xact_rollback+ xact_commit) from pg_stat_database;
sum
---------
5292417
(1 row)
postgres=# select now() - pg_postmaster_start_time();
?column?
-----------------------
1 day 13:18:48.721896
(1 row)
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.8 on x86_64-pc-linux-gnu (Ubuntu 9.6.8-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 row)
>
> This thing is running PgLogical and has a few of our event triggers as
> well. But nothing in this regard changed with the upgrade.
>
> What if some very frequent but trivial statements that did not get
> assigned a real TXID in 9.5 on this configuration now are being treated
> differently?
>
> What's puzzling too is that when I run my TPS monitor script, it's
> clicking along at what looks typical, presently would only amount to
> 700k transactions/day but we're off-peak.
>
> Thx
>>
>>
>>>
>>> If this is a not too uncommon case of users running amok, then this time
>>> in particular they really went off the charts :-)
>>
>> I have never heard of this problem.
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800
From | Date | Subject | |
---|---|---|---|
Next Message | Adam =?utf-8?Q?Sj=C3=B8gren?= | 2018-04-05 20:27:04 | Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100 |
Previous Message | Tom Lane | 2018-04-05 17:27:07 | Re: Docker + postgreSQL : OOM killing in a large Group by operation |