[Spacewalk-list] [Spacewalk-devel] I think I found the root cause of the PostgreSQL Idle in transaction connection build up.

Mon Nov 12 14:51:47 UTC 2012

After I've upgraded to spacewalk 1.8, I've not seen any of these.  I'd typically see 40 - 50 hanging out at any given time.

I'm not running the updated jdbc connectoid thingamajig, just whatever comes with doing an install from the spacewalk 1.8 repos.  On Friday, I had one process idle in transaction.  After just checking now, I have none.

Greg Wojtak
Sr. Unix Systems Engineer
Office: (313) 373-4306
Cell: (734) 718-8472

From: Paul Robert Marino <prmarino1 at gmail.com<mailto:prmarino1 at gmail.com>>
Reply-To: "spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com>" <spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com>>
Date: Friday, November 9, 2012 4:09 PM
To: "spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com>" <spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com>>, "lists at xistenz.org<mailto:lists at xistenz.org>" <lists at xistenz.org<mailto:lists at xistenz.org>>
Subject: Re: [Spacewalk-list] [Spacewalk-devel] I think I found the root cause of the PostgreSQL Idle in transaction connection build up.

I haven't had a chance to upgrade my qa instance yet because I've been bussy with other thing so I'm not sure.

On Nov 9, 2012 4:05 PM, "Jonathan Scott" <lists at xistenz.org<mailto:lists at xistenz.org>> wrote:
Are you seeing the same issue in 1.8? I was hoping a fresh install and profile migration would put me in the clear.

- Jonathan

On Fri, Nov 9, 2012 at 12:32 PM, Paul Robert Marino <prmarino1 at gmail.com<mailto:prmarino1 at gmail.com>> wrote:

Yea I'm seeing the same thing on my development instance.
While it doesn't completely solove the issue it seems to make it manageble for people still running 1.7. Without setting a rediculous number of max connection in postgresql. I still haven't had a chance to compare with 1.8 but I. Sould be able to start testing that soon.

On Nov 9, 2012 11:03 AM, "Jonathan Scott" <lists at xistenz.org<mailto:lists at xistenz.org>> wrote:
Update:

The system still seems to be managing the "idle in transaction" processes much better than before. While the number fluctuates (its in the 30s today), it doesn't appear to be a detriment to the application as it was once before.

- Jonathan

On Wed, Nov 7, 2012 at 11:30 AM, Jonathan Scott <lists at xistenz.org<mailto:lists at xistenz.org>> wrote:
Yea; after my nightly errata check, my "idle in transaction" processes climbed up to 50 and has hung there all morning. The only real noticeable change is that the app was actually functional this morning after the errata load vs. hung with maxed out apache processes. I'll keep running under this configuration for the remainder of the week.

- Jonathan

On Tue, Nov 6, 2012 at 4:21 PM, Paul Robert Marino <prmarino1 at gmail.com<mailto:prmarino1 at gmail.com>> wrote:
Well after letting it run for 24 hours Ive found it doesn't completely
eliminate them but it has reduced them significantly.

On Tue, Nov 6, 2012 at 2:36 PM, Wojtak, Greg (Superfly)
<GregWojtak at quickenloans.com<mailto:GregWojtak at quickenloans.com>> wrote:
> Just sayin', I haven't seen these in the two days since I upgraded to spacewalk 1.8…
>
> If they do appear, I wouldn't mind testing either.  I've got a few hundred servers on our spacewalk instance, along with a proxy,  to help stress it with.
>
> Greg Wojtak
> Sr. Unix Systems Engineer
> Office: (313) 373-4306<tel:%28313%29%20373-4306>
> Cell: (734) 718-8472<tel:%28734%29%20718-8472>
>
>
> From: Jonathan Scott <lists at xistenz.org<mailto:lists at xistenz.org><mailto:lists at xistenz.org<mailto:lists at xistenz.org>>>
> Reply-To: "lists at xistenz.org<mailto:lists at xistenz.org><mailto:lists at xistenz.org<mailto:lists at xistenz.org>>" <lists at xistenz.org<mailto:lists at xistenz.org><mailto:lists at xistenz.org<mailto:lists at xistenz.org>>>, "spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com><mailto:spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com>>" <spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com><mailto:spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com>>>
> Date: Tuesday, November 6, 2012 1:39 PM
> To: "spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com><mailto:spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com>>" <spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com><mailto:spacewalk-list at redhat.com<mailto:spacewalk-list at redhat.com>>>
> Cc: Tom Lane <tgl at redhat.com<mailto:tgl at redhat.com><mailto:tgl at redhat.com<mailto:tgl at redhat.com>>>, "spacewalk-devel at redhat.com<mailto:spacewalk-devel at redhat.com><mailto:spacewalk-devel at redhat.com<mailto:spacewalk-devel at redhat.com>>" <spacewalk-devel at redhat.com<mailto:spacewalk-devel at redhat.com><mailto:spacewalk-devel at redhat.com<mailto:spacewalk-devel at redhat.com>>>
> Subject: Re: [Spacewalk-list] [Spacewalk-devel] I think I found the root cause of the PostgreSQL Idle in transaction connection build up.
>
> Paul, you stud! I'm one of the ones reporting this same issue, and I will happily volunteer my 60-instance Spacewalk 1.7 install for testing. I'll implement your fix and report back on my findings.
>
> - Jonathan
>
> On Tue, Nov 6, 2012 at 12:51 AM, Paul Robert Marino <prmarino1 at gmail.com<mailto:prmarino1 at gmail.com><mailto:prmarino1 at gmail.com<mailto:prmarino1 at gmail.com>>> wrote:
>
> Well you are right there is nothing in the change log that idicates that this issue existed or how its fixed.
> But as I said it seems to fix it there is probably a side effect fix that was not planed but seems to work.
> The results are rediculously obvious initialy now honestly I think it needs a few days of testing to prove it, and I would like for others to confirm it but from my initial test it on one of my development instances it looks good. I would like other people to test it because I'm not using monitoring on that instance and I only have a few systems attached to it but the difference is so obvious there is deffinitly something there.
> By the way I've seen the change log betwean 701to 702 but I haven't seen the change log betwean 702 and 703 and I looked its not on their site or in the source package as far as I could initialy tell.
>
> While I admit I can't point to a reason in the change log why, it at least initialy seems to work. I think if any thing it may be a compound correction of multiple bugs that may of fixed a larger harder to pinpoint  issue.
>
> On Nov 6, 2012 12:01 AM, "Tom Lane" <tgl at redhat.com<mailto:tgl at redhat.com><mailto:tgl at redhat.com<mailto:tgl at redhat.com>>> wrote:
> Paul Robert Marino <prmarino1 at gmail.com<mailto:prmarino1 at gmail.com><mailto:prmarino1 at gmail.com<mailto:prmarino1 at gmail.com>>> writes:
>> Ive been doing some testing and I am fairly positive I found out why
>> the number of connections in PostgreSQL increases and its not a
>> spacewalk bug at all.
>> It looks like its a JDBC bug [ and is fixed in 8.4-703 ]
>
> This is really interesting, but I looked through the upstream commit
> logs, and I can't see any patches between 8.4-701 and 8.4-703 that look
> like they'd cure a "connection leak" such as you're describing.  There
> are a couple of fixes for possible loss-of-protocol-sync issues, but it
> doesn't seem like that would result in silent leakage; the symptoms
> would be pretty obvious.
>
> Have you poked into the client-side state to see what that end thinks
> it's doing with the idle connections?
>
>                         regards, tom lane
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com<mailto:Spacewalk-list at redhat.com><mailto:Spacewalk-list at redhat.com<mailto:Spacewalk-list at redhat.com>>
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com<mailto:Spacewalk-list at redhat.com>
> https://www.redhat.com/mailman/listinfo/spacewalk-list

_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com<mailto:Spacewalk-list at redhat.com>
https://www.redhat.com/mailman/listinfo/spacewalk-list

_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com<mailto:Spacewalk-list at redhat.com>
https://www.redhat.com/mailman/listinfo/spacewalk-list

_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com<mailto:Spacewalk-list at redhat.com>
https://www.redhat.com/mailman/listinfo/spacewalk-list