聊聊tomcat jdbc pool的默认参数及poolSweeper

codecraft

本文主要研究一下tomcat jdbc pool的默认参数及poolSweeper

tomcat jdbc pool 参数默认值

  • initialSize = 10(默认值)
  • maxActive=100(默认值)
  • maxIdle=100(默认值)
  • minIdle=10(默认值)
  • maxWait=30000(默认值)
  • validationQueryTimeout=-1(默认值)
  • testOnBorrow=false(默认值)
  • testOnReturn=false(默认值)
  • testWhileIdel=false(默认值)
  • timeBetweenEvictionRunsMillis=5000(默认值)
  • minEvictableIdleTimeMillis=60000(默认值)
  • accessToUnderlyingConnectionAllowed=true(默认值)
  • removeAbandoned=false(默认值)
  • removeAbandonedTimeout=60(默认值)
  • logAbandoned=false(默认值)
  • validationInterval=3000(默认值)
  • testOnConnect=false(默认值)
  • fairQueue=true(默认值)
  • abandonWhenPercentageFull=0(默认值)
  • maxAge=0(默认值)
  • suspectTimeout=0(默认值)
  • alternateUsernameAllowed=false(默认值)
  • commitOnReturn=false(默认值)
  • rollbackOnReturn=false(默认值)
  • useDisposableConnectionFacade=true(默认值)
  • logValidationErrors=false(默认值)
  • propageInterruptState=false(默认值)
  • ignoreExceptionOnPreLoad=false(默认值)

判断是否开启poolSweeper

tomcat-jdbc-8.5.11-sources.jar!/org/apache/tomcat/jdbc/pool/PoolProperties.java

    @Override
    public boolean isPoolSweeperEnabled() {
        boolean timer = getTimeBetweenEvictionRunsMillis()>0;
        boolean result = timer && (isRemoveAbandoned() && getRemoveAbandonedTimeout()>0);
        result = result || (timer && getSuspectTimeout()>0);
        result = result || (timer && isTestWhileIdle() && getValidationQuery()!=null);
        result = result || (timer && getMinEvictableIdleTimeMillis()>0);
        return result;
    }
如果timeBetweenEvictionRunsMillis不大于0,则肯定是关闭的,默认值为5000;即默认为true
之后是如下几个条件满足任意一个则开启
判断条件 默认值 结果
getTimeBetweenEvictionRunsMillis()>0 默认为5000 true
isRemoveAbandoned() && getRemoveAbandonedTimeout()>0 默认removeAbandoned为false,removeAbandonedTimeout为60 false
getSuspectTimeout()>0 默认为0 false
isTestWhileIdle() && getValidationQuery()!=null 默认testWhileIdle为false,常见的mysql,pg,oracle的validationQuery不为空 false
getMinEvictableIdleTimeMillis()>0 默认值为60000 true
默认为true,即会开启poolSweeper

DataSourceConfiguration

spring-boot-autoconfigure-1.4.5.RELEASE-sources.jar!/org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration.java

@ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource", matchIfMissing = true)
    static class Tomcat extends DataSourceConfiguration {

        @Bean
        @ConfigurationProperties("spring.datasource.tomcat")
        public org.apache.tomcat.jdbc.pool.DataSource dataSource(
                DataSourceProperties properties) {
            org.apache.tomcat.jdbc.pool.DataSource dataSource = createDataSource(
                    properties, org.apache.tomcat.jdbc.pool.DataSource.class);
            DatabaseDriver databaseDriver = DatabaseDriver
                    .fromJdbcUrl(properties.determineUrl());
            String validationQuery = databaseDriver.getValidationQuery();
            if (validationQuery != null) {
                dataSource.setTestOnBorrow(true);
                dataSource.setValidationQuery(validationQuery);
            }
            return dataSource;
        }

    }
这里默认会根据连接url判断是哪类数据库,然后默认的常见数据库都有对应的validationQuery

如果有validationQuery,则testOnBorrow会被设置为true

注意,如果使用通用的spring.datasource直接来配置,通用的driver-class-name,url,username和password会被认,validationQuery根据url来自动判断,如果能识别出,则testOnBorrow也会被设置为true,其他的连接池的参数,就需要根据具体实现来具体指定,比如spring.datasource.tomcat.initial-size,否则不生效

validationQuery

  • DatabaseDriver

spring-boot-1.4.5.RELEASE-sources.jar!/org/springframework/boot/jdbc/DatabaseDriver.java

    /**
     * Apache Derby.
     */
    DERBY("Apache Derby", "org.apache.derby.jdbc.EmbeddedDriver", null,
            "SELECT 1 FROM SYSIBM.SYSDUMMY1"),

    /**
     * H2.
     */
    H2("H2", "org.h2.Driver", "org.h2.jdbcx.JdbcDataSource", "SELECT 1"),

    /**
     * HyperSQL DataBase.
     */
    HSQLDB("HSQL Database Engine", "org.hsqldb.jdbc.JDBCDriver",
            "org.hsqldb.jdbc.pool.JDBCXADataSource",
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.SYSTEM_USERS"),

    /**
     * SQL Lite.
     */
    SQLITE("SQLite", "org.sqlite.JDBC"),

    /**
     * MySQL.
     */
    MYSQL("MySQL", "com.mysql.jdbc.Driver",
            "com.mysql.jdbc.jdbc2.optional.MysqlXADataSource", "SELECT 1"),

    /**
     * Maria DB.
     */
    MARIADB("MySQL", "org.mariadb.jdbc.Driver", "org.mariadb.jdbc.MariaDbDataSource",
            "SELECT 1"),

    /**
     * Oracle.
     */
    ORACLE("Oracle", "oracle.jdbc.OracleDriver",
            "oracle.jdbc.xa.client.OracleXADataSource", "SELECT 'Hello' from DUAL"),

    /**
     * Postgres.
     */
    POSTGRESQL("PostgreSQL", "org.postgresql.Driver", "org.postgresql.xa.PGXADataSource",
            "SELECT 1"),

关于poolCleaner

tomcat-jdbc-8.5.11-sources.jar!/org/apache/tomcat/jdbc/pool/ConnectionPool.java

    /**
     * Instantiate a connection pool. This will create connections if initialSize is larger than 0.
     * The {@link PoolProperties} should not be reused for another connection pool.
     * @param prop PoolProperties - all the properties for this connection pool
     * @throws SQLException Pool initialization error
     */
    public ConnectionPool(PoolConfiguration prop) throws SQLException {
        //setup quick access variables and pools
        init(prop);
    }

    public void initializePoolCleaner(PoolConfiguration properties) {
        //if the evictor thread is supposed to run, start it now
        if (properties.isPoolSweeperEnabled()) {
            poolCleaner = new PoolCleaner(this, properties.getTimeBetweenEvictionRunsMillis());
            poolCleaner.start();
        } //end if
    }
ConnectionPool构造器初始化会调用initializePoolCleaner,判断是否开启poolCleaner,默认配置为true,即会开启poolCleaner

poolCleaner

protected static class PoolCleaner extends TimerTask {
        protected WeakReference<ConnectionPool> pool;
        protected long sleepTime;

        PoolCleaner(ConnectionPool pool, long sleepTime) {
            this.pool = new WeakReference<>(pool);
            this.sleepTime = sleepTime;
            if (sleepTime <= 0) {
                log.warn("Database connection pool evicter thread interval is set to 0, defaulting to 30 seconds");
                this.sleepTime = 1000 * 30;
            } else if (sleepTime < 1000) {
                log.warn("Database connection pool evicter thread interval is set to lower than 1 second.");
            }
        }

        @Override
        public void run() {
            ConnectionPool pool = this.pool.get();
            if (pool == null) {
                stopRunning();
            } else if (!pool.isClosed()) {
                try {
                    if (pool.getPoolProperties().isRemoveAbandoned()
                            || pool.getPoolProperties().getSuspectTimeout() > 0)
                        pool.checkAbandoned();
                    if (pool.getPoolProperties().getMinIdle() < pool.idle
                            .size())
                        pool.checkIdle();
                    if (pool.getPoolProperties().isTestWhileIdle())
                        pool.testAllIdle();
                } catch (Exception x) {
                    log.error("", x);
                }
            }
        }

        public void start() {
            registerCleaner(this);
        }

        public void stopRunning() {
            unregisterCleaner(this);
        }
    }
这个timer主要的任务如下
任务 执行条件 默认值 结果
checkAbandoned removeAbandoned为true或suspectTimeout大于0 removeAbandoned为false,suspectTimeout为0 false
checkIdle pool.idel.size() > minIdle 默认minIdle为10 --
testAllIdle testWhileIdle为true 默认为false false
由于这些任务是依次往下执行的,默认参数配置可以执行的是checkIdle()
只要removeAbandoned=true或者suspectTimeout大于0,就会执行checkAbandoned()
只要testWhileIdle为true,就会执行testAllIdle()

checkAbandoned

    /**
     * Iterates through all the busy connections and checks for connections that have timed out
     */
    public void checkAbandoned() {
        try {
            if (busy.size()==0) return;
            Iterator<PooledConnection> locked = busy.iterator();
            int sto = getPoolProperties().getSuspectTimeout();
            while (locked.hasNext()) {
                PooledConnection con = locked.next();
                boolean setToNull = false;
                try {
                    con.lock();
                    //the con has been returned to the pool or released
                    //ignore it
                    if (idle.contains(con) || con.isReleased())
                        continue;
                    long time = con.getTimestamp();
                    long now = System.currentTimeMillis();
                    if (shouldAbandon() && (now - time) > con.getAbandonTimeout()) {
                        busy.remove(con);
                        abandon(con);
                        setToNull = true;
                    } else if (sto > 0 && (now - time) > (sto * 1000L)) {
                        suspect(con);
                    } else {
                        //do nothing
                    } //end if
                } finally {
                    con.unlock();
                    if (setToNull)
                        con = null;
                }
            } //while
        } catch (ConcurrentModificationException e) {
            log.debug("checkAbandoned failed." ,e);
        } catch (Exception e) {
            log.warn("checkAbandoned failed, it will be retried.",e);
        }
    }
suspectTimeout大于0,removeAbandoned=true两个条件一个成立就会执行checkAbandoned()
如果removeAbandoned为false,则只会进行suspect判断
如果开启removeAbandoned,那么在连接超过abandonTimeout时执行abandon,否则进入suspect判断
abandon会释放连接,即disconnect/close连接

abandon实例

连接池配置

spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/postgres?connectTimeout=60&socketTimeout=60
    username: postgres
    password: postgres
    jmx-enabled: true
    tomcat:
      initial-size: 1
      max-active: 5
      ## when pool sweeper is enabled, extra idle connection will be closed
      max-idle: 5
      ## when idle connection > min-idle, poolSweeper will start to close
      min-idle: 1
      # PoolSweeper run interval abandon及suspect检测的执行间隔
      time-between-eviction-runs-millis: 30000
      remove-abandoned: true
      # how long a connection should return,if not return regard as leak connection
      remove-abandoned-timeout: 10
      # how long a connection should return, or regard as probably leak connection
      suspect-timeout: 10
      log-abandoned: true
      abandon-when-percentage-full: 0 ## (used/max-active*100f)>=perc -->shouldAbandon, if set 0 always abandon
      # idle connection idle time before close
      min-evictable-idle-time-millis: 60000
      validation-query: select 1
      validation-interval: 30000

实例代码

    @Test
    public void testConnAbandon() throws SQLException {
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false); //NOTE pg 为了设置fetchSize,必须设置为false
        String sql = "select * from demo_table";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)connection.prepareStatement(sql);
            pstmt.setFetchSize(10);
            ResultSet rs = pstmt.executeQuery(); //NOTE 设置Statement执行完成的超时时间,前提是socket的timeout比这个大
            //NOTE 这里返回了就代表statement执行完成,pg会顺带返回fetchSize大小的第一批数据,mysql不会返回第一批数据
            int col = rs.getMetaData().getColumnCount();
            System.out.println("============================");
            while (rs.next()) { //NOTE 这个的timeout由socket的超时时间设置,oracle.jdbc.ReadTimeout=60000
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getObject(i));
                }
                System.out.println("");
                TimeUnit.SECONDS.sleep(1); //NOTE 这里模拟连接被abandon
            }
            System.out.println("============================");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //close resources
        }
    }

报错

2018-01-27 11:48:59.891  WARN 1004 --- [:1517024909680]] o.a.tomcat.jdbc.pool.ConnectionPool      : Connection has been abandoned PooledConnection[org.postgresql.jdbc.PgConnection@6c6bdce1]:java.lang.Exception
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1102)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:807)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:651)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:198)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:132)
    at com.demo.JpaDemoApplicationTests.testConnAbandon(JpaDemoApplicationTests.java:59)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:2389)
    at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1841)
    at com.demo.JpaDemoApplicationTests.testConnAbandon(JpaDemoApplicationTests.java:70)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
Caused by: java.io.IOException: Stream closed
    at sun.nio.cs.StreamEncoder.ensureOpen(StreamEncoder.java:45)
    at sun.nio.cs.StreamEncoder.flush(StreamEncoder.java:140)
    at java.io.OutputStreamWriter.flush(OutputStreamWriter.java:229)
    at org.postgresql.core.PGStream.flush(PGStream.java:549)
    at org.postgresql.core.v3.QueryExecutorImpl.sendSync(QueryExecutorImpl.java:1333)
    at org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:2383)
    ... 34 more

小结

  • 对于不同连接池的参数配置,需要额外注意.
  • 关于开启abandon,则会把连接强制关闭掉,这个是全局的
对于在同一个连接执行多个statement的情况,可以使用ResetAbandonedTimer来避免被错误abandon掉连接
  • 在springboot中会根据spring.datasource.url自动识别数据库,同时拿到默认的validationQuery,如果该值不为空,则testOnBorrow会被自动设置为true
  • 由于poolSweeper是每隔time-between-eviction-runs-millis时间执行一次,而且是checkAbandoned,checkIdle,testAllIdle依次往下执行,由于它是采用timer实现的,因此一旦某个时间点的任务延时了,后续也一并延时,并不能保证每隔time-between-eviction-runs-millis时间执行一次,这点需要额外注意。

doc

阅读 3.4k

code-craft
spring boot , docker and so on 欢迎关注微信公众号: geek_luandun

当一个代码的工匠回首往事时,不因虚度年华而悔恨,也不因碌碌无为而羞愧,这样,当他老的时候,可以很自豪告诉世人,我曾经将代码注入生命去打造互联网的浪潮之巅,那是个很疯狂的时代,我在一波波的浪潮上留下了或重如泰山或轻如鸿毛的几笔。

11.3k 声望
1.1k 粉丝
0 条评论

当一个代码的工匠回首往事时,不因虚度年华而悔恨,也不因碌碌无为而羞愧,这样,当他老的时候,可以很自豪告诉世人,我曾经将代码注入生命去打造互联网的浪潮之巅,那是个很疯狂的时代,我在一波波的浪潮上留下了或重如泰山或轻如鸿毛的几笔。

11.3k 声望
1.1k 粉丝
宣传栏