c3p0的坑导致并发性能问题

最近几天在做数据库结合应用的并发压力测试，发现并发压测性能一直上不去，透过应用日志发现主要慢在获取数据库连接上面，应用使用c3p0连接池的方式。连接池的主要配置如下，

        <dbConnPools>
          <dbConnPool id="1" name="afa" type="0">
            <property name="User" value="xxx"/>
            <property name="Password" value="xxx"/>
            <property name="JdbcUrl" value="jdbc:t4jdbc://172.1.1.1:23400,172.1.1.2:23400,172.1.1.3:23400/"/>
            <property name="DriverClass" value="org.trafodion.jdbc.t4.T4Driver"/>
            <property name="InitialPoolSize" value="64"/>
            <property name="MinPoolSize" value="64"/>
            <property name="MaxPoolSize" value="128"/>
            <property name="AcquireIncrement" value="8"/>
            <property name="AcquireRetryAttempts" value="1"/>
            <property name="AcquireRetryDelay" value="1000"/>
            <property name="MaxIdleTime" value="1800"/>
            <property name="MaxIdleTimeExcessConnections" value="0"/>
            <property name="MaxConnectionAge" value="40"/>
            <property name="CheckoutTimeout" value="0"/>
            <property name="AutoCommitOnClose" value="true"/>
            <property name="BreakAfterAcquireFailure" value="false"/>
            <property name="MaxStatements" value="0"/>
            <property name="MaxStatementsPerConnection" value="0"/>
            <property name="PropertyCycle" value="0"/>
            <property name="UnreturnedConnectionTimeout" value="0"/>
            <property name="NumHelperThreads" value="3"/>
          </dbConnPool>

连接池设置的pool size已经不小了，但是为什么获取数据库连接仍然很慢。于是本地创建c3p0应用进行测试。
首先下载c3p0的2个驱动包，c3p0-0.9.5.5.jar及 mchange-commons-java-0.2.19.jar，确定如果准备c3p0-config.xml文件，文件内容根据以上进行配置，接着导入数据库的驱动包。
完成以上步骤之后，创建两个JAVA代码程序，/c3p0test/src/c3p0test/C3P0Test.java及/c3p0test/src/c3p0test/JDBCUtils.java，完成以后的工程目录如下

具体代码如下，

package c3p0test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtils {
    /**
     * 1.定义成员变量
     */
    private static DataSource ds;

    /**
     * 2.读取配置文件
     */
    static{
        ds = new ComboPooledDataSource();
    }

    /**
     * 3.获得连接
     */
    public static Connection getConn() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 4.释放连接
     */
    public static void close(PreparedStatement pstmt, Connection conn){
        close(null,pstmt,conn);

    }
    public static void close(ResultSet rs, PreparedStatement pstmt, Connection conn){
        if(rs!=null){
            try {
                rs.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }
}

package c3p0test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class C3P0Test {
   public static void main(String[] args) {

       Connection conn = null;
       PreparedStatement pstmt = null;

       try {
    	   
           //1.获取连接
    	   for (int i=0; i<20; i ++) {
    		   long time1 = System.currentTimeMillis();
    		   conn = JDBCUtils.getConn();
    		   long time2 = System.currentTimeMillis();
    		   System.out.println("get connection 1:"+(time2-time1));
    	   }
           //2.定义sql
           String sql = "SELECT sysdate FROM dual";
           //3.获取数据库操作对象
           pstmt = conn.prepareStatement(sql);
           //5.执行操作
           pstmt.execute(sql);
           
       } catch (SQLException e) {
           e.printStackTrace();
       } 
   }
}

--c3p0-config.xml文件
<c3p0-config>
   <default-config>
       <property name="driverClass">org.trafodion.jdbc.t4.T4Driver</property>
       <property name="jdbcUrl">jdbc:t4jdbc://172.1.1.1:23400,172.1.1.2:23400,172.1.1.3:23400/:</property>
       <property name="user">xxx</property>
       <property name="password">xxx</property>
       <property name="initialPoolSize">10</property>
       <property name="minPoolSize">10</property>
       <property name="maxPoolSize">20</property>
       <property name="numHelperThreads">3</property>
   </default-config>
</c3p0-config>

我们发现，<property name=“numHelperThreads”>3</property> 这个配置会导致很严重的连接性能问题，每个次获取连接会有一次特别耗时，代码输出内容如下，

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
MLog initialization issue: slf4j found no binding or threatened to use its (dangerously silent) NOPLogger. We consider the slf4j library not found.
五月 12, 2020 8:42:37 下午 com.mchange.v2.log.MLog 
信息: MLog clients using java 1.4+ standard logging.
五月 12, 2020 8:42:38 下午 com.mchange.v2.c3p0.C3P0Registry 
信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:18:33 -0800; debug? true; trace: 10]
五月 12, 2020 8:42:38 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1brb12zaa2gzescuwad0r|bebdb06, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.trafodion.jdbc.t4.T4Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1brb12zaa2gzescuwad0r|bebdb06, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:t4jdbc://172.31.234.15:23400,172.31.234.16:23400,172.31.234.17:23400/:schema=afa_dz;maxStatements=400;connectionTimeout=0;clipVarchar=1;applicationName=aaaaaaaaaaaaaa, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
get connection 1:5001
get connection 1:0
get connection 1:1
get connection 1:3381
get connection 1:6
get connection 1:37
get connection 1:3328
get connection 1:17
get connection 1:18
get connection 1:3384
get connection 1:20
get connection 1:10
get connection 1:3353
get connection 1:32
get connection 1:6
get connection 1:3349
get connection 1:22
get connection 1:10
get connection 1:3330
get connection 1:40

关于参数numHelperThreads，网上有博客说这是c3p0的坑，读者可以参考博客c3p0存在严重bug 的相关描述。
在修改numHelperThreads为一个比较大的值时，如与maxPoolSize相同时每次连接都很快了，程序输出如下，

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
MLog initialization issue: slf4j found no binding or threatened to use its (dangerously silent) NOPLogger. We consider the slf4j library not found.
五月 12, 2020 8:48:50 下午 com.mchange.v2.log.MLog 
信息: MLog clients using java 1.4+ standard logging.
五月 12, 2020 8:48:51 下午 com.mchange.v2.c3p0.C3P0Registry 
信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:18:33 -0800; debug? true; trace: 10]
五月 12, 2020 8:48:51 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1brb12zaa2h7ekk1i5y1i8|bebdb06, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.trafodion.jdbc.t4.T4Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1brb12zaa2h7ekk1i5y1i8|bebdb06, idleConnectionTestPeriod -> 0, initialPoolSize -> 20, jdbcUrl -> jdbc:t4jdbc://172.31.234.15:23400,172.31.234.16:23400,172.31.234.17:23400/:schema=afa_dz;maxStatements=400;connectionTimeout=0;clipVarchar=1;applicationName=aaaaaaaaaaaaaa, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 20, numHelperThreads -> 20, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
get connection 1:5041
get connection 1:0
get connection 1:0
get connection 1:0
get connection 1:0
get connection 1:0
get connection 1:0
get connection 1:0
get connection 1:0
get connection 1:0
get connection 1:0
get connection 1:1
get connection 1:0
get connection 1:0
get connection 1:0
get connection 1:1
get connection 1:0
get connection 1:0
get connection 1:1
get connection 1:0