mysql查询超时控制

mysql查询超时控制

mysql客户端与服务端通讯可以采用 长连接 与 短连接 两种方式.

• 短连接 适合客户端数量不确定 数据交互量小. 交互完毕后立刻释放tcp链接. 不占用链接
• 长连接 适合客户端数量固定 且数据交互频繁的场景 节省tcp频繁创建断开开销

一般我们的服务器与mysql之间采用第二种方式. 服务端 与 mysql服务器 维持一个连接池. hold n 个长连接复用.
如果使用了长连接而长期没有对数据库进行任何操作，那么在timeout值后，mysql server就会关闭此连接

现有一个场景 dba 担心客户端发送查询为慢查询. 所以需要建立数据库过载保护机制. 即对提交的query申请提前设置合理的执行时间.
控制慢查询带来的性能开销,从而消除影响高峰期的服务质量的隐患.

show global variables like 'wait_timeout';

wait_timeout 
connect_timeout
interactive_timeout
long_query_time
max_execution_time
net_read_timeout  
net_write_timeout
wait_timeout

选择几个比较重要的解释一下

• wait_timeout connect_timeout

在MYSQL的默认设置中，如果一个数据库连接超过8小时没有使用(闲置8小时)，服务器将断开这条连接，后续在该连接上进行的查询操作都将失败。

• net_read_timeout 默认是30S
• net_write_timeout 默认是60S

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.
See also replica_net_timeout and slave_net_timeout.

这两个参数控制由于网络原因造成的异常超时。比如 server 在从 client 端读取大量的数据，读着读着突然发现读不到了，也没有遇到结束标识符，
这种情况下，server 在等待 net_read_timeout 秒还没读到后续数据，就那么服务端就会终止本次读取操作. 注意是终止本次操作 而不是断开长连接；
或者当 server select 出了大量数据发向客户端，发着发着，突然发现发不动了，客户端不接收了，而数据还没有发送完，
这时 server 在等待 net_write_timeout 秒后就终止写入操作.。

!!!但是我们的目的是：防止慢查询查死服务器. 而不是解决网络收发问题.

• max_execution_time

查询语句执行超时控制

客户端的两种设置方式

• _, err := data.MySQLConn.Exec("SET max_execution_time=1")
• SELECT /*+ MAX_EXECUTION_TIME(10) */ * FROM prop where sleep(10)

要验证这个结果需要借助sleep函数

• When sleep returns normally (without interruption), it returns 0:
• This statement is interrupted using KILL QUERY from another session: return 1
• This statement is interrupted by timing out: return 1
• When SLEEP() is only part of a query that is interrupted, the query returns an error:

eg.1

SET max_execution_time=1
`select *,sleep(1) from prop where id=2` // 可以在select结果的最后一位观测sleep返回值.

注意这种使用模式是不会超时err提示的. 因为sleep(1) 在1ms内返回了中断结果1. 这种相当于要使用sleep(1)的返回值. 错误细节会被屏蔽.

eg.2

exec:SET max_execution_time=1    
exec:SELECT * from prop where SLEEP(1);

而这种使用方式 , 则可以将错误返回到客户端 Query execution was interrupted, maximum statement execution time exceeded

并且select语句实际sleep的时间与where条件过滤后的条数相关. 实际sql执行时间 = sleep(10) * len(result) + 实际查询时间

参考文档

• sleep函数介绍官方文档 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html
• https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
• https://dev.mysql.com/doc/refman/8.0/en/error-lost-connection.html
• qa https://stackoverflow.com/questions/71261028/how-to-set-mysql-max-execution-time-property-use-go