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_ti、eout???????????|?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的。用戶名密碼認證則是服務端先生成一個隨機數發送給客戶端，客戶端用該隨機數和密碼進行多次循環加密后發送給服務端驗證。如果通過，整個連接握手過程完成。（具體握手過程后續找到資料再分析）
由此可見，整個連接握手可能會有各種可能出錯。所以這個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:????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
同樣來測試下(先創建一個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?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不變。
那么如果innodb_rollback_on_timeout=ON,同樣事務2會超時，但是這個時候如果我們begin開啟新的事務，那么會回滾請求鎖超時的整個事務，而不是像前面那樣只回滾了超時的那條語句。
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 in循環的方式導入很大的一個文件到數據庫中，然后中途用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）或者網絡出現問題時起作用。
【相關推薦】
1.?循環
2.?循環
3.?循環
