What is a good tool for the investigation of Database Connection usage in Java?

Question

What is a good tool for the investigation of Database Connection usage in Java?

A developer is supporting a complex Java program which is ocassionally exhausting the number of Database connections available. Since the problem is sporadic it would be useful to know which thread has opened multiple connections to the database to focus the effort in this area.

In the end, the correct fix seems to be to rewrite the program to reuse connections and not open multiple connections per thread.

I am asking, what tools should the developer have in his tool box to be able to investigate the resources i.e. Database Connections that have been allocated by a thread.

Answer 1

Have a look at log4jdbc. It enables you to have a look at all stuff going over your jdbc, including opening/closing connections as well as connection number information.

Answer 2

Connection pools can give you some diagnostics. For example check out debugUnreturnedConnectionStackTraces property for C3P0 connection pool:

http://www.mchange.com/projects/c3p0/index.html#debugUnreturnedConnectionStackTraces

Answer 3

Someone showed me ConnLeakFinder recently, "a simple tool to pinpoint jdbc connection leaks in java code". I haven't tested it myself so far but it should allow you To see who did not close the connection after use. See Connection+Leak+How+To+Find.htm.

But indeed, you should conslder using a connection pool (for example c3p0).

Answer 4

P6Spy is an open source framework to support applications that intercept and optionally modify database statements.

From http://www.p6spy.com/about.html
The P6Spy distribution includes the following modules:

• P6Log. P6Log intercepts and logs the database statements of any application that uses JDBC. This application is particularly useful for developers to monitor the SQL statements produced by EJB servers, enabling the developer to write code that achieves maximum efficiency on the server. P6Spy is designed to be installed in minutes and requires no code changes.
• P6Outage. P6Outage detects long-running statements that may be indicative of a database outage proble and will log any statement that surpasses the configurable time boundary during its execution. P6Outage was designed to minimize any logging performance penalty by logging only long running statements.