Talk about the default parameters of tomcat jdbc pool and poolSweeper

  jdbc

Order

This article mainly studies the default parameters of tomcat jdbc pool and poolSweeper.

Tomcat jdbc pool parameter default

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

判断是否开启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
After that, if any of the following conditions is met, it will be turned on.

Judgment condition Default value Result
getTimeBetweenEvictionRunsMillis()>0 The default is 5000 true
isRemoveAbandoned() && getRemoveAbandonedTimeout()>0 The default removeAbandoned is false and removeAbandonedTimeout is 60. false
getSuspectTimeout()>0 默认为0 false
isTestWhileIdle() && getValidationQuery()! =null 默认testWhileIdle为false,常见的mysql,pg,oracle的validationQuery不为空 false
getMinEvictableIdleTimeMillis()>0 The default value is 60000 true

The default is true, which opens 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;
        }

    }

The default here is to determine which type of database is based on the connection url, and then the default common databases have corresponding validationQuery.

TestOnBorrow is set to true if there is validationQuery.

Note that if the common spring.datasource is directly used for configuration, the common driver-class-name,url, username and password will be recognized, and the validationQuery will automatically judge according to the url. If it can be recognized, testOnBorrow will also be set to true, and other connection pool parameters, It needs to be specified according to the specific implementation, such as Spring.datasource.Tomcat.initial-size, otherwise it will not take effect.

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"),

About 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
    }

The initialization of the ConnectionPool constructor will call initializePoolCleaner to determine whether to open the poolCleaner. the default configuration is true, which will open the 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);
        }
    }

The main tasks of this timer are as follows

Task Conditions of execution Default value Result
checkAbandoned RemoveAbandoned is true or suspectTimeout is greater than 0 RemoveAbandoned is false and suspect timeout is 0. false
checkIdle pool.idel.size() > minIdle The default minIdle is 10.
testAllIdle TestWhileIdle is true The default is false. false

Since these tasks are executed sequentially, checkIdle () is the default parameter configuration that can be executed.
CheckAbandoned () is executed whenever removeAbandoned=true or suspectTimeout is greater than 0.
As long as testWhileIdle is true, testAllIdle () is executed

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 is greater than 0,removeAbandoned=true. checkAbandoned () will be executed when one of the two conditions is true
If removeAbandoned is false, only suspect judgment will be made.
If removeAbandoned is turned on, abandon is executed when the connection exceeds abandonTimeout, otherwise, suspect judgment is entered.
Abandon releases the connection, the disconnect/close connection

Abandon instance

Connection pool configuration

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

Instance code

    @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
        }
    }

Report an error

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

Summary

  • Additional attention should be paid to the parameter configuration of different connection pools.
  • Regarding the opening of abandon, the connection will be forcibly closed, which is global.

For the case where multiple statement are performed on the same connection, ResetAbandonedTimer can be used to prevent the connection from being dropped by the wrong abandon.

  • In springboot, the database will be automatically identified according to spring.datasource.url, and the default validationQuery will be obtained. If the value is not empty, testOnBorrow will be automatically set to true.
  • Since poolSweeper is executed every time-between-evidence-runs-mills-mills, and checkAbandoned, checkIdle, and testAllIdle are executed sequentially, since it is implemented by timer, once a task at a certain point in time is delayed, subsequent tasks are also delayed, and it is not guaranteed to be executed every time-between-evidence-runs-mills-mills, which requires extra attention.

doc