本文主要研究一下tomcat jdbc pool的默认参数及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
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,否则不生效
/**
* 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"),
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
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()
/**
* 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连接
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