sequence

This article focuses on tomcat JDBC pool default parameters and poolSweeper

Tomcat JDBC pool Parameter Default value

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

Check whether the poolSweeper is enabled

Tomcat JDBC – 8.5.11 – sources. The 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;
    }
Copy the code

If timeBetweenEvictionRunsMillis not greater than zero, then certainly is closed, the default value is 5000; If any of the following conditions is met, the function is enabled

Judge conditions The default value The results of
getTimeBetweenEvictionRunsMillis()>0 The default value is 5000 true
isRemoveAbandoned() && getRemoveAbandonedTimeout()>0 By default, removeAbandoned is false and removeAbandonedTimeout is 60 false
getSuspectTimeout()>0 The default is 0 false
isTestWhileIdle() && getValidationQuery()! =null TestWhileIdle is false by default. Common mysql, PG, and Oracle validationQuery is not null false
getMinEvictableIdleTimeMillis()>0 The default value is 60000 true

Default to true, poolSweeper is enabled

DataSourceConfiguration

Spring – the boot – autoconfigure – 1.4.5. RELEASE – sources. The 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);
			}
			returndataSource; }}Copy the code

The default is to determine what kind of database it is based on the connection URL, and the default common database has a validationQuery

If there is a validationQuery, testOnBorrow is set to true

Note that the driver-class-name, URL,username, and password values are recognized by the generic Spring. Datasource configuration. The validationQuery validates the driver-class-name, URL,username, and password values by the URL Orrow will also be set to true, the parameters of the other connection pool, according to the specific implementation is required to specify, such as spring. The datasource. Tomcat. Initial – size, or not to take effect

validationQuery

  • DatabaseDriver spring – the boot – 1.4.5. RELEASE – sources. The 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"),

Copy the code

About poolCleaner

Tomcat JDBC – 8.5.11 – sources. The 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
    }
Copy the code

The ConnectionPool constructor initializes a call to initializePoolCleaner to determine whether poolCleaner is enabled. The default setting is true, which means poolCleaner is enabled

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); }}Copy the code

The main tasks of this timer are as follows

task Performs conditional The default value The results of
checkAbandoned RemoveAbandoned is true or suspectTimeout is greater than 0 ‘removeAbandoned’ is false and ‘suspectTimeout’ is 0 false
checkIdle pool.idel.size() > minIdle By default, minIdle is 10 —
testAllIdle TestWhileIdle to true The default is false false

Because these tasks are done down, what the default parameter configuration can do is checkIdle() Whenever removeAbandoned=true or suspectTimeout is greater than 0, checkAbandoned() will be done TestAllIdle () is executed as long as testWhileIdle is true

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); }}Copy the code

SuspectTimeout > 0, ‘removeAbandoned’ =true ‘checkAbandoned()’ if ‘removeAbandoned’ is false, only suspect judgment will be done If removeAbandoned is enabled, abandon is executed when the connection exceeds abandonTimeout. Otherwise, enter suspect and judge abandon to release the connection, i.e. Disconnect /close

Abandon the 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 and suspect check execution interval
      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
Copy the code

The sample code

    @Test
	public void testConnAbandon() throws SQLException {
		Connection connection = dataSource.getConnection();
		connection.setAutoCommit(false); //NOTE pg To set fetchSize, it must be set tofalse
		String sql = "select * from demo_table"; PreparedStatement pstmt; try { pstmt = (PreparedStatement)connection.prepareStatement(sql); pstmt.setFetchSize(10); ResultSet rs = pstmt.executeQuery(); //NOTE Sets the timeout period for the Statement to be executed. If the socket timeout is larger than this value, the Statement will be executed int col = rs.getMetaData().getColumnCount(); System.out.println("= = = = = = = = = = = = = = = = = = = = = = = = = = = =");
			while(rs.next()) {//NOTE This timeout is set by the socket timeout, oracle.jdbc.readTimeout =60000for (int i = 1; i <= col; i++) {
					System.out.print(rs.getObject(i));
				}
				System.out.println(""); TimeUnit.SECONDS.sleep(1); //NOTE that the simulated connection is abandoned} system.out.println ("= = = = = = = = = = = = = = = = = = = = = = = = = = = =");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//close resources
		}
	}
Copy the code

An error

The 2018-01-27 11:48:59. 891 WARN 1004 - [1517024909680]] O.A.T omcat. 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.FrameworkMethodThe $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.ja va: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.ParentRunnerThe $1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.accessThe $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:7 0) 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 occurredwhile 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.FrameworkMethodThe $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.ja va: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.ParentRunnerThe $1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.accessThe $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:7 0) 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 moreCopy the code

summary

  • You need to pay extra attention to the parameter Settings for different connection pools.
  • Opening abandon forces the connection to be closed, which is global

In cases where multiple statements are executed in the same connection, you can use the ResetAbandonedTimer to avoid abandoning the connection by error

  • In SpringBoot, the database is automatically identified from the spring.datasource. Url and the default validationQuery is given. If this value is not empty, testOnBorrow is automatically set to true
  • The poolSweeper is executed every time-between-eviction-runs-millis time and is checkAbandoned, checkIdle, testAllIdle, which is implemented with timer, Extra attention should be given to the fact that once a task is delayed at any point in time, subsequent tasks will be delayed as well, and there is no guarantee that it will be executed every time-between-eviction-runs-millis time.

doc

  • Tomcat JDBC Pool advanced configuration
  • Resolve the suspect and abandon operations in the Tomcat JDBC connection pool
  • ResetAbandonedTimer for Tomcat JDBC
  • Java Timer and TimerTask instance tutorials