跳到主要内容
MySQL 常见超时参数解析与实战调优 | 极客日志
SQL
MySQL 常见超时参数解析与实战调优 MySQL 连接超时涉及多个关键参数,包括握手阶段的 connect_timeout、空闲时的 wait_timeout 以及锁等待相关的 innodb_lock_wait_timeout 等。详细解析了这些参数的含义、默认值及生效场景,并通过 telnet、事务锁竞争等实战案例演示了超时触发后的表现。理解这些机制有助于快速定位连接断开、死锁或网络异常问题,优化数据库稳定性。
SecGuard 发布于 2025/2/4 更新于 2026/4/25 2 浏览在实际运维中,MySQL 连接超时往往是排查问题的第一道坎。很多时候我们连接 MySQL 会在 timeout 这里跌倒,明确各个超时参数的含义和生效场景至关重要。
我们可以通过以下命令查看当前生效的超时配置:
mysql> show global 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
slave_net_timeout
wait_timeout
|
|
|
31536000
|
|
|
3600
|
|
|
28800
|
+
Session 级别的 timeout 也是由这些全局参数控制,具体取值取决于客户端类型。下面从这些变量中挑几个常用的逐个分析。
连接握手阶段:connect_timeout connect_timeout 指的是连接过程中 TCP 三次握手的超时时间。在 5.0.52 版本以后默认为 10 秒,之前版本默认是 5 秒。官方文档说明这是服务端等待客户端连接包的时间,超时后会返回 Bad handshake。
MySQL 的基本原理是有个监听线程循环接收请求,当有请求来时,创建线程(或者从线程池中取)来处理这个请求。由于 MySQL 连接采用 TCP 协议,在此之前势必需要进行 TCP 三次握手。TCP 三次握手成功之后,客户端会进入阻塞,等待服务端的消息。服务端这个时候会创建一个线程(或者从线程池中取一个线程)来处理请求,主要验证部分包括 host 和用户名密码验证。
由此可见,整个连接握手可能会有各种可能出错。所以这个 connect_timeout 值就是指这个超时时间了。可以简单测试下,运行下面的 telnet 命令会发现客户端会在 10 秒后超时返回:
256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL
空闲连接阶段:wait_timeout & interactive_timeout 这两个参数都是指不活跃的连接超时时间。连接线程启动的时候,wait_timeout 会根据是交互模式还是非交互模式被设置为这两个值中的一个。如果我们运行 mysql -uroot -p 命令登陆到 MySQL,wait_timeout 就会被设置为 interactive_timeout 的值。
如果在 wait_timeout 时间内没有进行任何操作,那么再次操作的时候就会提示超时,这时 MySQL Client 会重新连接。
mysql> set global interactive_timeout= 3 ;
重新进入 MySQL,这时候可以看到 wait_timeout 已经被设置为 3 秒:
mysql> show variables like '%timeout%' ;
+
| Variable_name | Value |
+
| ... | ... |
| interactive_timeout | 3 |
| ... | ... |
| wait_timeout | 3 |
+
这样,我们 3 秒后再执行其他命令,会提示如下错误:
mysql> show variables like '%timeout%' ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 50 Current database: * * * NONE * * *
InnoDB 锁等待:innodb_lock_wait_timeout & innodb_rollback_on_timeout 这两个值是针对 InnoDB 引擎的,是 InnoDB 中行锁的等待超时时间,默认为 50 秒。如果超时,则当前语句会回滚。如果设置了 innodb_rollback_on_timeout,则会回滚整个事务,否则,只回滚事务等待行锁的这个语句。
同样来测试下(先创建一个 InnoDB 引擎的表 test,只有一列,列名为 a):
mysql> CREATE TABLE `test` (
`a` int primary key
) engine= innodb;
mysql> insert into test values (1 ),(2 ),(3 );
当前 innodb_rollback_on_timeout=OFF,设置 innodb_lock_wait_timeout=1,我们开启两个事务:
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where a= 2 for update ;
+
| a |
+
| 2 |
+
1 row in set (0.01 sec)
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test where a= 1 ;
Query OK, 1 row affected (0.00 sec)
mysql> delete from test where a= 2 ;
## 请求行锁超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
此时查询表内容,发现只剩下 2 和 3。如果这里我们显示的 rollback,则会回滚整个事务,保持 1、2、3 不变。
那么如果 innodb_rollback_on_timeout=ON,同样事务 2 会超时,但是这个时候如果我们 begin 开启新的事务,那么会回滚请求锁超时的整个事务,而不是像前面那样只回滚了超时的那条语句。
元数据锁:lock_wait_timeout lock_wait_timeout 是元数据锁等待超时,任意锁元数据的语句都会用到这个超时参数,默认为一年。元数据锁可以保证事务可串行化,不管是 MyISAM 还是 InnoDB 引擎的表,只要是开始一个事务,就会获取操作表的元数据锁,这时候如果另一个事务要对表的元数据进行修改,则会阻塞直到超时。
测试例子:我们用一个 MyISAM 引擎的表 myisam_test 来测试。其中有一条记录 (1,1),现在我们先开启一个事务,然后执行一个 select 语句。另外打开一个 session,然后执行表的元数据操作,如删除表,会发现操作阻塞直到 lock_wait_timeout 秒后提示超时。
第一个 Session:获取 metadata lock
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myisam_test;
+
| i | j |
+
| 2 | 1 |
+
1 row in set (0.00 sec)
mysql> drop table myisam_test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
DROP TABLE t;
ALTER TABLE t ...;
LOCK TABLE t ... WRITE;
当然,对于 MyISAM 表的加锁以及并发插入等,相关原理可参考 MyISAM 表锁机制详解。
网络传输阶段:net_read_timeout & net_write_timeout 这两个参数在网络条件不好的情况下起作用。比如我在客户端用 load data infile 的方式导入很大的一个文件到中,然后中途用 iptables 禁用掉 MySQL 的 3306 端口,这个时候服务器端该连接状态是 reading from net,在等待 net_read_timeout 后关闭该连接。
同理,在程序里面查询一个很大的表时,在查询过程中同样禁用掉端口,制造网络不通的情况,这样该连接状态是 writing to net,然后在 net_write_timeout 后关闭该连接。
测试:我创建一个 120M 的数据文件 data.txt。然后登陆到 MySQL:
mysql -uroot -h 127.0.0.1 -P 3306 --local-infile=1
导入过程设置 iptables 禁用 3306 端口:
iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -A OUTPUT -p tcp --sport 3306 -j DROP
可以看到连接状态为 reading from net,然后经过 net_read_timeout 秒后关闭。
总结 经过几个实验可以发现,connect_timeout 在握手认证阶段(authenticate)起作用,interactive_timeout 和 wait_timeout 在连接空闲阶段(sleep)起作用,而 net_read_timeout 和 net_write_timeout 则是在连接繁忙阶段(query)或者网络出现问题时起作用。理解这些差异,能帮助我们在遇到连接断开或超时错误时快速定位根因。
相关免费在线工具 SQL 美化和格式化 在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
SQL转CSV/JSON/XML 解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 工具包 CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
Base64 字符串编码/解码 将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
Base64 文件转换器 将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
Markdown转HTML 将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML转Markdown 互为补充。 在线工具,Markdown转HTML在线工具,online