[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Help understanding SIReadLock growing without bound on completed transaction
From: "Mike Klaas" <mike () superhuman ! com>
Date: 2020-05-26 16:14:47
Message-ID: kao3yp20.7dbea4d8-f0ac-4b61-9057-52a708419841 () we ! are ! superhuman ! com
[Download RAW message or body]
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@superhuman.com > wrote:
>
> On Fri, May 22, 2020 at 3:15 PM, Thomas Munro < thomas. munro@ gmail. com (
> 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
>
>
>
[Attachment #3 (unknown)]
<html><head></head><body><div><div></div><div><div><div class="">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:<br/></div><div class=""><br/></div><div \
class="">SELECT relname, age(relfrozenxid), relfrozenxid FROM pg_class WHERE relkind \
= 'r' and relname not like 'pg%' order by relname;<br/></div><div \
class=""> relname | age | \
relfrozenxid<br/></div><div \
class="">---------------------------+-----------+--------------<br/></div><div \
class=""><table name> | 107232506 | \
4237961815<br/></div><div class=""><table name> | \
93692362 | 4251501959<br/></div><div class=""><table name> \
| 183484103 | 4161710218<br/></div><div class=""><table name> \
| 50760536 | 4294433785<br/></div><div class=""><table name> \
| 58821410 | 4286372911<br/></div><div class=""><table name> \
| 117427283 | 4227767038<br/></div><div class=""><table name> \
| 94541111 | 4250653210<br/></div><div class="">…<br/></div><div \
class=""><br/></div><p class="p1" style="margin:0px;"><span class="s1">select \
max(backend_xid::text), min(backend_xmin::text) from pg_stat_activity where \
state='active';</span><br/></p><p class="p1" style="margin:0px;"><span \
class="s1"><span class="Apple-converted-space"> </span>max<span \
class="Apple-converted-space"> </span>| <span class="Apple-converted-space"> \
</span>min <span class="Apple-converted-space"> </span></span><br/></p><p \
class="p1" style="margin:0px;"><span \
class="s1">----------+----------</span><br/></p><p class="p1" \
style="margin:0px;"><span class="s1"><span class="Apple-converted-space"> \
</span>50350294 | 50350065</span><br/></p><div class=""><br/></div><div \
class="">-Mike</div></div><br/><div \
class="gmail_signature"></div></div><br/><div><div class="gmail_quote">On Tue, May \
26, 2020 at 8:42 AM, Mike Klaas <span dir="ltr"><<a \
href="mailto:mike@superhuman.com" target="_blank">mike@superhuman.com</a>></span> \
wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><div class="gmail_extra"><div class="gmail_quote \
sh-color-black sh-color" style="null" id="null"><div class="sh-color-black \
sh-color"><div class="sh-color-black sh-color"><div class="sh-color-black \
sh-color"><div class="sh-color-black sh-color"><div class="gmail_quote sh-color-black \
sh-color">On Fri, May 22, 2020 at 3:15 PM, Thomas Munro <span class="sh-color-black \
sh-color" dir="ltr"><<a href="mailto:thomas.munro@gmail.com" target="_blank" \
rel="noopener noreferrer">thomas.<wbr/>munro@<wbr/>gmail.<wbr/>com</a>></span> \
wrote:<br/><blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex" class="gmail_quote sh-color-black sh-color"><div \
class="gmail_extra sh-color-black sh-color"><div id="null" style="null" \
class="gmail_quote sh-color-black sh-color"><p class="sh-color-black sh-color"> \
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).
<br/></p></div></div></blockquote></div></div></div><div class="sh-color-black \
sh-color"><div class="sh-color-black sh-color"><br/></div><div class="sh-color-black \
sh-color">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.<br/></div><div class="sh-color-black sh-color"><br/></div><div \
class="sh-color-black sh-color">Would it be helpful to cross-post this thread to \
pgsql-bugs or further investigate on my end<br/></div><div class="sh-color-black \
sh-color"><br/></div><div class="sh-color-black sh-color">-Mike<br/></div><div \
class="sh-color-black sh-color"><br/></div><div class="sh-color-black sh-color">[1] \
<span class="s1 sh-color-black sh-color">superhuman@</span><span class="s2 \
sh-color-black sh-color">production</span><span class="s1 sh-color-black \
sh-color">=> select datname, datfrozenxid, age(datfrozenxid) from \
pg_catalog.pg_database;</span></div></div><p style="margin:0px;" class="p1 \
sh-color-black sh-color"><span class="s1 sh-color-black sh-color"><span \
class="Apple-converted-space sh-color-black sh-color"> </span>datname<span \
class="Apple-converted-space sh-color-black sh-color"> </span>| datfrozenxid \
|<span class="Apple-converted-space sh-color-black sh-color"> </span>age <span \
class="Apple-converted-space sh-color-black sh-color"> </span></span><br/></p><p \
style="margin:0px;" class="p1 sh-color-black sh-color"><span class="s1 sh-color-black \
sh-color">---------------+--------------+-----------</span><br/></p><p \
style="margin:0px;" class="p1 sh-color-black sh-color"><span class="s1 sh-color-black \
sh-color"><span class="Apple-converted-space sh-color-black sh-color"> \
</span>cloudsqladmin | <span class="Apple-converted-space sh-color-black sh-color"> \
</span>4173950091 | 169089900</span><br/></p><p style="margin:0px;" class="p1 \
sh-color-black sh-color"><span class="s1 sh-color-black sh-color"><span \
class="Apple-converted-space sh-color-black sh-color"> </span>template0 <span \
class="Apple-converted-space sh-color-black sh-color"> </span>| <span \
class="Apple-converted-space sh-color-black sh-color"> </span>4266855294 |<span \
class="Apple-converted-space sh-color-black sh-color"> \
</span>76184697</span><br/></p><p style="margin:0px;" class="p1 sh-color-black \
sh-color"><span class="s1 sh-color-black sh-color"><span class="Apple-converted-space \
sh-color-black sh-color"> </span>postgres<span class="Apple-converted-space \
sh-color-black sh-color"> </span>| <span class="Apple-converted-space \
sh-color-black sh-color"> </span>4173951306 | 169088685</span><br/></p><p \
style="margin:0px;" class="p1 sh-color-black sh-color"><span class="s1 sh-color-black \
sh-color"><span class="Apple-converted-space sh-color-black sh-color"> \
</span>template1 <span class="Apple-converted-space sh-color-black sh-color"> \
</span>| <span class="Apple-converted-space sh-color-black sh-color"> \
</span>4266855860 |<span class="Apple-converted-space sh-color-black sh-color"> \
</span>76184131</span><br/></p><p style="margin:0px;" class="p1 sh-color-black \
sh-color"><span class="s1 sh-color-black sh-color"><span class="Apple-converted-space \
sh-color-black sh-color"> </span>superhuman<span class="Apple-converted-space \
sh-color-black sh-color"> </span>| <span class="Apple-converted-space \
sh-color-black sh-color"> </span>4145766807 | \
197273184</span></p></div></div></div></div></blockquote></div></div><br/></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic