I'm running into the classic "pool exhausted" problem. Every 36 hours or so, we hit the wall on our BasicDataSource. But in this case, we *are* closing our connections. In fact, we've implemented several checks to make sure all connections are released.
I've exhausted all of our avenues so I'm hoping that someone can shed some light or at least point us in a new direction to try.
We have a two app servers, both running Tomcat 5.0.28 (includes dbcp-1.2.1). We have one database server, running MySQL 5.0.x. The two tomcat servers are configured identically and running the same code in a clustered environment. We are using the Spring Framework in the application to load a BasicDataSource. The configuration of it looks like:
driverClassName -> com.mysql.jdbc.Driver
maxActive -> 100
maxIdle -> 5
maxWait -> 10000
minEvictableIdleTimeMillis -> 5000
timeBetweenEvictionRunsMillis -> 5000
defaultAutoCommit -> false
removeAbandoned -> true
removeAbandonedTimeout -> 300
logAbandoned -> true
If you'll notice, we have the removeAbandoned feature working. >From the docs, I believe that guarantees that any unclosed connection will be closed and reported. For my sanity's sake I verified our server logs that it is using those settings and found:
Now after production has been running for about 24 hours, one of the app servers has skewed its active connections to up around 78 connections. It has been holding steady at 70 connections since this morning. (I'm inspecting these values through JMX) However, there are only 10 active sessions for the application on that server! Then, when I examine the database's active connections, it reports only 27 connections open for that app server. (Also, it has been holding steady at around 20 connections all day) That seems like a strange disconnect!
And finally, I search through the entire logs for our runaway app server. I search both the custom log file of our application, and catalina.out of tomcat. I'm looking for a statement from removeAbandoned about unclosed connections like:
DBCP object created 2007-02-06 17:30:50 by the following code was never closed:
However, this statement (only searching on "DBCP object") never appears in our log files. So it appears that we are indeed being responsible with our db connections. It should be noted that we also have custom code that reports any connections that are still active after a timeout of X minutes. Essentially, its the same as the configuration to track abandoned connections. We added this code before we found the nice configuration options above.
I've attached a screen shot that shows the MySQL Admin connections screen overlaid on JConsole. JConsole is showing both app servers and the graph of their active connections. Both servers have not been rebooted since yesterday afternoon.
Please Help! We are desperate! We have been trying to troubleshoot this problem for the past 2 months.
P.S. - though the current screen shot shows only one app server exhibiting the problem, it is not limited to one or the other and sometimes both servers hit the limit at close to the same time. Also, the top graph in the screen shot is not normalized to the one in the bottom. Unfortunately, JConsole died on me and I had to restart that server's connection.