Search Postgresql Archives

Re: Help understanding SIReadLock growing without bound on completed transaction

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On second look, it does seems the xid crossed the 2^32 mark recently, since most tables have a frozenxid close to 4b and the current xid is ~50m:

SELECT relname, age(relfrozenxid), relfrozenxid FROM pg_class WHERE relkind = 'r' and relname not like 'pg%' order by relname;
          relname          |    age    | relfrozenxid
---------------------------+-----------+--------------
<table name>              | 107232506 |   4237961815
<table name>              |  93692362 |   4251501959
<table name>              | 183484103 |   4161710218
<table name>              |  50760536 |   4294433785
<table name>              |  58821410 |   4286372911
<table name>              | 117427283 |   4227767038
<table name>              |  94541111 |   4250653210


select max(backend_xid::text), min(backend_xmin::text) from pg_stat_activity where state='active';

   max    |   min    

----------+----------

 50350294 | 50350065


-Mike


On Tue, May 26, 2020 at 8:42 AM, Mike Klaas <mike@xxxxxxxxxxxxxx> wrote:
On Fri, May 22, 2020 at 3:15 PM, Thomas Munro <thomas.munro@gmail.com> wrote:

Predicate locks are released by ClearOldPredicateLocks(), which releases SERIALIZABLEXACTs once they are no longer interesting. It has a conservative idea of what is no longer interesting: it waits until the lowest xmin across active serializable snapshots is >= the transaction's finishedBefore xid, which was the system's next xid (an xid that hasn't been used yet*) at the time the SERIALIZABLEXACT committed. One implication of this scheme is that SERIALIZABLEXACTs are cleaned up in commit order. If you somehow got into a state where a few of them were being kept around for a long time, but others committed later were being cleaned up (which I suppose must be the case or your system would be complaining about running out of SERIALIZABLEXACTs), that might imply that there is a rare leak somewhere in this scheme. In the past I have wondered if there might be a problem with wraparound in the xid tracking for finished transactions, but I haven't worked out the details (transaction ID wraparound is both figuratively and literally the Ground Hog Day of PostgreSQL bug surfaces).


Thanks for the detailed reply, Thomas.  Is SERIALIZABLEXACT transaction ID wraparound the same as global xid wraparound?  The max transaction age in the db is ~197M [1] so I don't think we've gotten close to global wraparound lately.

Would it be helpful to cross-post this thread to pgsql-bugs or further investigate on my end

-Mike

[1] superhuman@production=> select datname, datfrozenxid, age(datfrozenxid) from pg_catalog.pg_database;

    datname    | datfrozenxid |    age    

---------------+--------------+-----------

 cloudsqladmin |   4173950091 | 169089900

 template0     |   4266855294 |  76184697

 postgres      |   4173951306 | 169088685

 template1     |   4266855860 |  76184131

 superhuman    |   4145766807 | 197273184



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux