数据源连接池数配置对执行sql的影响
1 背景
目前java应用很少使用jdbc直接连接数据库执行sql了，且数据库连接是创建、关闭比较耗时的资源，池技术的引用数据源中是不可避免的。
使用数据库连接池会提高应用的性能，但是如果配置失误的话反而会适得其反，甚至引起应用的宕机。
前些日子发现有些明明很简单的sql，很简单，单次执行很快，但是有些时候执行时间却是数倍的变化。
2 设定与准备
设立单独的测试数据库，使用公司云主机
一条sql，执行时间是68ms左右
使用druid连接池进行测试，配置：初始连接数、最小连接数、最大连接数
使用newFixedThreadPool连接池模拟并发情况
监控指标：连接池活动的连接、空闲的连接，获取连接的时间、sql执行时间、执行总时间，线程等待数据源等待的线程（ps：获取改数量或影响连接池的性能，这里暂不考虑）
3 程序结果与解读
程序：
```
public class ConnectTest {
public static ThreadLocal timelocal =new ThreadLocal<>();
@Test
public void test_druidConnectPool()throws SQLException {
final MyDruidDataSource dataSource =new MyDruidDataSource();
Properties dbProperties =new Properties();
dbProperties.put("druid.initialSize", "3");
dbProperties.put("druid.minIdle", "3");
dbProperties.put("druid.maxActive", "15");
dbProperties.put("druid.maxWait", "5000");
dbProperties.put("druid.timeBetweenEvictionRunsMillis", "90000");
dbProperties.put("druid.minEvictableIdleTimeMillis", "1800000");
dbProperties.put("druid.testOnBorrow", "false");
dbProperties.put("druid.testOnReturn", "false");
dbProperties.put("druid.testWhileIdle", "true");
dbProperties.put("druid.name", "true");
dbProperties.put("druid.url", "jdbc:mysql:///sieve?zeroDateTimeBehavior=convertToNull");
dbProperties.put("druid.username", "");
dbProperties.put("druid.password", "");
dbProperties.put("druid.driverClassName", "com.mysql.jdbc.Driver");
dataSource.setConnectProperties(dbProperties);
try {
dataSource.init();
}catch (SQLException e) {
e.printStackTrace();
}
// 监控线程
ScheduledExecutorService cron= Executors.newScheduledThreadPool(1);
cron.scheduleAtFixedRate(new Runnable(){
@Override
public void run() {
System.out.println(dataSource);
// System.out.println(dataSource.getWaitThreadCount());
}
},0,500, TimeUnit.MILLISECONDS);
// // 增加链接线程
// ScheduledExecutorService cron1= Executors.newScheduledThreadPool(1);
// cron1.scheduleAtFixedRate(new Runnable(){
//
// @Override
// public void run() {
// System.out.println(dataSource);
// int i = dataSource.getMaxActive();
// dataSource.setMaxActive(i+1);
// }
// },0,1500, TimeUnit.MILLISECONDS);
int count =10000;
final CountDownLatch latch =new CountDownLatch(count);
ExecutorService executorService = Executors.newFixedThreadPool(15);
for (int i =0; i < count; i++) {
final int index = i;
executorService.submit(new Runnable() {
@Override
public void run() {
try {
ConnectTest.ExceTime time =new ConnectTest.ExceTime();
timelocal.set(time);
time.setIndex(index);
// exceSqlQuery(dataSource, "SELECT * FROM sieve.po_detail limit 1;");
exceSqlUpdate(dataSource, "");
}catch (Throwable e) {
e.printStackTrace();
}
latch.countDown();
}
});
}
try {
latch.await();
}catch (InterruptedException e) {
e.printStackTrace();
}
try {
Thread.sleep(5000);
}catch (InterruptedException e) {
e.printStackTrace();
}
}
public void exceSqlUpdate(MyDruidDataSource dataSource, String sql) {
Connection con =null;
PreparedStatement ps =null;
try {
con = dataSource.getConnection(timelocal);
ps = con.prepareStatement(sql);
timelocal.get().setStartSqlTime(new java.util.Date().getTime());
ps.execute(sql);
timelocal.get().setEndSqlTime(new java.util.Date().getTime());
}catch (Exception e) {
e.printStackTrace();
}finally {
if (ps !=null) {
try {
ps.close();
}catch (SQLException e) {
e.printStackTrace();
}
}
if (con !=null) {
try {
con.close();
}catch (SQLException e) {
e.printStackTrace();
}
}
}
timelocal.get().calculationTime();
timelocal.remove();
}
static class ExceTime {
private int index;
private long getConTime;
private long startSqlTime;
private long endSqlTime;
public int getIndex() {
return index;
}
public void setIndex(int index) {
this.index = index;
}
public long getGetConTime() {
return getConTime;
}
public void setGetConTime(long getConTime) {
this.getConTime = getConTime;
}
public long getStartSqlTime() {
return startSqlTime;
}
public void setStartSqlTime(long startSqlTime) {
this.startSqlTime = startSqlTime;
}
public long getEndSqlTime() {
return endSqlTime;
}
public void setEndSqlTime(long endSqlTime) {
this.endSqlTime = endSqlTime;
}
public void calculationTime(){
System.out.println("编号："+index+",获取con与开始sql距离：" + (startSqlTime - getConTime) +", sql执行时间:" + (endSqlTime - startSqlTime)
+",总时间:"+(endSqlTime - getConTime));
}
}
public void exceSqlQuery(MyDruidDataSource dataSource, String sql) {
Connection con =null;
PreparedStatement ps =null;
try {
con = dataSource.getConnection();
ps = con.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery(sql);
analysisResultSet(resultSet);
}catch (Exception e) {
e.printStackTrace();
}finally {
if (ps !=null) {
try {
ps.close();
}catch (SQLException e) {
e.printStackTrace();
}
}
if (con !=null) {
try {
con.close();
}catch (SQLException e) {
e.printStackTrace();
}
}
}
}
private void analysisResultSet(ResultSet resultSet)throws SQLException {
List> objectList =new ArrayList<>();
//获得ResultSetMeataData对象
ResultSetMetaData rsmd =null;
rsmd = resultSet.getMetaData();
int total_rows = rsmd.getColumnCount();
while (resultSet.next()) {
List list =new ArrayList<>();
//判断数据类型&获取value
for (int i =0; i < total_rows; i++) {
String columnName = rsmd.getColumnLabel(i +1);
try {
list.add(resultSet.getObject(columnName));
}catch (Exception e) {
e.printStackTrace();
}
}
objectList.add(list);
}
System.out.println(Arrays.deepToString(objectList.toArray()));
}
}
```
```
public class MyDruidDataSourceextends DruidDataSource {
public DruidPooledConnection getConnection(ThreadLocal local)throws SQLException {
local.get().setGetConTime(new Date().getTime());
DruidPooledConnection connection =super.getConnection();
return connection;
}
}
```
a:
配置
初始连接数=3、最小连接数=3、最大连接数=15 并发线程=15 任务量=10000
结果
执行总时间：4分10s
获取连接平均时间：300ms左右，有少量600ms+
机器性能：cpu使用增加1%- 2% 负载略有增加
总结
并发15对于连接池是3来说，是有一定获取压力的，获取对执行sql有一定影响，时间影响为4倍
附件
b：
配置
初始连接数=3、最小连接数=3、最大连接数=3 并发线程=50 任务量=10000
结果
执行总时间：4分10s
获取连接平均时间：1s+,有少量2s+，峰值5s+
机器性能：cpu使用增加1%- 2% 负载略有增加，与a持平
总结
并发量50对于连接池数量为3来说，压力巨大，获取对执行sql有巨大压力，时间影响为10+倍
附件
c:
配置
初始连接数=3、最小连接数=3、最大连接数=15 并发线程=15 任务量=10000
结果
执行总时间：1分2s
获取连接平均时间：1ms，在开始500次执行的过程中有少量200ms，当连接数=并发数时=15时，获取连接不需要耗费时间。
机器性能：cpu使用增加4.7%-5.3%，负载略有增加，与a相比增加5倍cpu使用率。
总结
连接池的连接动态增加会影响sql的执行总时间，推荐：初始数=最小数=最大连接数。
当连接数=并发数时=15时，获取连接不需要耗费时间。
当连接数增加时，意味着数据库会执行更多的任务，cpu使用率、负载都会升高。
附件
4 总结
可以看到，数据库连接池的设置会sql的执行、数据库的性能都是有很到影响的，过小会增加获取连接的开销、过大会影响数据库的cpu使用率。
当然影响数据库性能的因素还有很多，流量也是一个很重要的方便，过大的流量也会使db宕机，连接池也是对sql流量的一种限流措施。qps与db性能要综合考虑。
如果流量不大的话，并发=连接数可以达到qps的最大。
