Technology Sharing | Explore the realization of the Druid connection pool exploration method

Author: Liu Kaiyang

The Beijing DBA of Eikesheng's delivery service team has a strong learning interest in database and peripheral technology, likes to read books, and pursues technology.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

Recently, the problem of Druid has been studied. Select x cannot be output normally during detection of live detection. After solving the problem, I ran to share with you the implementation of Druid detection mechanism.

1. What is Druid? How does Druid detect connections?

Druid is a JDBC component open sourced by Alibaba. It is a database connection pool. It has strong adaptability and functions to MySQL, including monitoring database access performance, database password encryption, SQL execution log, and implementation of extended monitoring, etc. , Application to MySQL is still very fragrant.

Through some simple analysis of the official source code (see reference for details), it is learned that when using Druid to detect the connection, it involves the adjustment of the following two parameters:

| Parameters | Description |
| :--------- | :--: |
|druid.validationQuery = select 1 | SQL used to check whether the connection is valid, the requirement is a query statement, usually select'x'. If the validationQuery is null, testOnBorrow, testOnReturn, testWhileIdle will not work. |
| druid.mysql.usePingMethod = false | Disable the mysql com_ping detection mechanism, enable validationQuery = select x, and enable the validationQuery detection mechanism |

For the description of other parameters, refer to the configuration attribute list: https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7 %E5%88%97%E8%A1%A8

The information obtained in the source code is that Druid initializes and loads initValidConnectionChecker(); and validationQueryCheck(); in ValidConnectionChecker by default com_ping is enabled, so com_ping is selected as the default detection activity. Let's observe the output of com_ping and validationquery respectively.

Let's read on to see how the functions of these two parameters are implemented.

Two, verification

In the test, let's finalize the role of these two probing mechanism parameters. The version used here is Druid 1.2.5

1. The com_ping method needs to be verified by packet capture

Capture the packet through tcpdump to get the transmission of network packets in the Druid connection, and then use wireshark to analyze and view the Request packet sent by Druid to MySQL.

Get Ping (14) in Request Command Ping of MySQL Protocol.

2. The validation query method is verified by using MySQL general log

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);
    }
    //注意这里是从系统变量中获取的 System.getProperties()。
    configFromProperties(System.getProperties());
}
   
@Override
public void configFromProperties(Properties properties) {
   //从系统变量中获取的，所以应该是在项目的启动脚本中添加 usePingMethod=false
   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 (conn.isClosed()) {
      return false;
  }
   
  if (usePingMethod) {
      if (conn instanceof DruidPooledConnection) {
          conn = ((DruidPooledConnection) conn).getConnection();
      }
   
      if (conn instanceof ConnectionProxy) {
          conn = ((ConnectionProxy) conn).getRawObject();
      }
   
       // 当前的 conn 是否是 com.mysql.jdbc.MySQLConnection(or com.mysql.cj.jdbc.ConnectionImpl)
      if (clazz.isAssignableFrom(conn.getClass())) {
          if (validationQueryTimeout <= 0) {
              validationQueryTimeout = DEFAULT_VALIDATION_QUERY_TIMEOUT;
          }
   
          try {
              // 使用反射调用MySQLConnection.pingInternal 方法，检查连接有效性，并且会刷新连接的空闲时间，如果失败则会抛出异常，上层捕获
              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;
  // 当usePingMethod=false 或者 conn 不是 com.mysql.jdbc.MySQLConnection （or com.mysql.cj.jdbc.ConnectionImpl）会执行一下方法
  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);
      }
      // 执行 select x 的query ，并且会刷新连接的空闲时间
      //  如果失败则会抛出异常，上层捕获
      rs = stmt.executeQuery(query);
      return true;
  } finally {
      JdbcUtils.close(rs);
      JdbcUtils.close(stmt);
  }
}

druid.validationQuery = SELECT 1 To enable the validation query cannot be used directly, it needs to be configured to disable com_ping (druid.mysql.usePingMethod = false). This parameter can be added directly to the configuration file, but you need to pay attention to it. If the configuration turns off com_ping and the validation query cannot be used for probing, it may be a problem in the program itself.

There may be parameter values in the program code. Only pulling the parameter information of configFromPropety causes the (druid.mysql.usePingMethod = false) parameter to become invalid. The following is a schematic diagram of the connection after my program is modified:

// 原程序
public DruidDriverTest() {
   logger = Logger.getLogger("druid_driver_test");
   this.dataSource = new DruidDataSource();
 
   // Druid 配置文件地址.
   this.configPath = "./config.properties";
···
 
#############################################
// 修改后
public DruidDriverTest() {
    logger = Logger.getLogger("druid_driver_test");
 
    // Druid 配置文件地址.
    this.configPath = "config.properties";
 
    try (BufferedReader bufferedReader = new BufferedReader(new FileReader(configPath))) {
            // 将配置文件读入到 system.config 中
            System.getProperties().load(bufferedReader);
    } catch (IOException e) {
            e.printStackTrace();
            return;
    }
···

In the original program: Druid pulls configuration parameter information from the config configuration file to DruidDataSource by default, and the usePingMethod parameter needs to be loaded and read into DruidDataSource using the MySqlValidConnectionChecker plug-in, but config is not loaded into System.getProperties(), so Druid cannot recognize config The usePingMethod parameter in the configuration file. Druid loads the configuration information in DruidDataSource to perform a series of actions.

After modification: Establish a connection to load the config configuration file into the system variable, and then use the MySqlValidConnectionChecker plug-in to load it into the DruidDataSource.

[root@yang-02 druid_demo-master]# mvn exec:java -Dexec.mainClass="test.App"
[INFO] Scanning for projects...                                                                
[INFO] ------------------------------------------------------------------------
[INFO] Building druid-demo 1.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] --- exec-maven-plugin:3.0.0:java (default-cli) @ druid-demo ---
[2021-04-28 17:23:06] [SEVERE] minEvictableIdleTimeMillis should be greater than 30000
[2021-04-28 17:23:06] [SEVERE] keepAliveBetweenTimeMillis should be greater than 30000
[2021-04-28 17:23:06] [INFO] start test
[2021-04-28 17:23:06] [INFO] ------------------ status --------------------
[2021-04-28 17:23:06] [INFO] initial size: 3
[2021-04-28 17:23:06] [INFO] min idle: 2
[2021-04-28 17:23:06] [INFO] max active: 20
[2021-04-28 17:23:06] [INFO] current active: 0
[2021-04-28 17:23:06] [INFO] max wait: 6000
[2021-04-28 17:23:06] [INFO] time between eviction runs millis: 2000
[2021-04-28 17:23:06] [INFO] validation query: SELECT 1
[2021-04-28 17:23:06] [INFO] keepAlive: true
[2021-04-28 17:23:06] [INFO] testWhileIdle: false
[2021-04-28 17:23:06] [INFO] testOnBorrow: false
[2021-04-28 17:23:06] [INFO] testOnReturn: false
[2021-04-28 17:23:06] [INFO] keepAliveBetweenTimeMillis: 4000
[2021-04-28 17:23:06] [INFO] MinEvictableIdleTimeMillis: 2000
[2021-04-28 17:23:06] [INFO] MaxEvictableIdleTimeMillis: 25200000
[2021-04-28 17:23:06] [INFO] RemoveAbandoned: false
[2021-04-28 17:23:06] [INFO] RemoveAbandonedTimeoutMillis: 300000
[2021-04-28 17:23:06] [INFO] RemoveAbandonedTimeout: 300
[2021-04-28 17:23:06] [INFO] LogAbandoned: false
 
  
// 通过开启MySQL general log 观测Druid下发查询的命令输出
// mysql general log output
2021-04-28T17:23:01.435944+08:00     7048 Connect   root@127.0.0.1 on druid_demo using TCP/IP
2021-04-28T17:23:01.441663+08:00     7048 Query /* mysql-connector-java-5.1.40 ( Revision: 402933ef52cad9aa82624e80acbea46e3a701ce6 )
*/SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client,
@@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results,
@@character_set_server AS character_set_server,@@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout,
@@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet,
@@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size,
@@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone,
@@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
2021-04-28T17:23:01.467362+08:00     7048 Query SHOW WARNINGS
2021-04-28T17:23:01.469893+08:00     7048 Query SET NAMES utf8mb4
2021-04-28T17:23:01.470325+08:00     7048 Query SET character_set_results = NULL
2021-04-28T17:23:01.470681+08:00     7048 Query SET autocommit=1
2021-04-28T17:23:01.580189+08:00     7048 Query SELECT 1
2021-04-28T17:23:01.584444+08:00     7048 Query select @@session.tx_read_only
2021-04-28T17:23:01.584964+08:00     7048 Query SELECT @@session.tx_isolation
······
2021-04-28T17:23:10.621839+08:00     7052 Quit
2021-04-28T17:23:12.623470+08:00     7051 Query SELECT 1
2021-04-28T17:23:12.624380+08:00     7053 Query SELECT 1
2021-04-28T17:23:14.625555+08:00     7053 Query SELECT 1
2021-04-28T17:23:14.626719+08:00     7051 Query SELECT 1
2021-04-28T17:23:16.627945+08:00     7051 Query SELECT 1
2021-04-28T17:23:16.628719+08:00     7053 Query SELECT 1
2021-04-28T17:23:18.629940+08:00     7053 Query SELECT 1
2021-04-28T17:23:18.630674+08:00     7051 Query SELECT 1

If the teachers who read the article have research on Druid's probing or other parameters, please leave a message in the background to contact the editor, the level is limited, please enlighten me.

reference

https://github.com/alibaba/druid/blob/1.2.5/src/main/java/com/alibaba/druid/pool/DruidDataSource.java

https://github.com/alibaba/druid/blob/1.2.5/src/main/java/com/alibaba/druid/pool/vendor/MySqlValidConnectionChecker.java

Thanks:

Aikesheng CTO-Mr. Huang Yan and Aikesheng R&D-Mr. Sun Jian, thank the two teachers for their help in the Druid test.