mysql 的 timeout 有多种,
show variables like '%timeout%'
结果:
重点说几种
一.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.
Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at 'XXX', system error: errno."
mysql server 对连接的等待时间。默认值是10秒,超过这个时间,mysql 将会返回 Bad handshake。
二.wait_timeout
介绍先看官网的介绍
"The number of seconds the server waits for activity on a noninteractive connection before closing it.
On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout."
对于nonactive 的连接,mysql server 要等待多久。超过这个时间,将会关闭该非active 连接。
2.wait_timeout 解释
默认是28800。
该参数过大的弊端是,MySQL 里有大量的sleep 进程无法释放,从而拖累性能。
该参数过小,会遭遇到"MySQL has gone away "之类的问题。
其实有两个 wait_timeout,一个是global 一个是session。通常 show variables 即是 session 级别。
3.wait_timeout 的查询与修改
查询
show global variables like 'wait_timeout'
修改
set global wait_timeout=XX;
三.net_read_timeout
官网介绍:
"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 slave_net_timeout."
说明,mysql 读数据时的等待超时。
四.innodb_lock_wait_timeout
介绍
当transaction 在等待资源时,它会先等待资源变成 free ,不然的话,将会停止等待并返回error.
该配置,决定了transaction 要等资源等多久,超过这个时间,transaction 将会放弃等待。
默认值:50秒
最小值:1秒
超时后,正在执行的sql 语句将会回滚;用户的程序也许会重新执行sql 语句。