之前我的两篇关于Druid连接池的文章讨论了一些关于连接保活和超时设置的问题,后来我又重新梳理了一Druid关于空闲连接检测以及KeepAlive
执行的过程,本文其实已经写了很久了,当时是基于1.2.4
版本,一直忘了发布上来。目前最新版是1.2.8
版本,建议升级到最新版本,因为1.2.4
版本存在一些连接检测异常被错误丢弃和KeepAlive
的一些Bug,不过本文探讨的大致流程没有变化,因此还是基于1.2.4
版本说明,但是请关注新版本的Releases Note。
空闲连接检测
这是一份简化的Druid配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
...... min-idle: 1 max-active: 20 time-between-eviction-runs-millis: 60000 keep-alive-between-time-millis: 120000 min-evictable-idle-time-millis: 300000 max-evictable-idle-time-millis: 3600000 validation-query: SELECT 1 validation-query-timeout: 10 keep-alive: true test-while-idle: true test-on-borrow: false test-on-return: false ......
在当前Druid 1.2.4版本,DestroyTask
线程会按照time-between-eviction-runs-millis
时间间隔检测空闲连接,当idleMillis
(连接空闲时间=当前系统时间-lastActiveTimeMillis
)> min-evictable-idle-time-millis
,会驱逐多余超过min-idle
数量的连接,直到idleMillis
> max-evictable-idle-time-millis
,min-idle
的连接也会被关闭重新建立。如果开启keep-alive
,当idleMillis
> keep-alive-between-time-millis
,会对连接进行心跳保活,首先会执行连接检测,不同数据源的检测方式不同,MySQL连接检测有两种方式ping
和validation-query
,默认使用ping
,只检测连接有效,不会刷新相关时间参数,检测之后刷新lastKeepTimeMillis
。检测的超时时间都是取自validation-query-timeout
,默认是1。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
long idleMillis = currentTimeMillis - connection.lastActiveTimeMillis; if (idleMillis < minEvictableIdleTimeMillis && idleMillis < keepAliveBetweenTimeMillis ) { break ; } if (idleMillis >= minEvictableIdleTimeMillis) { if (checkTime && i < checkCount) { evictConnections[evictCount++] = connection; continue ; } else if (idleMillis > maxEvictableIdleTimeMillis) { evictConnections[evictCount++] = connection; continue ; } } if (keepAlive && idleMillis >= keepAliveBetweenTimeMillis) { keepAliveConnections[keepAliveCount++] = connection; } ...... if (keepAliveCount > 0 ) { for (int i = keepAliveCount - 1 ; i >= 0 ; --i) { ...... boolean validate = false ; try { this .validateConnection(connection); validate = true ; } catch (Throwable error) { ...... } boolean discard = !validate; if (validate) { holer.lastKeepTimeMillis = System.currentTimeMillis(); ...... } ...... this .getDataSourceStat().addKeepAliveCheckCount(keepAliveCount); Arrays.fill(keepAliveConnections, null ); }
当开启了test-while-idle
,获取连接后会检测空闲连接,空闲的判断逻辑大致为取lastActiveTimeMillis
和lastKeepTimeMillis
的最大值和当前系统时间对比,如果超过了time-between-eviction-runs-millis
,就认为连接空闲,需要检测,检测的第一步和上面类似,默认使用ping
,成功之后再次判断连接的空闲时间,此处是通过反射获取MySQL连接的lastPacketReceivedTimeMs
,ping
不会刷新这个时间,如果当前时间-lastPacketReceivedTimeMs
>time-between-eviction-runs-millis
,则会认为连接已经超过空闲时间,于是抛弃这个连接,打印WARN日志discard long time none received connection
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
if (testWhileIdle) { ...... if (lastKeepTimeMillis > lastActiveTimeMillis) { lastActiveTimeMillis = lastKeepTimeMillis; } long idleMillis = currentTimeMillis - lastActiveTimeMillis; ...... if (idleMillis >= timeBetweenEvictionRunsMillis || idleMillis < 0 ) { boolean validate = testConnectionInternal(poolableConnection.holder, poolableConnection.conn); ...... } } boolean valid = validConnectionChecker.isValidConnection(conn, validationQuery, validationQueryTimeout); ...... if (valid && isMySql) { long lastPacketReceivedTimeMs = MySqlUtils.getLastPacketReceivedTimeMs(conn); if (lastPacketReceivedTimeMs > 0 ) { long mysqlIdleMillis = currentTimeMillis - lastPacketReceivedTimeMs; if (lastPacketReceivedTimeMs > 0 && mysqlIdleMillis >= timeBetweenEvictionRunsMillis) { discardConnection(holder); String errorMsg = "discard long time none received connection. " + ", jdbcUrl : " + jdbcUrl + ", version : " + VERSION.getVersionNumber() + ", lastPacketReceivedIdleMillis : " + mysqlIdleMillis; LOG.warn(errorMsg); return false ; } }
根据当前的配置,每60s执行一次空闲检测,但是只有空闲超过120s才会执行keepalive
,所以超过空闲超过60s的连接不做处理,如果此时获取该连接,空闲连接检测生效,就会丢掉该连接.如果将最小keepalive
时间改为和空闲检测一致,每次空闲检测都会刷新lastKeepTimeMillis
,这样再获取连接不会进行空闲检测.但是这种是理想情况,如果0s进行了keepalive,间隔20s之后,执行数据库操作,当60s时,空闲为40s,无需keepalive
,当100s时,此时空闲时间为80s,如果此时获取连接,则又会进行连接空闲检测抛弃连接.(1.2.6
版本已经要求keepAliveBetweenTimeMillis
必须要大于timeBetweenEvictionRunsMillis
)
Druid检测MySQL连接的方式是根据一个系统属性druid.mysql.usePingMethod
,没有设置的情况如果有ping method
下会使用MySQL ping
进行连接检测
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
public MySqlValidConnectionChecker () { try { clazz = Utils.loadClass("com.mysql.jdbc.MySQLConnection" ); if (clazz == null ) { clazz = Utils.loadClass("com.mysql.cj.jdbc.ConnectionImpl" ); } if (clazz != null ) { ping = clazz.getMethod("pingInternal" , boolean .class, int .class); } if (ping != null ) { usePingMethod = true ; } } catch (Exception e) { LOG.warn("Cannot resolve com.mysql.jdbc.Connection.ping method. Will use 'SELECT 1' instead." , e); } configFromProperties(System.getProperties()); } @Override public void configFromProperties (Properties properties) { String property = properties.getProperty("druid.mysql.usePingMethod" ); if ("true" .equals(property)) { setUsePingMethod(true ); } else if ("false" .equals(property)) { setUsePingMethod(false ); } } public boolean isValidConnection (Connection conn, String validateQuery, int validationQueryTimeout) throws Exception { if (usePingMethod) { ...... if (clazz.isAssignableFrom(conn.getClass())) { if (validationQueryTimeout <= 0 ) { validationQueryTimeout = DEFAULT_VALIDATION_QUERY_TIMEOUT; } try { ping.invoke(conn, true , validationQueryTimeout * 1000 ); } catch (InvocationTargetException e) { Throwable cause = e.getCause(); if (cause instanceof SQLException) { throw (SQLException) cause; } throw e; } return true ; } } String query = validateQuery; if (validateQuery == null || validateQuery.isEmpty()) { query = DEFAULT_VALIDATION_QUERY; } Statement stmt = null ; ResultSet rs = null ; try { stmt = conn.createStatement(); if (validationQueryTimeout > 0 ) { stmt.setQueryTimeout(validationQueryTimeout); } rs = stmt.executeQuery(query); return true ; } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); } }
MySql空闲连接关闭Warn日志解决
如果不想出现空闲连接被强制关闭并且出现这个Warn日志,也很好解决
只需要将druid.mysql.usePingMethod
设置为false
,这样每次连接检测都会执行validation-query
语句,因此不会再丢弃空闲连接,由于该配置为系统属性,可以通过启动参数-Ddruid.mysql.usePingMethod=false
或者代码 配置
1 2 3 4
@PostConstruct public void setProperties () { System.setProperty("druid.mysql.usePingMethod" ,"false" ); }
共有 0 条评论