序

本文主要研究一下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

@Overridepublic 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 poolsinit(prop);}public void initializePoolCleaner(PoolConfiguration properties) {//if the evictor thread is supposed to run, start it nowif (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.");}}@Overridepublic 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 itif (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.Driverurl: jdbc:postgresql://localhost:5432/postgres?connectTimeout=60&socketTimeout=60username: postgrespassword: postgresjmx-enabled: truetomcat:initial-size: 1max-active: 5## when pool sweeper is enabled, extra idle connection will be closedmax-idle: 5## when idle connection > min-idle, poolSweeper will start to closemin-idle: 1# PoolSweeper run interval abandon及suspect檢測的執行間隔time-between-eviction-runs-millis: 30000remove-abandoned: true# how long a connection should return,if not return regard as leak connectionremove-abandoned-timeout: 10# how long a connection should return, or regard as probably leak connectionsuspect-timeout: 10log-abandoned: trueabandon-when-percentage-full: 0 ## (used/max-active*100f)>=perc -->shouldAbandon, if set 0 always abandon# idle connection idle time before closemin-evictable-idle-time-millis: 60000validation-query: select 1validation-interval: 30000

實例代碼

@Testpublic void testConnAbandon() throws SQLException {Connection connection = dataSource.getConnection();connection.setAutoCommit(false); //NOTE pg 為了設置fetchSize,必須設置為falseString 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=60000for (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.Exceptionat 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 closedat 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

• tomcat jdbc pool高級配置
• tomcat jdbc連接池的suspect、abandon操作解析
• 淺析tomcat jdbc的ResetAbandonedTimer
• Java Timer和TimerTask實例教程

總結

以上是生活随笔為你收集整理的聊聊tomcat jdbc pool的默认参数及poolSweeper的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。