java database connection pools benchmark

不同的数据库连接池100000条记录插入性能测试，以下结果基于 mysql 的 jdbc 驱动，代码参考druid项目的 benchmark 测试代码。

配置数据库最大连接数为 1000，并运行之前先创建表语句如下：

-- 设置最大连接数
SET GLOBAL max_connections = 1000;

CREATE TABLE `insert_test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `type` varchar(25) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

结论

hikari的性能是最好的。

测试一： JMH Benchmark

import com.alibaba.druid.pool.DruidDataSource;
import com.jolbox.bonecp.BoneCPDataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.zaxxer.hikari.HikariDataSource;
import org.openjdk.jmh.annotations.*;
import org.openjdk.jmh.runner.Runner;
import org.openjdk.jmh.runner.RunnerException;
import org.openjdk.jmh.runner.options.Options;
import org.openjdk.jmh.runner.options.OptionsBuilder;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

@Warmup(iterations = DataSourcePoolMicroBenchmark.DEFAULT_WARMUP_ITERATIONS)
@Measurement(iterations = DataSourcePoolMicroBenchmark.DEFAULT_MEASURE_ITERATIONS)
@Fork(DataSourcePoolMicroBenchmark.DEFAULT_FORKS)
@State(Scope.Thread)
public class DataSourcePoolMicroBenchmark {

    static final int DEFAULT_WARMUP_ITERATIONS = 10;

    static final int DEFAULT_MEASURE_ITERATIONS = 10;

    static final int DEFAULT_FORKS = 2;

    String jdbcUrl = "jdbc:mysql://localhost/jdbc?useSSL=false";

    String user = "root";

    String password = "test.yu";

    String driverClass = "com.mysql.jdbc.Driver";

    int initialSize = 10;

    int minPoolSize = 10;

    int maxPoolSize = 50;

    int maxActive = 50;

    String validationQuery = "SELECT 1";

    int threadCount = 50;

    int count = 10000 / threadCount;

    HikariDataSource hikariDataSource = new HikariDataSource();

    DruidDataSource druidDataSource = new DruidDataSource();

    org.apache.commons.dbcp.BasicDataSource dbcpDataSource = new org.apache.commons.dbcp.BasicDataSource();

    org.apache.commons.dbcp2.BasicDataSource dbcp2DataSource = new org.apache.commons.dbcp2.BasicDataSource();

    BoneCPDataSource boneCPDataSource = new BoneCPDataSource();

    ComboPooledDataSource c3p0DataSource = new ComboPooledDataSource();

    org.apache.tomcat.jdbc.pool.DataSource tomcatJdbcDataSource = new org.apache.tomcat.jdbc.pool.DataSource();

    {
        hikariCP();
        druid();
        dbcp();
        dbcp2();
        bonecp();
        c3p0();
        tomcatJdbc();
    }

    void hikariCP() {
        hikariDataSource.setMaximumPoolSize(maxPoolSize);
        hikariDataSource.setJdbcUrl(jdbcUrl);
        hikariDataSource.setUsername(user);
        hikariDataSource.setPassword(password);
        hikariDataSource.setConnectionTestQuery("SELECT 1");
        hikariDataSource.setPoolName("hikariCP");
        hikariDataSource.setMaxLifetime(1800000L);
        hikariDataSource.setIdleTimeout(600000);
        hikariDataSource.setReadOnly(false);
        hikariDataSource.setConnectionTimeout(30000);
    }

    @Benchmark
    public void hikariBenchmark() {
        run(hikariDataSource, "hikari");
    }

    void druid() {
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setMinIdle(minPoolSize);
        druidDataSource.setPoolPreparedStatements(true);
        druidDataSource.setDriverClassName(driverClass);
        druidDataSource.setUrl(jdbcUrl);
        druidDataSource.setPoolPreparedStatements(true);
        druidDataSource.setUsername(user);
        druidDataSource.setPassword(password);
        druidDataSource.setValidationQuery(validationQuery);
        druidDataSource.setTestOnBorrow(false);
    }

    @Benchmark
    public void druidBenchmark() {
        run(druidDataSource, "druid");
    }

    void dbcp() {
        dbcpDataSource.setInitialSize(initialSize);
        dbcpDataSource.setMaxActive(maxActive);
        dbcpDataSource.setMinIdle(minPoolSize);
        dbcpDataSource.setMaxIdle(maxPoolSize);
        dbcpDataSource.setPoolPreparedStatements(true);
        dbcpDataSource.setDriverClassName(driverClass);
        dbcpDataSource.setUrl(jdbcUrl);
        dbcpDataSource.setPoolPreparedStatements(true);
        dbcpDataSource.setUsername(user);
        dbcpDataSource.setPassword(password);
        dbcpDataSource.setValidationQuery("SELECT 1");
        dbcpDataSource.setTestOnBorrow(false);
    }

    @Benchmark
    public void dbcpBenchmark() {
        run(dbcpDataSource, "dbcp");
    }

    void dbcp2() {
        dbcp2DataSource.setInitialSize(initialSize);
        dbcp2DataSource.setMaxTotal(maxActive);
        dbcp2DataSource.setMinIdle(minPoolSize);
        dbcp2DataSource.setMaxIdle(maxPoolSize);
        dbcp2DataSource.setPoolPreparedStatements(true);
        dbcp2DataSource.setDriverClassName(driverClass);
        dbcp2DataSource.setUrl(jdbcUrl);
        dbcp2DataSource.setPoolPreparedStatements(true);
        dbcp2DataSource.setUsername(user);
        dbcp2DataSource.setPassword(password);
        dbcp2DataSource.setValidationQuery("SELECT 1");
        dbcp2DataSource.setTestOnBorrow(false);
    }

    @Benchmark
    public void dbcp2Benchmark() {
        run(dbcp2DataSource, "dbcp2");
    }

    void bonecp() {
        boneCPDataSource.setMinConnectionsPerPartition(minPoolSize);
        boneCPDataSource.setMaxConnectionsPerPartition(maxPoolSize);

        boneCPDataSource.setDriverClass(driverClass);
        boneCPDataSource.setJdbcUrl(jdbcUrl);
        boneCPDataSource.setStatementsCacheSize(100);
        boneCPDataSource.setServiceOrder("LIFO");
        boneCPDataSource.setUsername(user);
        boneCPDataSource.setPassword(password);
        boneCPDataSource.setConnectionTestStatement("SELECT 1");
        boneCPDataSource.setPartitionCount(1);
        boneCPDataSource.setAcquireIncrement(5);
        boneCPDataSource.setIdleConnectionTestPeriodInMinutes(0L);
        boneCPDataSource.setDisableConnectionTracking(true);
    }

    @Benchmark
    public void boneCPBenchmark() {
        run(boneCPDataSource, "boneCp");
    }

    void c3p0() {
        c3p0DataSource.setMinPoolSize(minPoolSize);
        c3p0DataSource.setMaxPoolSize(maxPoolSize);

        try {
            c3p0DataSource.setDriverClass(driverClass);
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }

        c3p0DataSource.setJdbcUrl(jdbcUrl);
        c3p0DataSource.setUser(user);
        c3p0DataSource.setPassword(password);
    }

    @Benchmark
    public void c3p0Benchmark() {
        run(c3p0DataSource, "c3p0");
    }

    void tomcatJdbc() {
        tomcatJdbcDataSource.setMaxIdle(maxPoolSize);
        tomcatJdbcDataSource.setMinIdle(minPoolSize);
        tomcatJdbcDataSource.setMaxActive(maxPoolSize);

        tomcatJdbcDataSource.setDriverClassName(driverClass);
        tomcatJdbcDataSource.setUrl(jdbcUrl);
        tomcatJdbcDataSource.setUsername(user);
        tomcatJdbcDataSource.setPassword(password);
    }

    @Benchmark
    public void tomcatJdbcBenchmark() {
        run(tomcatJdbcDataSource, "tomcat-jdbc");
    }

    private void run(final DataSource dataSource, final String name) {
        try {
            for (int i = 0; i < count; ++i) {
                Connection conn = dataSource.getConnection();
                Statement statement = conn.createStatement();
                statement.executeUpdate("INSERT INTO insert_test(`type`) VALUES ('" + name + "')");
                statement.close();
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws RunnerException {
        Options opt = new OptionsBuilder()
                .include(DataSourcePoolMicroBenchmark.class.getSimpleName())
                .build();
        new Runner(opt).run();
    }

}

程序运行结果输出如下，Hikari的得分最高：

Benchmark                                          Mode  Cnt  Score   Error  Units
DataSourcePoolMicroBenchmark.boneCPBenchmark      thrpt   20  2.324 ± 0.092  ops/s
DataSourcePoolMicroBenchmark.c3p0Benchmark        thrpt   20  5.237 ± 0.358  ops/s
DataSourcePoolMicroBenchmark.dbcp2Benchmark       thrpt   20  5.509 ± 0.443  ops/s
DataSourcePoolMicroBenchmark.dbcpBenchmark        thrpt   20  5.772 ± 0.444  ops/s
DataSourcePoolMicroBenchmark.druidBenchmark       thrpt   20  5.616 ± 0.267  ops/s
DataSourcePoolMicroBenchmark.hikariBenchmark      thrpt   20  5.837 ± 0.332  ops/s
DataSourcePoolMicroBenchmark.tomcatJdbcBenchmark  thrpt   20  5.675 ± 0.224  ops/s

测试二：多线程压测

package com.example.pool.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import com.jolbox.bonecp.BoneCPDataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.Test;

import javax.sql.DataSource;
import java.lang.management.ManagementFactory;
import java.lang.management.ThreadInfo;
import java.sql.Connection;
import java.sql.Statement;
import java.text.NumberFormat;
import java.util.concurrent.CountDownLatch;

public class DataSourcePoolBenchmark {

    String jdbcUrl = "jdbc:mysql://localhost/jdbc?useSSL=false";

    String user = "root";

    String password = "test.yu";

    String driverClass = "com.mysql.jdbc.Driver";

    int initialSize = 10;

    int minPoolSize = 10;

    int maxPoolSize = 50;

    int maxActive = 50;

    String validationQuery = "SELECT 1";

    int threadCount = 50;

    int loopCount = 10;

    int count = 10000 / threadCount;

    @Test
    public void hikari() throws Exception {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setMaximumPoolSize(maxPoolSize);
        dataSource.setJdbcUrl(jdbcUrl);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setConnectionTestQuery("SELECT 1");
        dataSource.setPoolName("hikariCP");
        dataSource.setMaxLifetime(1800000L);
        dataSource.setIdleTimeout(600000);
        dataSource.setReadOnly(false);
        dataSource.setConnectionTimeout(30000);

        long sum = 0;
        for (int i = 0; i < loopCount; ++i) {
            sum += run(dataSource, "hikariCP", threadCount);
        }
        System.out.printf("%20s\t%d%n", "hikariCP", sum);
    }

    @Test
    public void druid() throws Exception {
        DruidDataSource dataSource = new DruidDataSource();

        dataSource.setInitialSize(initialSize);
        dataSource.setMaxActive(maxActive);
        dataSource.setMinIdle(minPoolSize);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(jdbcUrl);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestOnBorrow(false);

        long sum = 0;
        for (int i = 0; i < loopCount; ++i) {
            sum += run(dataSource, "druid", threadCount);
        }
        System.out.printf("%20s\t%d%n", "druid", sum);
    }

    @Test
    public void dbcp() throws Exception {
        final org.apache.commons.dbcp.BasicDataSource dataSource = new org.apache.commons.dbcp.BasicDataSource();

        dataSource.setInitialSize(initialSize);
        dataSource.setMaxActive(maxActive);
        dataSource.setMinIdle(minPoolSize);
        dataSource.setMaxIdle(maxPoolSize);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(jdbcUrl);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTestOnBorrow(false);

        long sum = 0;
        for (int i = 0; i < loopCount; ++i) {
            sum += run(dataSource, "dbcp", threadCount);
        }
        System.out.printf("%20s\t%d%n", "dbcp", sum);
    }

    @Test
    public void dbcp2() throws Exception {
        final org.apache.commons.dbcp2.BasicDataSource dataSource = new org.apache.commons.dbcp2.BasicDataSource();

        dataSource.setInitialSize(initialSize);
        dataSource.setMaxTotal(maxActive);
        dataSource.setMinIdle(minPoolSize);
        dataSource.setMaxIdle(maxPoolSize);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(jdbcUrl);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTestOnBorrow(false);

        long sum = 0;
        for (int i = 0; i < loopCount; ++i) {
            sum += run(dataSource, "dbcp2", threadCount);
        }
        System.out.printf("%20s\t%d%n", "dbcp2", sum);
    }

    @Test
    public void bonecp() throws Exception {
        BoneCPDataSource dataSource = new BoneCPDataSource();
        dataSource.setMinConnectionsPerPartition(minPoolSize);
        dataSource.setMaxConnectionsPerPartition(maxPoolSize);

        dataSource.setDriverClass(driverClass);
        dataSource.setJdbcUrl(jdbcUrl);
        dataSource.setStatementsCacheSize(100);
        dataSource.setServiceOrder("LIFO");
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setConnectionTestStatement("SELECT 1");
        dataSource.setPartitionCount(1);
        dataSource.setAcquireIncrement(5);
        dataSource.setIdleConnectionTestPeriodInMinutes(0L);
        dataSource.setDisableConnectionTracking(true);

        long sum = 0;
        for (int i = 0; i < loopCount; ++i) {
            sum += run(dataSource, "boneCP", threadCount);
        }
        System.out.printf("%20s\t%d%n", "boneCP", sum);
    }

    @Test
    public void c3p0() throws Exception {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setMinPoolSize(minPoolSize);
        dataSource.setMaxPoolSize(maxPoolSize);

        dataSource.setDriverClass(driverClass);
        dataSource.setJdbcUrl(jdbcUrl);
        dataSource.setUser(user);
        dataSource.setPassword(password);

        long sum = 0;
        for (int i = 0; i < loopCount; ++i) {
            sum += run(dataSource, "c3p0", threadCount);
        }
        System.out.printf("%20s\t%d%n", "c3p0", sum);
    }

    @Test
    public void tomcatJdbc() throws Exception {
        org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource();
        dataSource.setMaxIdle(maxPoolSize);
        dataSource.setMinIdle(minPoolSize);
        dataSource.setMaxActive(maxPoolSize);

        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(jdbcUrl);
        dataSource.setUsername(user);
        dataSource.setPassword(password);

        long sum = 0;
        for (int i = 0; i < loopCount; ++i) {
            sum += run(dataSource, "tomcat-jdbc", threadCount);
        }
        System.out.printf("%20s\t%d%n", "tomcat-jdbc", sum);
    }

    private long run(final DataSource dataSource, final String name, int threadCount) throws Exception {
        final CountDownLatch startLatch = new CountDownLatch(1);
        final CountDownLatch endLatch = new CountDownLatch(threadCount);
        final CountDownLatch dumpLatch = new CountDownLatch(1);

        Thread[] threads = new Thread[threadCount];
        for (int i = 0; i < threadCount; ++i) {
            Thread thread = new Thread() {

                public void run() {
                    try {
                        startLatch.await();
                        for (int i = 0; i < count; ++i) {
                            Connection conn = dataSource.getConnection();
                            // 并发插入数据测试，压入完可改成 SELECT 测试
                            Statement statement = conn.createStatement();
                            statement.executeUpdate("INSERT INTO insert_test(`type`) VALUES ('" + name + "')");
                            statement.close();
                            conn.close();
                        }
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                    endLatch.countDown();

                    try {
                        dumpLatch.await();
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                }
            };
            threads[i] = thread;
            thread.start();
        }

        long startMillis = System.currentTimeMillis();
        startLatch.countDown();
        endLatch.await();

        long[] threadIdArray = new long[threads.length];
        for (int i = 0; i < threads.length; ++i) {
            threadIdArray[i] = threads[i].getId();
        }
        ThreadInfo[] threadInfoArray = ManagementFactory.getThreadMXBean().getThreadInfo(threadIdArray);

        dumpLatch.countDown();

        long blockedCount = 0;
        long waitedCount = 0;
        for (int i = 0; i < threadInfoArray.length; ++i) {
            ThreadInfo threadInfo = threadInfoArray[i];
            blockedCount += threadInfo.getBlockedCount();
            waitedCount += threadInfo.getWaitedCount();
        }

        long millis = System.currentTimeMillis() - startMillis;

        StringBuilder stringBuilder = new StringBuilder().append("thread ").append(threadCount)
                                                         .append(" ").append(name).append(" millis ")
                                                         .append(NumberFormat.getInstance().format(millis))
                                                         .append(" blockedCount ")
                                                         .append(NumberFormat.getInstance().format(blockedCount))
                                                         .append(" waitedCount ")
                                                         .append(NumberFormat.getInstance().format(waitedCount));

        // System.out.println(stringBuilder.toString());
        return millis;
    }

}

运行结果如下(数值越小越好)：

| Connection Pool | MilliSeconds |
|          boneCP | 81196        |
|        hikariCP | 34557        |
|            c3p0 | 38409        |
|            dbcp | 34947        |
|           dbcp2 | 34749        |
|           druid | 34136        |
|     tomcat-jdbc | 36186        |

根据上面多线程压测的结果来看，druid和hikari的结果是最好的。

References

1. durid benchmark