Details
Improvement
Closed
Major
Description
Improve the connection pool recovery logic if possible. More info at:
http://www.jivesoftware.org/forums/thread.jspa?threadID=15165
Activity
Can we show some simple additional stats on the database page? Check out the following:
http://proxool.sourceforge.net/admin.html
It would be super sweet to show the current info as well as a graph over time. Maybe the graphing would be in the future though. 
Rather than include the web servlet, it seems like it would be much better to incorporate that same data into the existing database page.
Hrm. Good point. I had planned on doing that "in the future" but might as well do it now instead of using the servlet. =)
BTW I ran your test and wow. it didn't miss a beat! (I took down MySQL, almost immediately did it detect problems, things broke, etc, and then I started MySQL back up and it immediately was happy again) I also did a rude test -> i kill -9'd mysql out from under openfire and it didn't detect the mysql shutdown immediately in terms of .. immediately show that mysql died, but the first database request caught it. (and I imagine the every 30 second check would have seen it) Brought up MySQL, all is well again immediately.
I'm quite pleased with it's behavior!
I'll look into improving the servlet. 
BTW, looks like the ClearSpace folk aren't particularly interested for now, sounds like they solved their problems with their stuff. Bruce did tell me that JNDI has some sort of it's own connection pooling, but I'm not sure it has the powerful connection testing that this has. (proxool is testing the connection before and after it's used to see if it's still a valid connection, it seems to be very robust in that sense ... the caveat of course is that' it's doing extra "SELECT 1" commands, but I explicitly picked something super quick like that so it shouldn't be a noticable performance hit.
Speaking of which, at some point I'd like to investigate where we could use some indexes and such to improve our database interactions altogether. Guus found at least one instance where we were missing a possible important index that sped things up quite a bit.
Matt,
Last week I was thinking about this same issue and discussed an idea with Bill. Basically, the idea was to create a DbCommand class that will encapsulate the logic of getting a connection from the pool, executing the query and returning the connection to the pool. When trying to execute the query a try/catch block would be used to detect SocketExceptions so if a socket exception was caught then a new connection from the pool will be obtained and the previous one is discarded. We may use a times_to_retry_limit if we want or just retry until a good connection is found thus cleaning up dead connections in the pool.
I would use an optimistic approach instead of a pesimistic one. Where optimistic approach = assume that the connection is good, try to execute the query and if the socket is dead then try to recover. A perimistic approach would be to assume that the connection is dead so a check-query should be performed before obtaining or using a connection from the pool. Note: The optimistic approach has an important overhead.
The DbCommand class would have 2 methods that subclasses would need to implement. #configureStatement and #processResultSet. A nice side effect of this class is that we won't need to remember to use a tr/catch/finally block to get and return connections to the pool and remember to close the result set and statement.