数据源连接池数配置对执行sql的影响

数据源连接池数配置对执行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的最大。