珠海网站建设王道下拉強,做视频开头动画网站,江苏省招标投标信息网,教做网站本文环境 阿里云RDS MySQL 8.0.34 当客户端向MySQL数据库发送一条SQL之后,由于SQL很慢很慢,它会在什么时候结束呢? 查看
max_execution_time 变量值
mysql show variables like max_execution_time;
---------------------------
| Variable_name | Value |
------… 本文环境 阿里云RDS MySQL 8.0.34 当客户端向MySQL数据库发送一条SQL之后,由于SQL很慢很慢,它会在什么时候结束呢? 查看
max_execution_time 变量值
mysql show variables like max_execution_time;
---------------------------
| Variable_name | Value |
---------------------------
| max_execution_time | 0 |
---------------------------查看官方文档 如果 max_execution_time 0 则SELECT语句的执行不会超时, 直到查询结果返回. 进行模拟实验, 通过 root 用户登录到数据库.(一个用于查看监控数据,一个用于执行慢查询)
mysql select * from information_schema.processlist where userroot order by id asc;
----------------------------------------------------------------------------------------------------------------------------------------------------
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 125901 | root | 183.94.140.242:17228 | NULL | Query | 0 | executing | select * from information_schema.processlist where userroot order by id asc |
| 125905 | root | 183.94.140.242:16471 | db1 | Sleep | 38 | | NULL |
----------------------------------------------------------------------------------------------------------------------------------------------------接下来在其中一个客户端执行以下语句, 模拟耗时查询 mysql select * from operation_record where record_id460156845005578240 and sleep(120);
而在另一个客户端执行如下监控语句(监控数据库连接的情况), 可以看到 ID 125905 的客户端(即上面那个客户端)在执行耗时查询.
mysql select * from information_schema.processlist where userroot order by id asc;
-------------------------------------------------------------------------------------------------------------------------------------------------------
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 125901 | root | 183.94.140.242:17228 | NULL | Query | 0 | executing | select * from information_schema.processlist where userroot order by id asc |
| 125905 | root | 183.94.140.242:16471 | db1 | Query | 49 | User sleep | select * from operation_record where record_id460156845005578240 and sleep(120) |
-------------------------------------------------------------------------------------------------------------------------------------------------------经过120秒之后, 查询会正常返回.
我们把以上流程的慢查询语句在 MySQLWorkbench 工具里执行. 同样也可以监控到该慢查询
mysql select * from information_schema.processlist where userroot order by id asc;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 125901 | root | 183.94.140.242:17228 | NULL | Query | 0 | executing | select * from information_schema.processlist where userroot order by id asc |
| 125905 | root | 183.94.140.242:16471 | db1 | Sleep | 687 | | NULL |
| 125934 | root | 183.94.140.242:16963 | NULL | Sleep | 232 | | NULL |
| 125940 | root | 183.94.140.242:17078 | db1 | Query | 12 | User sleep | select * from operation_record where record_id460156845005578240 and sleep(120) LIMIT 0, 50 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------可是在经过30秒之后, 客户端收到了错误响应 而且从监控中也发现少了一个客户端连接(ID 125940 不见了)
mysql select * from information_schema.processlist where userroot order by id asc;
----------------------------------------------------------------------------------------------------------------------------------------------------
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 125901 | root | 183.94.140.242:17228 | NULL | Query | 0 | executing | select * from information_schema.processlist where userroot order by id asc |
| 125905 | root | 183.94.140.242:16471 | db1 | Sleep | 727 | | NULL |
| 125934 | root | 183.94.140.242:16963 | NULL | Sleep | 272 | | NULL |
----------------------------------------------------------------------------------------------------------------------------------------------------是因为 MySQLWorkbench 这个工具设置了默认读超时的最大值 30 秒. 如果把这个默认超时时间设置大于120秒,那么慢查询就可以执行到120秒返回结果,而不会在30秒的时候出现异常了. 需要重新打开一个新的查询窗口或者重新打开MySQLWorkbench工具 有时候,需要控制查询的最大执行时长,可以通过 MAX_EXECUTION_TIME(N) 控制. # 设置最大执行时长 10 秒
mysql select /* MAX_EXECUTION_TIME(10000) */ * from operation_record where record_id460156845005578240 and sleep(120);# 10 秒之后,会返回如下错误
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded如果客户端不主动断开连接, 如果客户端不主动设置最大执行时长, 而数据库端设置最大执行时长.
把 RDS MySQL max_execution_time 值修改成 60 秒 客户端重连数据库,再次查看 max_execution_time
mysql show variables like max_execution_time;
---------------------------
| Variable_name | Value |
---------------------------
| max_execution_time | 60000 |
---------------------------已生效
再次执行慢查询 mysql select * from operation_record where record_id460156845005578240 and sleep(120); # 60 秒之后,会返回如下错误
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded【总结】 当一个SQL语句在慢查询的执行过程中 1.如果客户端主动断开连接, 则数据库连接会消失.毕竟断开了连接 2.如果客户端设置了SELECT语句最大执行时长, 则数据库连接依然在, 如果查询耗时超过设置的最大执行时长,语句将被中断,返回给客户端如下错误 ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded 3.如果数据库端设置最大执行时长,则数据库连接依然在, 如果查询耗时超过设置的最大执行时长,语句将被中断,返回给客户端如下错误 ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded 另外 如果客户端开启一个事务之后, 长时间没有再与数据库有过沟通, 比如很长时间没有再向数据库发送DML语句, 那么经过
wait_timeout/interactive_timeout 时间之后, 数据库会主动断开该连接,
数据库连接消失,事务会被回滚. 好在客户端的程序都有连接池, 连接池进行活性检测. 个人站点 https://www.infuq.com
