Trying to identify source of abandoned connections in tomcat

Question

I'm using dbcp connection pooling in tomcat (version 7) and I have a connection leak somewhere in my code. After a short amount of time, a request for a new connection returns the following exception:

"Cannot get a connection, pool error Timeout waiting for idle object"

I went back through my code and to me it looks like all connections are being closed properly (doesn't everyone say this...).

In order to debug this I added the following properties in context.xml:

logAbandoned="true" removeAbandoned="true" removeAbandonedTimeout="300"

So the resource tag now looks like this:

 <Resource name="jdbc/findata" auth="Container" type="javax.sql.DataSource"
               maxActive="20" maxIdle="5" maxWait="10000"
               username="root" password="xxxxxx" driverClassName="com.mysql.jdbc.Driver"
               logAbandoned="true" removeAbandoned="true" removeAbandonedTimeout="300"
               url="jdbc:mysql://localhost:3306/findata"/>

I then restarted tomcat and started hitting web pages until the error message appeared (in the browser window). However I haven't yet been able to figure out where the "logAbandoned" property is writing its information. I'm looking in

/usr/share/apache-tomcat-7.0.11/logs

but the only recently modified log file in there is

localhost_access_log.2011-04-18.txt

Any assistance is much appreciated.

Solution 1

What I did in a similar scenario was to save threadstacks of threads which requests connections, and then in another thread print out threadstacks pertaining to each open connection in every minute or so. I guess this is the brute force way of doing it. But I solved my issue pretty quickly.

Solution 2

According to this site you must provide a factory to your resource definition from your context.xml. The configuration of the resource will be done by this factory instance so all the "additional" parameters are set this way. To be more specific you would have something like this in your context.xml (or server.xml - depends where you define your resource) :

<Resource name="jdbc/db" auth="Container" type="javax.sql.DataSource"
 factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
 driverClassName="org.postgresql.Driver" url="jdbc:postgresql://127.0.0.1/db"
 username="hibernate" password="hibernate" maxActive="20" maxIdle="10"
 maxWait="1000" removeAbandoned="true" removeAbandonedTimeout="20"
 logAbandoned="true" />  

Notice the factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" which is essential for our purpose. Without it the removeAbandoned="true" has no effect.

The stacks of each connection that is abandoned is stored in the catalina.log ($tomcat_dir/logs). From there it will give pretty accurate details for debugging connections.

Other than "abandoned" parameters you can configure a lot of stuff with relation to tomcat jdbc pool performance , timeouts and other tints and bolts. Of course this requires some deep know-how. (You can find details in the site I initially provided)

Solution 3

One relatively easy way to make sure you always close a connection is to get it in the servlet filter, place it into ThreadLocal, use that ThreadLocal through all your code, and then close it when response goes back through the filter. (An optimization would be to place a proxy into the ThreadLocal which gets connection on the first request only).

But your immediate problem is to find the leak source, right?

First, make sure you close the connections in finally{} statement, so an exception wouldn't prevent you from doing it.

Second, it is unclear how long it takes for logAbandoned to figure out the connection is idle. Try to wait for some time, may be 15 minutes or so.

Third, you may use proxy JDBC drivers such as http://code.google.com/p/log4jdbc/ . They generate a log of all activities over connections, so you may grep the log to find mismatching open() and close().

Good luck!

Comments

[Vladimir Dyuzhev]

Good one. And remove the stack from the map on close(), right? Simple and efficient.

[KoenigGunther]

Where did you find the information that factory is essential for removeAbandoned? I have no factory in my local configuration. However, abandoned connections are getting closed.

[Ali HAMDI]

This is stated as required in the tomcat 7 documentation tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

[walen]

Tomcat JDBC pool and Apache DBCP pool are two different things. If you don't specify ...tomcat.jdbc.pool... as the factory, Tomcat will default to using DBCP, which is the case for OP ("I'm using dbcp connection pooling in tomcat [...]").