mysql timeout知多少，mysqltimeout知

mysql timeout知多少，mysqltimeout知
mysql timeout知多少

                         ——————by石鍋拌飯

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

mysql的基本原理應該是有個監聽線程迴圈接收請求，當有請求來時，建立線程（或者從線程池中取）來處理這個請求。由於mysql串連採用TCP協議，那麼之前勢必是需要進行TCP三向交握的。TCP三向交握成功之後，用戶端會進入阻塞，等待服務端的訊息。服務端這個時候會建立一個線程(或者從線程池中取一個線程)來處理請求，主要驗證部分包括host和使用者名稱密碼驗證。host驗證我們比較熟悉，因為在用grant命令授權使用者的時候是有指定host的。使用者名稱密碼認證則是服務端先產生一個隨機數發送給用戶端，用戶端用該隨機數和密碼進行多次sha1加密後發送給服務端驗證。如果通過，整個串連握手過程完成。（具體握手過程後續找到資料再分析）

由此可見，整個串連握手可能會有各種可能出錯。所以這個connect_timeout值就是指這個逾時時間了。可以簡單測試下，運行下面的telnet命令會發現用戶端會在10秒後逾時返回。

telnet localhost 3306

在逾時之前mysql中該串連狀態如下：

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，這時候可以看到：

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        |+-----------------------------+----------+

可以看到wait_timeout被設定為了interactive_timeout的值，這樣，我們3秒後再執行其他命令，會提示如下：

mysql> show variables like '%timeout%';ERROR 2006 (HY000): MySQL server has gone away  ##逾時重連No connection. Trying to reconnect...Connection id:    50Current 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

同樣來測試下(先建立一個innodb引擎的表test，只有一列，列名為a)：

mysql> CREATE TABLE `test` ( `a` int primary key) engine=innodb;

首先插入三條測試資料

mysql> select * from test;+---+| a |+---+| 1 || 2 || 3 |

當前innodb_rollback_on_timeout=OFF，設定innodb_lock_wait_timeout=1，我們開啟兩個事務

##事務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 transactionmysql> select * from test;+---+| a |+---+| 2 || 3 |+---+2 rows in set (0.00 sec)mysql> begin; ##這裡我們直接開啟另外的事務，則原來的事務只會復原第二條語句，最終結果就是test表中只剩下2和3.如果這裡我們顯示的rollback，則會復原整個事務，保持1，2，3不變。

那麼如果innodb_rollback_on_timeout=ON,同樣事務2會逾時，但是這個時候如果我們begin開啟新的事務，那麼會復原請求鎖逾時的整個事務，而不是像前面那樣只復原了逾時的那條語句。

2.4 lock_wait_timeout

文檔中描述如下，簡單說來lock_wait_timeout是中繼資料鎖等待逾時，任意鎖中繼資料的語句都會用到這個逾時參數，預設為一年。中繼資料鎖可以參加mysql metadata lock，為了保證事務可序列化，不管是myisam還是innodb引擎的表，只要是開始一個事務，就會擷取動作表的中繼資料鎖，這時候如果另一個事務要對錶的中繼資料進行修改，則會阻塞直到逾時。

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)，現在我們先開啟一個事務，然後執行一個select語句。另外開啟一個session，然後執行表的中繼資料操作，如刪除表，會發現操作阻塞直到lock_wait_timeout秒後提示逾時。

##第一個session，擷取metadata lockmysql> 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=latin1mysql> 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 DROPiptables -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）或者網路出現問題時起作用。

4.參考資料

• mysql timeout調研與執行個體
• mysql timeout解析
• mysql system variables