<html>
<head>
<meta content="text/html; charset=KOI8-R" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">On 10/12/2012 10:03, James Hogarth
wrote:</div>
<blockquote
cite="mid:CAGkb5vfCVPUnrKx5ApN_ourxZWDTGsqXvvpCKR3pGxp+4PWW7Q@mail.gmail.com"
type="cite">
<div class="gmail_extra">
<div class="gmail_quote">
<div>So in general with that patch connection counts look
better however I'm still seeing connections as IDLE in
transaction with hanging open transactions even if the
server didn't outright fall over this time...</div>
<div><br>
</div>
<div>So it looks like there's still some underlying issue
even if this mitigates some of it.</div>
<div><br>
</div>
<div>Here's my current pg_stat_activity for IDLE in
transaction connections:</div>
<div>
<br>
</div>
<div><span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">datid |
datname | procpid | usesysid | usename |
current_query | waiting |
xact_start | query_start
| backend_start | client_addr |
client_port</span><br
style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">
<span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">-------+-------------+---------+----------+-----------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------</span><br
style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">
<span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">16384 |
spaceschema | 17267 | 16388 | spaceuser |
<IDLE> in transaction | f | 2012-12-07
16:23:51.178637+00 | 2012-12-07 16:23:51.178966+00 |
2012-12-07 16:12:34.620533+00 | 127.0.0.1 | 45196</span><br
style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">
<span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">16384 |
spaceschema | 16102 | 16388 | spaceuser |
<IDLE> in transaction | f | 2012-12-07
16:13:19.062683+00 | 2012-12-07 16:13:19.063373+00 |
2012-12-07 16:08:49.622536+00 | 127.0.0.1 | 44779</span><br
style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">
<span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">16384 |
spaceschema | 12847 | 16388 | spaceuser |
<IDLE> in transaction | f | 2012-12-07
16:23:51.141108+00 | 2012-12-07 16:23:51.14131+00 |
2012-12-07 15:57:34.622143+00 | 127.0.0.1 | 43979</span><br
style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">
<span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">16384 |
spaceschema | 17468 | 16388 | spaceuser |
<IDLE> in transaction | f | 2012-12-07
16:13:19.225653+00 | 2012-12-07 16:13:19.226371+00 |
2012-12-07 16:13:19.123346+00 | 127.0.0.1 | 45215</span><br
style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">
<span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">16384 |
spaceschema | 17471 | 16388 | spaceuser |
<IDLE> in transaction | f | 2012-12-07
16:23:51.260104+00 | 2012-12-07 16:23:51.260277+00 |
2012-12-07 16:13:19.340865+00 | 127.0.0.1 | 45218</span><br>
</div>
<div><span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px"><br>
</span></div>
<div><span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px">Checking
the locks table shows that these all have locks on
something ....</span></div>
<div><span style="color:rgb(0,0,0);font-family:'Segoe
UI',Helvetica,Arial,sans-serif;font-size:13px"><br>
</span></div>
</div>
</div>
</blockquote>
What helped me in similar situation is upgrading to postgresql 9.1.<br>
I'm not a DB guru, but the one here told me that there are
significant performance improvements, and he was right.<br>
<br>
After upgrading to 9.1 it works <span id="result_box"
class="short_text" lang="en"><span class="hps">noticeably faster.<br>
Problems with connections count overgrow was </span></span><span
id="result_box" class="short_text" lang="en"><span class="hps"><span
id="result_box" class="short_text" lang="en"><span class="hps">also
</span></span>gone.<br>
<br>
As far as I remember, there was a problem to install Spacewalk
with pgsql91, so I've installed on pgsql84, dumped DB, installed
pgsql 91 and flashed the DB back.<br>
All operation were done on Spacewalk 1.6, </span></span><span
id="result_box" class="short_text" lang="en"><span class="hps">CentOS
6.3.<br>
Now it is 1.8 - no issues with DB was faced.<br>
</span></span>
</body>
</html>