Druid空闲连接检测 KeepAlive与MySql discard long time none received connection报警

发表于 | 分类于 | 评论数： | 阅读次数：

之前我的两篇关于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
#空闲保活时间，超过时间需要保活，默认2*60s
keep-alive-between-time-millis: 120000
#最小空闲时间，即多余min-idle数目的连接空闲存活时间
min-evictable-idle-time-millis: 300000
#最大空闲时间，min-idle连接空闲存活时间
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;
       }
   }
 //是否需要keepAlive
   if (keepAlive && idleMillis >= keepAliveBetweenTimeMillis) {
       keepAliveConnections[keepAliveCount++] = connection;
   }

......
 //进行keepAlive	
   if (keepAliveCount > 0) {
       // keep order
       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 // unexcepted branch
               ) {
         	//空闲连接需要检测
           boolean validate = testConnectionInternal(poolableConnection.holder, poolableConnection.conn);
           ......
       }
   }     


//此处检测连接
boolean valid = validConnectionChecker.isValidConnection(conn, validationQuery, validationQueryTimeout);
   ......
   if (valid && isMySql) { // unexcepted branch
       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) {
          ......
				//ping检测后返回成功
          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;
          }
      }
		//Select 检测
      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");
}