mysql影片教學欄位介紹timeout變數這個東東
#1、timeout變數知多少
開啟mysql，用show variables like '%timeout%'指令一看，不看不知道，一看嚇一跳，結果如下面所示，這麼多timeout相關變量，一下就嚇尿了。 。原來對mysql的了解原來是如此的不夠，好了，這麼些timeout究竟各自是什麼意思，花了一下午去學習，做了幾個小實驗，總算明白了一二，如有錯誤，請不吝賜教啊。
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.分析
下面從timeout裡面找些比較常用的出來逐一分析下。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
telnet localhost 3306
256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL
2.2 interactive_timeout & wait_timeout
#還是先看官方文檔，從文件來看wait_timeout和interactive_timeout都是指不活躍的連線逾時時間，連線執行緒啟動的時候wait_timeout會根據是互動模式或非互動模式被設定為這兩個值中的一個。如果我們執行mysql -uroot -p指令登陸到mysql，wait_timeout就會被設定為interactive_timeout的值。如果我們在wait_timeout時間內沒有進行任何操作，那麼再次操作的時候就會提示逾時，這是mysql client會重新連線。
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()).
mysql> set global interactive_timeout=3; ##设置交互超时为3秒
mysql> show variables like '%timeout%'; ##wait_timeout已经被设置为3秒 +-----------------------------+----------+ | 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 | 3 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 3 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 3600 | | wait_timeout | 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 *** +-----------------------------+----------+ | 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 | 3 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 3 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 3600 | | wait_timeout | 3 | +-----------------------------+----------+
2.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout
#還是先祭出官方文檔，從文檔中看，這個值是針對innodb引擎的，是innodb中行鎖的等待超時時間，預設為50秒。如果逾時，則目前語句會回滾。如果設定了innodb_rollback_on_timeout，則會回滾整個事務，否則，只回滾事務等待行鎖的這個語句。The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> CREATE TABLE `test` ( `a` int primary key) engine=innodb;
mysql> select * from test; +---+ | a | +---+ | 1 | | 2 | | 3 |
##事务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)
##事务2，请求行锁 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 mysql> select * from test; +---+ | a | +---+ | 2 | | 3 | +---+ 2 rows in set (0.00 sec) mysql> begin; ##这里我们直接开启另外的事务(或者直接commit当前事务)，则原来的事务只会回滚第二条语句，最终结果就是test表中只剩下2和3.如果这里我们显示的rollback，则会回滚整个事务，保持1，2，3不变。
2.4 lock_wait_timeout
在文件中描述如下，簡單說來lock_wait_timeout是元資料鎖等待超時，任意鎖元資料的語句都會用到這個超時參數，默認為一年。元資料鎖可以參加mysql metadata lock，為了確保事務可串列化，不管是myisam還是innodb引擎的表，只要是先在一個session裡面開啟一個事務，就會獲取操作表的元資料鎖，這時候如果另一個session要對表的元資料進行修改，則會阻塞直到逾時。This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000. This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements
我們用一個myisam引擎的表myisam_test來測試。其中有一筆記錄(1,1)，現在我們先開啟一個session，然後執行一個select語句。另外開啟一個session，然後執行表格的元資料操作，如刪除表，會發現操作阻塞直到lock_wait_timeout秒後提示逾時。
##第一个session，获取metadata lock mysql> show create table myisam_test; -----------------------------------------------------------+ | Table | Create Table | +----------------------------------------------------------- | myisam_test | CREATE TABLE `myisam_test` ( `i` int(11) NOT NULL, `j` int(11) DEFAULT NULL, PRIMARY KEY (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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) ##另一个session，删除表提示超时 mysql> drop table myisam_test; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
其中更改表结构的元数据操作指令有如下这些：
DROP TABLE t; ALTER TABLE t ...; DROP TABLE nt; ALTER TABLE nt ...; LOCK TABLE t ... WRITE；
当然，多说一句，对于myisam表的加锁以及并发插入等，这篇博客myisam表锁非常详细，有兴趣的可以看看。
2.5 net_read_timeout & net_write_timeout
文档中描述如下，就是说这两个参数在网络条件不好的情况下起作用。比如我在客户端用load data infile的方式导入很大的一个文件到数据库中，然后中途用iptables禁用掉mysql的3306端口，这个时候服务器端该连接状态是reading from net，在等待net_read_timeout后关闭该连接。同理，在程序里面查询一个很大的表时，在查询过程中同样禁用掉端口，制造网络不通的情况，这样该连接状态是writing to net，然后在net_write_timeout后关闭该连接。slave_net_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
测试：
我创建一个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秒后关闭。
3.总结
经过几个实验可以发现，connect_timeout在握手认证阶段（authenticate）起作用，interactive_timeout 和wait_timeout在连接空闲阶段（sleep）起作用，而net_read_timeout和net_write_timeout则是在连接繁忙阶段（query）或者网络出现问题时起作用。
以上是探秘mysql timeout變數的詳細內容。更多資訊請關注PHP中文網其他相關文章！
