java - Tomcat/Spring 连接池 - 列出连接项

Question

尝试调试具有与 Missing connections in tomcat jdbc connection pool 类似问题的应用程序.我目前正在使用:

• Tomcat 6.0.32 - 公司要求
• Tomcat JDBC 池 1.0.9.3
• Java 1.6.x - 公司要求
• Spring 3.2.14
• myBatis 3.2.8
• quartz 2.2.2

混合使用 Spring Web 请求以及使用池的 Quartz 触发作业。运行大约 5 次后，池中不再有可用连接，initialSize=5，maxActive=10。增加 maxActive 没有任何作用，它只是在池变空之前需要更长的时间和更多的 Quartz 运行。

2016-05-19 08:59:46,027 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 1 triggers
2016-05-19 09:00:00,001 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 0 triggers
2016-05-19 09:00:00,003 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6   JobRunShell.run  201 : Calling execute on job DEFAULT.refMigrDetail
2016-05-19 09:00:00,003 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6   DataSourceUtils.doGetConnection  110 : Fetching JDBC Connection from DataSource
2016-05-19 09:00:24,409 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 0 triggers
2016-05-19 09:00:30,004 ERROR org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6   CMRoreReferenceMigrator.run   93 : org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: [org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[10 in use].
2016-05-19 09:00:49,378 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 0 triggers

到目前为止我做了什么:

• 从 Apache Commons DBCP 切换到 Tomcat JDBC 池。这是有效的。
• 为 Tomcat JDBC 池启用 DEBUG。也在工作
• 在 Tomcat 上启用 JMX 以使用 jvisualvm 监控连接池 - 这不起作用，因为 Spring 实际上是在创建池，而不是 Tomcat。

在为 Tomcat JDBC 池启用 DEBUG 的情况下，日志记录不会显示未释放连接的位置/原因。对于 JMX 路由，由于我没有使用 Spring-Boot，我似乎无法在 Spring 中启用 JMX 来查看连接池。

我的问题是，有没有办法从 SPRING(不是 Tomcat)获取 Spring Connection Pool 对象/实例，然后遍历所有连接并列出每个连接的一些简单属性，例如 Activity 、使用中、关闭等...

我的想法是，我只是编写一些 Java 代码，在 Quartz 作业前后将连接池列表转储到日志中。转储出我在 Web 应用程序之前和之后执行的关键 Spring Web 事务的列表。

我的Spring连接池配置，DB2数据库:

<bean id="rrfecfDataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    <property name="driverClassName" value="${rrfecf.datasource.jdbcDriver}" />
    <property name="url" value="${rrfecf.dataousrce.databaseUrl}" />
    <property name="username" value="${rrfecf.datasource.username}" />
    <property name="password" value="${password:rrfecf.datasource}" />
    <property name="initialSize" value="5" />
    <property name="maxActive" value="10" />
    <property name="defaultAutoCommit" value="false" />
    <property name="maxIdle" value="5" />
    <property name="minIdle" value="0" />
    <property name="testOnBorrow" value="true" />
    <property name="testOnReturn" value="false" />
    <property name="testWhileIdle" value="true" />
    <property name="validationQuery" value="SELECT CURRENT_DATE FROM sysibm.sysdummy1" />
    <property name="timeBetweenEvictionRunsMillis" value="600000" /> <!-- 10 minutes -->
    <property name="minEvictableIdleTimeMillis" value="900000" /> <!-- 15 minutes -->
</bean>

myBatis Spring 配置:

<bean id="rrfecfSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="configLocation" value="WEB-INF/sqlMapConfig.xml"/>
    <property name="dataSource" ref="rrfecfDataSource" />
</bean>

Answer 1

这个“似乎”有效，不确定是否有更好的方法:

• 在 Spring 制作了一个 ApplicationContextAware Bean。
• 将该 ApplicationContextAware Bean 连接到 Quartz 类。
• 在 Quartz 类(class)中，我是:
  • 使用 ApplicationContextAware Bean 获取连接池。
  • 使用 for 循环遍历所有连接并转储属性

ApplicationContextAware 类:

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;

/**
 * <p>
 * The <code>BatchApplicationContextProvider</code> provides...
 * </p>
 */

public class BatchApplicationContextProvider implements ApplicationContextAware {

    public static final String ID              = BatchApplicationContextProvider.class.getName ();
    private String             SHORT_NAME      = "BatchApplicationContextProvider()";
    @SuppressWarnings("unused")
    private String              SYSTEM_IDENTITY = String.valueOf ( System.identityHashCode ( this ) );

    private ApplicationContext  context;

    /**
     * 
     */
    public BatchApplicationContextProvider() {
        // TODO Auto-generated constructor stub
    }

    /* (non-Javadoc)
     * @see org.springframework.context.ApplicationContextAware#setApplicationContext(org.springframework.context.ApplicationContext)
     */
    @Override
    public void setApplicationContext(ApplicationContext arg0)
        throws BeansException {
        this.context = arg0;
    }

    public ApplicationContext getApplicationContext() {
        return context;
    }

    public org.apache.tomcat.jdbc.pool.DataSource   getConnectionPool () { 
        return ( DataSource ) this.context.getBean ( "rrfecfDataSource" );
    }

}

示例用法:

public void        dumpConnectionPool () { 
    org.apache.tomcat.jdbc.pool.DataSource  lthePool = null;
    ConnectionPool  lbusyPool = null;
    try { 
        lthePool   = getBatchContext ().getConnectionPool ();
        lbusyPool  = lthePool.getPool ();
        if ( lbusyPool != null ) { 
            if ( lbusyPool.getSize () > 0 ) { 
                getLog().info ( SHORT_NAME + ".run() - ConnectionPool - size....................[" + lbusyPool.getSize () + "] idle [" + lbusyPool.getIdle () + "] active [" + lbusyPool.getActive () + "]"  );
                //getLog().info ( SHORT_NAME + ".run() - ConnectionPool - idle....................[" + lbusyPool.getIdle () + "]" );
                //getLog().info ( SHORT_NAME + ".run() - ConnectionPool - active..................[" + lbusyPool.getActive () + "]" );
                //PoolConfiguration       lpoolCfg = lbusyPool.getPoolProperties ();
            }
        }

        lbusyPool = null;
        lthePool = null;

    }
    catch ( Exception ltheXcp ) { 
        log.error ( ltheXcp );
    }
    finally { 

    }


}

我希望有人有更优雅的方法......这对我来说似乎是蛮力，但此时我需要在 Web 应用程序中解决这个问题。

编辑:dumpConnectionPool() 有问题...for 循环在遍历时似乎卡在所有连接上，使它们都处于“Activity ”状态。致力于此...

编辑:取出 for 循环...设置一个 connection = null 似乎不会将其释放回连接池。

成功:

虽然不漂亮，但 dumpConnectionPool() 显示了问题 list 本身:

Quartz 运行前的 dumpConnectionPool()，显示 5 个空闲，0 个 Activity :

2016-05-20 11:20:00,078  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  138 : -----------------------------------------------------------------------------------------------------------------
2016-05-20 11:20:00,078  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.dumpConnectionPool  257 : RunReferenceMigrator().run() - ConnectionPool - size....................[5] idle [5] active [0]
2016-05-20 11:20:00,078  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  140 : -----------------------------------------------------------------------------------------------------------------

Quartz 运行后的 dumpConnectionPool() 显示 4 个空闲，1 个 Activity :

2016-05-20 11:24:45,063  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  161 : -----------------------------------------------------------------------------------------------------------------
2016-05-20 11:24:45,063  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.dumpConnectionPool  257 : RunReferenceMigrator().run() - ConnectionPool - size....................[5] idle [4] active [1]
2016-05-20 11:24:45,063  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  163 : -----------------------------------------------------------------------------------------------------------------