[Building Sakai] DB connections timeout after some time in 2.9.2

[David Wafula Wanyonyi]

Thanks  Bill,
Looking at logs we have narrowed down one problematic tool (own custom developed) that is causing this. So nothing to do with  Sakai itself, it appears.

Regards

David Wafula,
Team Lead, Software Development,
eLearning Support and Innovation Unit,
University of Witwatersrand, Johannesburg
Tel +27 11 717 7180
Website http://elearn.wits.ac.za
Private Bag 3, Wits, 2050, South Africa
________________________________
From: Niebel, William (wdn5e) [wdn5e at eservices.virginia.edu]
Sent: Thursday, June 20, 2013 6:57 PM
To: David Wafula Wanyonyi; dev sakai
Subject: RE: DB connections timeout after some time in 2.9.2

Hi, David.
    I'm sure you'd prefer a certain, quick fix, which I don't have.  But in case it helps, my reading of the log output is that all pool connections are in use, i.e., checked out but not returned to the pool.  (We upgraded a month ago from 2.8.1 to 2.9.1, and haven't seen this problem.)

    One cause would be a 2.9.1-to-2.9.2 change which somehow/sometimes results in a connection not being returned after its use.

    If this is what's happening, you could try setting log to ALL for a few appropriate classes or packages.  I suggest this hoping log output might show connections removed and returned to the pool.   (We use a servlet "LogConfig" to change log levels dynamically without Tomcat restart.  It's been in play for a while and I'm not sure where we got it.)  I have in mind items like:
org.apache.commons.dbcp.PoolingDataSource
org.sakaiproject.db
org.hibernate

    And also to set in a sakai properties file:
hibernate.show_sql=true

    An alternate would be to use a diagnostic proxy somehow between your Sakai and database machines to see the traffic.  mysql "show processlist" might help you identify a build-up of sleeping connections, which you could trace back to proxy output.  (For example, there's a proxy product "Charles".)

    Something like this might show you the sql issued through a connection which isn't returned to the pool, and that might help identify the faulty code.

    Good luck with this.  Please let us know if what you find is interesting.
Bill


<table width="100%" border="0" cellspacing="0" cellpadding="0" style="width:100%;"> 
<tr>
<td align="left" style="text-align:justify;"><font face="arial,sans-serif" size="1" color="#999999"><span style="font-size:11px;">This communication is intended for the addressee only. It is confidential. If you have received this communication in error, please notify us immediately and destroy the original message. You may not copy or disseminate this communication without the permission of the University. Only authorised signatories are competent to enter into agreements on behalf of the University and recipients are thus advised that the content of this message may not be legally binding on the University and may contain the personal views and opinions of the author, which are not necessarily the views and opinions of The University of the Witwatersrand, Johannesburg. All agreements between the University and outsiders are subject to South African Law unless the University agrees in writing to the contrary. </span></font></td>
</tr>
</table
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130621/310ba877/attachment.html