The pit of c3p0 leads to concurrent performance problems

In recent days, I have been doing concurrent stress testing of database combined applications , It is found that the performance of concurrent pressure measurement has not been improved , Through the application log, it is found that the main slowness is to obtain the database connection , Application and use c3p0 How to connect the pool . The main configurations of connection pool are as follows: ,

        <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>

Connection pool settings pool size It's not small , But why is it still slow to get database connections . So create locally c3p0 The application is tested .
First download c3p0 Of 2 Drive packages ,c3p0-0.9.5.5.jar And mchange-commons-java-0.2.19.jar, Make sure if you're ready c3p0-config.xml file , The file content is configured according to the above , Then import the driver package of the database .
After completing the above steps , Create two JAVA Code program ,/c3p0test/src/c3p0test/C3P0Test.java And /c3p0test/src/c3p0test/JDBCUtils.java, The project catalogue after completion is as follows

The specific code is as follows ,

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. Define member variables 
     */
    private static DataSource ds;

    /**
     * 2. Read configuration file 
     */
    static{
        ds = new ComboPooledDataSource();
    }

    /**
     * 3. Obtain a connection 
     */
    public static Connection getConn() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 4. Release the connection 
     */
    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. Get the connection 
    	   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. Definition sql
           String sql = "SELECT sysdate FROM dual";
           //3. Get database operation object 
           pstmt = conn.prepareStatement(sql);
           //5. Perform the operation 
           pstmt.execute(sql);
           
       } catch (SQLException e) {
           e.printStackTrace();
       } 
   }
}

--c3p0-config.xml file 
<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>

We found that ,<property name=“numHelperThreads”>3</property> This configuration can cause serious connection performance problems , Each time you get a connection, it takes a lot of time , The output of the code is as follows ,

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.
 May  12, 2020 8:42:37  Afternoon  com.mchange.v2.log.MLog 
 Information : MLog clients using java 1.4+ standard logging.
 May  12, 2020 8:42:38  Afternoon  com.mchange.v2.c3p0.C3P0Registry 
 Information : Initializing c3p0-0.9.5.5 [built 11-December-2019 22:18:33 -0800; debug? true; trace: 10]
 May  12, 2020 8:42:38  Afternoon  com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
 Information : 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

About parameters numHelperThreads, There are blogs on the Internet that say this is c3p0 The pit of , Readers can refer to the blog c3p0 There are serious problems bug Related description of .
In the revision numHelperThreads For a relatively large value , Such as and maxPoolSize When the same, each connection is fast , The program output is as follows ,

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.
 May  12, 2020 8:48:50  Afternoon  com.mchange.v2.log.MLog 
 Information : MLog clients using java 1.4+ standard logging.
 May  12, 2020 8:48:51  Afternoon  com.mchange.v2.c3p0.C3P0Registry 
 Information : Initializing c3p0-0.9.5.5 [built 11-December-2019 22:18:33 -0800; debug? true; trace: 10]
 May  12, 2020 8:48:51  Afternoon  com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
 Information : 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