深入理解Mysql 超时配置项

朱雀 2023-09-26 17:42 108阅读 0赞

## 1 JDDB超时 ##

JDBC 是 Java 应用程序中用于访问数据库的一套标准 API

![format_png][]

类型4驱动是通过socket来处理字节流的。如果socket超时设置不合适，类型4驱动也可能有同样的错误（连接被阻塞）。

### 1.2 JDBC超时层次 ###

应用程序WAS与数据库间的超时的层次

![format_png 1][]

更上层的超时依赖于下层的超时，只有当较低层的超时机制正常工作，上层的超时才会正常。

#### 1.2.1 事务超时 ####

**事务超时**是在框架（Spring、EJB容器）或应用程序层面上才有效的超时。 在 Spring 中，事务超时可以 XML文件配置或在 Java 代码中用 Transactional 注解来配置。

![format_png 2][]

Spring 中数据库连接被保存在线程本地变量（ThreadLocal）中，这被称作事务同步（Transaction Synchronization）。当数据库连接被保存到 ThreadLocal 时，同时会记录事务的开始时间和超时时间。所以通过数据库连接的代理创建的 Statement 在执行时就会校验这个时间。

#### 1.2.2 Statement 超时 ####

Statement 超时是用来限制 Statement 的执行时间的，它的具体值是通过 JDBC API 来设置的。JDBC 驱动程序基于这个值进行 Statement 执行时的超时处理。Statement 超时是通过 JDBC API 中`java.sql.Statement`类的 `setQueryTimeout(int timeout)`方法配置的。不过现在更多是通过框架来进行设置。

以 iBatis 为例，可以通过`SqlMapConfig.xml` 中的 `settings` 属性`defaultStatementTimeout` 来设置全局的`statement超时`缺省值。

![format_png 3][]

也可以通过在具体的 sql 映射文件中的 `select insert update` 标签的 `timeout` 属性来覆盖。

![format_png 4][]

**JDBC的statement timeout处理过程**

每个数据库和驱动程序的Statement超时的处理也是不同的。MySQL (5.0.8) 中的 Statement 超时处理如下：

*  调用 Connection 的 createStatement() 方法创建一个 Statement 对象
 *  调用 Statement 的 executeQuery() 方法
 *  Statement 通过内部的 Connection 将查询命令传输到 MySqlServer 数据库
 *  Statement 创建一个新的超时执行线程(**timeout-execution**)来处理超时
 *  5.1以上版本改为每个连接分配一个线程
 *  向timeout-execution 线程注册当前的 Statement
 *  发生超时
 *  timeout-execution 线程创建一个相同配置的 Connection
 *  用新创建的 Connection 发送取消查询的命令

![format_png 5][]

#### 1.2.3 JDBC的socket timeout ####

Socket超时可以通过 JDBC 驱动程序配置。通过设置 Socket 超时，可以防止出现网络错误时一直等待的情况并缩短故障时间。 Socket 超时的值必须要高于 Statement 的超时时间，否则Socket超时将会先生效。

*  Socket 连接时的超时：通过 Socket 对象的 `connect(SocketAddress endpoint, int timeout)` 方法来配置
 *  Socket 读写时的超时：通过 Socket 对象的 `setSoTimeout(int timeout)` 方法来配置

**MySQL驱动的socket timeout配置方式**

*  连接超时配置 ：connectTimeout（默认值：0，单位：ms）
 *  Socket超时配置： socketTimeout（默认值：0，单位：ms）

示例：`jdbc:mysql://xxx.xx.xxx.xxx:3306/database?connectTimeout=60000&socketTimeout=60000`

也可以通过属性进行配置，而无需直接使用 DBCP 的 API 。

#### 1.2.4 操作系统Socket超时 ####

操作系统同样能够对socket timeout进行配置。 通常，应用会在调用Socket.read()时由于网络问题被阻塞住，而很少在调用Socket.write()时进入waiting状态。如果系统内核缓冲区由于某种网络错误而满了的话，Socket.write()也会进入waiting状态。这种情况下，操作系统会尝试重新发包，当达到重试的时间限制时，将产生系统错误。

## 2 Mysql服务器超时配置 ##

mysql> show variables like '%timeout%';
    +-----------------------------+----------+
    | Variable_name               | Value    |
    +-----------------------------+----------+
    | connect_timeout             | 10       |
    | delayed_insert_timeout      | 300      |
    | innodb_flush_log_at_timeout | 1        |
    | innodb_lock_wait_timeout    | 50       |
    | innodb_rollback_on_timeout  | OFF      |
    | interactive_timeout         | 28800    |
    | lock_wait_timeout           | 31536000 |
    | net_read_timeout            | 30       |
    | net_write_timeout           | 60       |
    | rpl_stop_slave_timeout      | 31536000 |
    | slave_net_timeout           | 3600     |
    | wait_timeout                | 28800    |
    +-----------------------------+----------+
    复制代码

### 2.1 connect\_timeout ###

connect\_timeout指的是连接过程中握手的超时时间，在5.0.52以后默认为10秒，之前版本默认是5秒。官方文档是这样说的：

> connect\_timeout: The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds as of MySQL 5.0.52 and 5 seconds before that

**connect timeout就是tcp连接超时 其中又分两种，一种是超过了自己设置的连接超时时间 一种是tcp层面连接sync包报文达到了重试次数报的超时， 两种超时错误提示信息是不一样的。**

### 2.2 interactive\_timeout & wait\_timeout ###

wait\_timeout和interactive\_timeout都是指不活跃的连接超时时间，连接线程启动的时候wait\_timeout会根据是交互模式还是非交互模式被设置为这两个值中的一个。 如果我们运行`mysql -uroot -p`命令登陆到mysql，`wait_timeout`就会被设置为interactive\_timeout的值

### 2.3 net\_read\_timeout & net\_write\_timeout ###

net\_read\_timeout和net\_write\_timeout这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间

> The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net\_read\_timeout is the timeout value controlling when to abort. When the server is writing to the client, net\_write\_timeout is the timeout value controlling when to abort

这两个参数控制由于网络原因造成的异常超时。比如server在从client端读取大量的数据，读着读着突然发现读不到了，也没有遇到结束标识符，这种情况下，server在等待net\_read\_timeout秒还没读到后续数据，就断开连接；或者当server select出了大量数据发向客户端，发着发着，突然发现发不动了，客户端不接收了，而数据还没有发送完，这时server在等待net\_write\_timeout秒后就断开连接。

是mysql应用层的协议，而tcp的写超时只有在丢包次数过多才会。

## 3 Mysql客户端 ##

**示例**

jdbc:mysql://192.168.1.8:3306/mytest?serverTimezone=GMT%2B8&autoReconnect=true&allowMultiQueries=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&connectTimeout=60000&socketTimeout=60000
    复制代码

**connectTimeout**

建立链接需要的时间。该参数只在建立链接阶段生效。默认是0，不超时。建议配置connectTimeout=60000，单位毫秒。

**socketTimeout**

发送请求给数据库（建立链接后），数据库处理的最大时间；默认是0，不超时。建议配置socketTimeout=60000，单位毫秒。

超过这个客户端报超时超时异常`Caused by: java.net.SocketTimeoutException: Read timed out`。

**autoReconnect**

是否自动重连。默认false。mysql服务端参数wait\_timeout，其默认值为 28800秒(8小时)，其意义为如果一个连接闲置超过这个选项所设置的秒数，MySQL会主动断开这个连接。如果无法保证应用程序在设定的秒数内至少有一次操作，添加autoReconnect=true这个参数，即能解决这个问题。

**maxReconnects**

autoReconnect设置为true时，重试连接的次数，默认3。

**initialTimeout**

autoReconnect设置为true时，两次重连之间的时间间隔，默认2，单位：秒

## 4 Mysql连接池配置 ##

### 4.1 Druid连接池配置 ###

**DruidDataSource参考配置** [github.com/alibaba/dru…][github.com_alibaba_dru]

#### 4.1.1 连接池的初始值、最大值、最小值 ####

initialSize: 5
    minIdle: 6
    maxActive: 10
    复制代码

项目启动时，会自动初始化`initialSize`个连接出来（没有流量访问数据库也会创建），这几个链接会一直存在。可以通过数据库命令`SHOW PROCESSLIST`查看初始化的链接。

#### 4.1.2 获取连接的等待时间 ####

maxWait: 6000
    复制代码

获取连接池中的连接时的最大等待时间，单位是毫秒。  
如果这个时间内未获得可用链接则报错：`nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 6000, active 10, maxActive 10, creating 0`。这个报错表明获取链接超时了，而且数据库的10个链接都被占用，也不能创建新的链接了。

#### 4.1.3 回收连接池中的链接 ####

timeBetweenEvictionRunsMillis: 2000
    minEvictableIdleTimeMillis: 10000
    复制代码

每隔`timeBetweenEvictionRunsMillis: 2000ms`对连接池的连接做一次检查， 如果有连接空闲时间超过`minEvictableIdleTimeMillis: 10000ms`就回收该链接。

#### 4.1.4 校验链接池中的链接是否有效 ####

validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    validationQueryTimeout：10    
    复制代码

校验的方式：使用待校验的连接在在数据库上执行一下校验的sql，这里使用`SELECT 1`，一定要保证改sql是该数据能执行的，不同的数据库的校验sql可能不一样。

校验的时机： 通常是在连接空闲时校验（testWhileIdle: true），  
也可以在获取连接时校验（testOnBorrow: true，这个配置会降低性能），  
也可以在归还连接到连接池时校验（testOnReturn: true，这个配置会降低性能）。

validationQueryTimeout是检测连接是否有效的超时时间，单位：秒。

如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。

### 4.1 HikariCP配置 ###

#### 4.2.1 **连接池的最大值、最小值** ####

**maximumPoolSize：** 池中最大连接数，包括闲置和使用中的连接。`默认为 10`。如果 maxPoolSize 小于1，则会被重置。当 minIdle <=0 被重置为DEFAULT\_POOL\_SIZE 则为 10;如果 minIdle > 0 则重置为 minIdle 的值。

**minimumIdle**： 控制连接池空闲连接的最小数量，当连接池空闲连接少于 minimumIdle，而且总共连接数不大于 maximumPoolSize 时，HikariCP 会尽力补充新的连接。为了性能考虑，不建议设置此值，而是让 HikariCP 把连接池当做固定大小的处理，`默认 minimumIdle 与 maximumPoolSize 一`样。当 minIdle < 0 或者 minIdle > maxPoolSize，则被重置为 maxPoolSize，该值默认为 10。

#### 4.2.2 获取连接的等待时间 ####

**connectionTimeout**： 等待来自池的连接的最大毫秒数，`默认为 30000 ms = 30 s`，允许最小时间是 250 毫秒，如果小于 250 毫秒，则被重置回 30 秒。

#### 4.2.3 超时时间 ####

**idleTimeout：** 连接允许在池中闲置的最长时间。  
如果 idleTimeout + 1 秒 > maxLifetime 且 maxLifetime > 0，则会被重置为 0（代表永远不会退出）；`只有当 minimumIdle 小于 maximumPoolSize 时，这个参数才生效`，当空闲连接数超过 minimumIdle，而且空闲时间超过 idleTimeout，则会被移除。

这是hikaricp用来判断是否应该从连接池移除空闲连接的一个重要的配置。负责剔除的也还是HouseKeeper这个定时任务，值为0时，HouseKeeper不会移除空闲连接，直到到达maxLifetime后，才会移除，默认值也就是0。

**maxLifetime**：

池中连接最长生命周期`。默认为 1800000， 30 分钟`。 Mysql 为了防止空闲连接浪费，占用资源，在超过wait\_timeout 时间后，会主动关闭该连接，清理资源。 但是hikaricp如何知道池子里维护的一把连接，有没有被mysql回收呢？所以就有了`maxLifetime`这个配置，官方也强烈建议必须按需设置此值！自然这个值也应该小于mysql的`wait_timeout`。 那hikaricp在`空闲连接`超过`maxLifetime`，就会从连接池中剔除，防止业务进程取到了已关闭的连接，导致业务受损。

**keepaliveTime & connectionTestQuery**

keepaliveTime类比tcp的keepAlive机制。为了防止获取到被mysql关闭的无效连接，导致业务出错的一种兜底扫描方案。

具体过程就是每隔keepaliveTime时间间隔，去和数据库发送心跳，来探测连接是否有效。如果发现是无效的，就会及时从连接池中剔除，来保证业务进程获取到的都是有效连接。

如果配置了`connectionTestQuery` ，如`select 1`, 心跳检查过程就会调用connectionTestQuery。

所以如果你配置了`connectionTestQuery` 但是没有配置`keepaliveTime` ，是没有用的，因为默认是关闭的。

而`connectionTestQuery` 配置项，官方建议如果驱动支持JDBC4，不要设置此属性！

因为相比于通过select查询方式探活，mysql 自带的ping命令 性能更高（直接在sql server返回结果，就不会做语法解析，执行优化，再通过存储引擎操作）。 而基本上java mysql驱动包5以上的版本都支持JDBC4。

[format_png]: https://img-blog.csdnimg.cn/img_convert/40d9bdab9bae4ce61ff0cf59debd90e0.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/c15f10cae1440a20b01ab96b669a80dd.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/af75f4d41edae9385090441c9b787ac6.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/adb79567c3f7f592f36a7a40f400354a.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/f082500a39f0e09df66367ee58f7a257.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/9229b18221c54d5db1082ffe66175c5f.webp?x-oss-process=image/format,png
[github.com_alibaba_dru]: https://link.juejin.cn?target=https%3A%2F%2Fgithub.com%2Falibaba%2Fdruid%2Fwiki%2FDruidDataSource%25E9%2585%258D%25E7%25BD%25AE