I know of two ways to look at the Tomcat DB Connection Pool info.
1. Using JMX Monitoring
The Tomcat Connection Pool by default will register itself as an MBean (JMX Bean). This feature can be turned on/off with the jmxEnabled attribute on the tomcat-jdbc-pool. See The Tomcat JDBC Connection Pool.
You can use various external JMX tools to monitor the DB Connection Pool and other JMX resources. I would suggest starting with JConsole, which comes with Java. Launch JConsole, connect to your Tomcat (Catalina) JVM, select MBeans header, open Catalina/DataSource/... see pic below.
Read more on Monitoring Tomcat.
2. Write a JdbcInterceptor class that logs DB Connection Pool info
The Tomcat connection pool allows you to register interceptors for JDBC Connections. Below, I will show how to write a JdbcInterceptor class that logs connection usage. The example is for Tomcat 8, but it might work for Tomcat 7 as well.
Add tomcat-jdbc.jar as a provided dependency to your project.
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>8.0.8</version>
<scope>provided</scope>
</dependency>
Create a JdbcInterceptor class
This class uses commons logging, you may want to use something else.
package com.acme.rest.config;
import java.lang.reflect.Method;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.tomcat.jdbc.pool.ConnectionPool;
import org.apache.tomcat.jdbc.pool.JdbcInterceptor;
import org.apache.tomcat.jdbc.pool.PooledConnection;
public class MyConnectionPoolLogger extends JdbcInterceptor {
private static final Log log = LogFactory.getLog(MyConnectionPoolLogger.class);
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if (log.isDebugEnabled()) {
String name = method.getName();
if (CLOSE_VAL.equals(name)) {
log.debug(String.format("Returning Connection to Pool [%s]", proxy));
}
}
return super.invoke(proxy, method, args);
}
@Override
public void reset(ConnectionPool connPool, PooledConnection conn) {
if (connPool != null && conn != null) {
if (log.isDebugEnabled()) {
log.debug(String.format("Getting Connection [%s], Pool active=[%s], idle=[%s]", conn.toString(),
connPool.getActive(), connPool.getIdle()));
}
}
}
@Override
public void disconnected(ConnectionPool connPool, PooledConnection conn, boolean finalizing) {
if (connPool != null && conn != null) {
if (log.isDebugEnabled()) {
log.debug(String.format("Closing Connection [%s], Pool active=[%s], idle=[%s]", conn.toString(),
connPool.getActive(), connPool.getIdle()));
}
}
}
}
Register this interceptor class in Context.xml
<Context>
<Resource
name="jdbc/acmedb"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;
com.acme.rest.config.MyConnectionPoolLogger"
/>
</Context>
JdbcInterceptor classes can be registered as either a Resource, as shown above, or as a POJO.
Log Samples
Below are some sample logs from Tomcat when accessing the Connection Pool
2017-11-04 00:15:19,389 DEBUG Getting Connection [PooledConnection[com.mysql.jdbc.JDBC4Connection@6dea96f]], Pool active=[1], idle=[0]
2017-11-04 00:15:19,393 DEBUG Returning Connection to Pool [ProxyConnection[PooledConnection[com.mysql.jdbc.JDBC4Connection@6dea96f]]]
2017-11-04 00:16:19,249 DEBUG Closing Connection [PooledConnection[com.mysql.jdbc.JDBC4Connection@6dea96f]], Pool active=[0], idle=[1]
Best Answer
There is no 100% right answer to this. It is a matter of trade-off and context.
But considering this as a corner-case, the testOnBorrow gives pretty good assurance.
Now the trade-off with that is that, every time you request a connection, a query (no matter how light-weight) is made to the database-server. This maybe very fast, but the cost is still not zero.
And if you have a busy application, with very good database-connection-reliability, then you'll start seeing from the data, that the COST of "validity check on every connection-request from the pool" outweighs the benefits of detecting connection issues.
It ensures to the max, that you have a good connection before you use it. Especially considering the cost (retry + manual intervention + loss of workflow etc.) of "not being able to recover easily" from a failed DB operation.
Now imagine if you have the testOnIdle option. This requires that your connections go idle (dependent on the idle timeout of the connection) before a sanity check can be made.
And one last data point is that for some applications, the critical-path is not the "validation query" time (in lower millis hopefully). The applications have bigger issues to deal with. And of course, for some applications, that time is very significant.