最近几天在做数据库结合应用的并发压力测试,发现并发压测性能一直上不去,透过应用日志发现主要慢在获取数据库连接上面,应用使用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