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

栏目: Java · 发布时间: 8年前

内容简介：聊聊tomcat jdbc pool的默认参数及poolSweeper

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

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

PHP与MySQL权威指南

吴津津、田睿、李云、刘昊 / 机械工业出版社华章公司 / 2011-10 / 118.00元

PHPChina官方出品，Discuz！创始人戴志康、UCHome创始人李国德、ThinkPHP创始人刘晨、PHPCMS项目负责人王参加等联袂推荐。本书是目前为止最全面的关于PHP与MySQL开发技术的书籍之一，系统而全面地讲解了PHP与MySQL技术的方方面面，适合初中级的PHP程序员系统地学习；本书也是目前为止首本系统而深入地讲解UCenter、Discuz!、UCHome、ShopN......一起来看看《PHP与MySQL权威指南》这本书的介绍吧!

码农工具