大規(guī)模多線程操作事務(wù)的時(shí)候，有時(shí)候打開一個(gè)鏈接，會(huì)進(jìn)行等待，這時(shí)候如果數(shù)據(jù)庫(kù)的超時(shí)時(shí)間設(shè)置的過(guò)短，就可能會(huì)出現(xiàn)，數(shù)據(jù)鏈接自動(dòng)被釋放，當(dāng)然設(shè)置過(guò)大也不好，慢SQL或其他因素引起的鏈接過(guò)長(zhǎng)，導(dǎo)致整個(gè)系統(tǒng)被拖慢，甚至掛掉。SO，適當(dāng)?shù)脑O(shè)置超時(shí)時(shí)間。設(shè)置方法：
網(wǎng)站建設(shè)公司,為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì)及定制網(wǎng)站建設(shè)服務(wù),專注于成都企業(yè)網(wǎng)站建設(shè),高端網(wǎng)頁(yè)制作,對(duì)成都混凝土攪拌站等多個(gè)行業(yè)擁有豐富的網(wǎng)站建設(shè)經(jīng)驗(yàn)的網(wǎng)站建設(shè)公司。專業(yè)網(wǎng)站設(shè)計(jì),網(wǎng)站優(yōu)化推廣哪家好,專業(yè)成都網(wǎng)站推廣優(yōu)化,H5建站,響應(yīng)式網(wǎng)站。
SHOW GLOBAL VARIABLES LIKE '%timeout%'
SET GLOBAL wait_timeout=10000
網(wǎng)頁(yè)鏈接
1.timeout變量知多少
打開mysql，用show variables like
'%timeout%'命令一看，不看不知道，一看嚇一跳，結(jié)果如下面所示，這么多timeout相關(guān)變量，一下就嚇尿了。。原來(lái)對(duì)mysql的了解原來(lái)
是如此的不夠，好了，這么些timeout究竟各自是什么意思，花了一下午去學(xué)習(xí)，做了幾個(gè)小實(shí)驗(yàn)，總算明白了一二，如有錯(cuò)誤，請(qǐng)不吝賜教啊。
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里面找些比較常用的出來(lái)逐個(gè)分析下。
2.1 connect_timeout
connect_timeout指的是連接過(guò)程中握手的超時(shí)時(shí)間，在5.0.52以后默認(rèn)為10秒，之前版本默認(rèn)是5秒。官方文檔是這樣說(shuō)的：
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的基本原理應(yīng)該是有個(gè)監(jiān)聽線程循環(huán)接收請(qǐng)求，當(dāng)有請(qǐng)求來(lái)時(shí)，創(chuàng)建線程（或者從線程池中?。﹣?lái)處理這個(gè)請(qǐng)求。由于mysql連接采用TCP
協(xié)議，那么之前勢(shì)必是需要進(jìn)行TCP三次握手的。TCP三次握手成功之后，客戶端會(huì)進(jìn)入阻塞，等待服務(wù)端的消息。服務(wù)端這個(gè)時(shí)候會(huì)創(chuàng)建一個(gè)線程(或者從線
程池中取一個(gè)線程)來(lái)處理請(qǐng)求，主要驗(yàn)證部分包括host和用戶名密碼驗(yàn)證。host驗(yàn)證我們比較熟悉，因?yàn)樵谟胓rant命令授權(quán)用戶的時(shí)候是有指定
host的。用戶名密碼認(rèn)證則是服務(wù)端先生成一個(gè)隨機(jī)數(shù)發(fā)送給客戶端，客戶端用該隨機(jī)數(shù)和密碼進(jìn)行多次sha1加密后發(fā)送給服務(wù)端驗(yàn)證。如果通過(guò)，整個(gè)連
接握手過(guò)程完成。（具體握手過(guò)程后續(xù)找到資料再分析）
由此可見，整個(gè)連接握手可能會(huì)有各種可能出錯(cuò)。所以這個(gè)connect_timeout值就是指這個(gè)超時(shí)時(shí)間了?？梢院?jiǎn)單測(cè)試下，運(yùn)行下面的telnet命令會(huì)發(fā)現(xiàn)客戶端會(huì)在10秒后超時(shí)返回。
telnet localhost 3306
在超時(shí)之前mysql中該連接狀態(tài)如下：
256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL
2.2 interactive_timeout wait_timeout
還是先看官方文檔，從文檔上來(lái)看wait_timeout和interactive_timeout都是指不活躍的連接超時(shí)時(shí)間，連接線程啟動(dòng)的時(shí)
候wait_timeout會(huì)根據(jù)是交互模式還是非交互模式被設(shè)置為這兩個(gè)值中的一個(gè)。如果我們運(yùn)行mysql -uroot
-p命令登陸到mysql，wait_timeout就會(huì)被設(shè)置為interactive_timeout的值。如果我們?cè)趙ait_timeout時(shí)間
內(nèi)沒有進(jìn)行任何操作，那么再次操作的時(shí)候就會(huì)提示超時(shí)，這是mysql client會(huì)重新連接。
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()).
測(cè)試如下：
mysql set global interactive_timeout=3; ##設(shè)置交互超時(shí)為3秒
重新進(jìn)入mysql，這時(shí)候可以看到：
mysql show variables like '%timeout%'; ##wait_timeout已經(jīng)被設(shè)置為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被設(shè)置為了interactive_timeout的值，這樣，我們3秒后再執(zhí)行其他命令，會(huì)提示如下：
mysql show variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away ##超時(shí)重連
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
還是先祭出官方文檔，從文檔中看，這個(gè)值是針對(duì)innodb引擎的，是innodb中行鎖的等待超時(shí)時(shí)間，默認(rèn)為50秒。如果超時(shí)，則當(dāng)前語(yǔ)句會(huì)回
滾。如果設(shè)置了innodb_rollback_on_timeout，則會(huì)回滾整個(gè)事務(wù)，否則，只回滾事務(wù)等待行鎖的這個(gè)語(yǔ)句。
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
同樣來(lái)測(cè)試下(先創(chuàng)建一個(gè)innodb引擎的表test，只有一列，列名為a)：
mysql CREATE TABLE `test` ( `a` int primary key) engine=innodb;
首先插入三條測(cè)試數(shù)據(jù)
mysql select * from test;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
當(dāng)前innodb_rollback_on_timeout=OFF，設(shè)置innodb_lock_wait_timeout=1，我們開啟兩個(gè)事務(wù)
##事務(wù)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)
##事務(wù)2，請(qǐng)求行鎖
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; ##請(qǐng)求行鎖超時(shí)
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; ##這里我們直接開啟另外的事務(wù)，則原來(lái)的事務(wù)只會(huì)回滾第二條語(yǔ)句，最終結(jié)果就是test表中只剩下2和3.如果這里我們顯示的rollback，則會(huì)回滾整個(gè)事務(wù)，保持1，2，3不變。
那么如果innodb_rollback_on_timeout=ON,同樣事務(wù)2會(huì)超時(shí)，但是這個(gè)時(shí)候如果我們begin開啟新的事務(wù)，那么會(huì)回滾請(qǐng)求鎖超時(shí)的整個(gè)事務(wù)，而不是像前面那樣只回滾了超時(shí)的那條語(yǔ)句。
2.4 lock_wait_timeout
文檔中描述如下，簡(jiǎn)單說(shuō)來(lái)lock_wait_timeout是元數(shù)據(jù)鎖等待超時(shí)，任意鎖元數(shù)據(jù)的語(yǔ)句都會(huì)用到這個(gè)超時(shí)參數(shù)，默認(rèn)為一年。元數(shù)據(jù)鎖
可以參加mysql metadata
lock，為了保證事務(wù)可串行化，不管是myisam還是innodb引擎的表，只要是開始一個(gè)事務(wù)，就會(huì)獲取操作表的元數(shù)據(jù)鎖，這時(shí)候如果另一個(gè)事務(wù)要
對(duì)表的元數(shù)據(jù)進(jìn)行修改，則會(huì)阻塞直到超時(shí)。
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
測(cè)試?yán)樱何覀冇靡粋€(gè)myisam引擎的表myisam_test來(lái)測(cè)試。其中有一條記錄(1,1)，現(xiàn)在我們先開啟一個(gè)事務(wù)，然后執(zhí)行一個(gè)
select語(yǔ)句。另外打開一個(gè)session，然后執(zhí)行表的元數(shù)據(jù)操作，如刪除表，會(huì)發(fā)現(xiàn)操作阻塞直到lock_wait_timeout秒后提示超
時(shí)。
##第一個(gè)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)
##另一個(gè)session，刪除表提示超時(shí)
mysql drop table myisam_test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
其中更改表結(jié)構(gòu)的元數(shù)據(jù)操作指令有如下這些：
DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE；
當(dāng)然，多說(shuō)一句，對(duì)于myisam表的加鎖以及并發(fā)插入等，這篇博客myisam表鎖非常詳細(xì)，有興趣的可以看看。
2.5 net_read_timeout net_write_timeout
文檔中描述如下，就是說(shuō)這兩個(gè)參數(shù)在網(wǎng)絡(luò)條件不好的情況下起作用。比如我在客戶端用load data infile的方式導(dǎo)入很大的一個(gè)文件到數(shù)據(jù)庫(kù)中，然后中途用iptables禁用掉mysql的
3306端口，這個(gè)時(shí)候服務(wù)器端該連接狀態(tài)是reading from
net，在等待net_read_timeout后關(guān)閉該連接。同理，在程序里面查詢一個(gè)很大的表時(shí)，在查詢過(guò)程中同樣禁用掉端口，制造網(wǎng)絡(luò)不通的情況，
這樣該連接狀態(tài)是writing to net，然后在net_write_timeout后關(guān)閉該連接。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
測(cè)試：我創(chuàng)建一個(gè)120M的數(shù)據(jù)文件data.txt。然后登陸到mysql。
mysql -uroot -h 127.0.0.1 -P 3306 --local-infile=1
導(dǎo)入過(guò)程設(shè)置iptables禁用3306端口。
iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -A OUTPUT -p tcp --sport 3306 -j DROP
可以看到連接狀態(tài)為reading from net，然后經(jīng)過(guò)net_read_timeout秒后關(guān)閉。
MYSQL_OPT_READ_TIMEOUT 是 MySQL c api 客戶端中用來(lái)設(shè)置讀取超時(shí)時(shí)間的參數(shù)。在 MySQL 的官方文檔中，該參數(shù)的描述是這樣的：
MYSQL_OPT_READ_TIMEOUT (argument type: unsigned int *)The timeout in seconds for each attempt to read from the server. There are retries if necessary, so the total effective timeout value is three times the option value. You can set the value so that a lost connection can be detected earlier than the TCP/IPClose_Wait_Timeout value of 10 minutes.
也就是說(shuō)在需要的時(shí)候，實(shí)際的超時(shí)時(shí)間會(huì)是設(shè)定值的 3 倍。但是實(shí)際測(cè)試后發(fā)現(xiàn)實(shí)際的超時(shí)時(shí)間和設(shè)置的超時(shí)時(shí)間一致。
而具體什么時(shí)候發(fā)生三倍超時(shí)，在文檔中沒有找到。所以對(duì) MySQL 5.7.20 的源碼進(jìn)行了一些分析。
使用 GDB 調(diào)試代碼找了實(shí)際與 mysql server 通信的代碼，如下：
請(qǐng)點(diǎn)擊輸入圖片描述
其中 vio_read() 函數(shù)中，使用 recv 和 poll 來(lái)讀取報(bào)文和做讀取超時(shí)。net_should_retry() 函數(shù)只有在發(fā)生 EINTR 時(shí)才會(huì)返回 true。從這段代碼來(lái)看是符合測(cè)試結(jié)果的，并沒有對(duì)讀取進(jìn)行三次重試。只有在讀取操作被系統(tǒng)中斷打斷時(shí)才會(huì)重試，但是這個(gè)重試并沒有次數(shù)限制。
從上面代碼的分析可以看出，代碼的邏輯和文檔的描述不符。于是在一頓搜索后，找到了一個(gè) MySQL 的 BUG(Bug #31163)。該 BUG 報(bào)告了在?MySQL?5.0 中，MySQL c api 讀取的實(shí)際超時(shí)時(shí)間是設(shè)置的三倍，與現(xiàn)有文檔描述相符。于是對(duì) MySQL 5.0.96 的代碼又進(jìn)行分析。
同樣使用 GDB 找到了通信部分的代碼。這次找到了重試三次的代碼，如下：
請(qǐng)點(diǎn)擊輸入圖片描述
這個(gè)版本的 MySQL api 的讀寫超時(shí)是直接使用的 setsockopt 設(shè)置的。第一次循環(huán)，在 A 點(diǎn)發(fā)生了第一次超時(shí)（雖然注釋寫的非阻塞，但是客戶端的連接始終是阻塞模式的）。然后在 B 點(diǎn)將該 socket 設(shè)置為阻塞模式，C 點(diǎn)這里重置 retry 次數(shù)。由于設(shè)置了 alarm 第二次以后的循環(huán)會(huì)直接進(jìn)入 D 點(diǎn)的這個(gè)分支，并且判斷循環(huán)次數(shù)。作為客戶端時(shí)net-retry_count 始終是 1，所以重試了兩次，共計(jì)進(jìn)行了 3 次 vioread 后從 E 點(diǎn)退出函數(shù)。
由上面的分析可知，MySQL 文檔對(duì)于該參數(shù)的描述已經(jīng)過(guò)時(shí)，現(xiàn)在的 MYSQL_OPT_READ_TIMEOUT 并不會(huì)出現(xiàn)三倍超時(shí)的問(wèn)題。而 Bug #31163 中的處理結(jié)果也是將文檔中該參數(shù)的描述更新為實(shí)際讀取超時(shí)時(shí)間是設(shè)定時(shí)間的三倍。也許是 MySQL 的維護(hù)者們?cè)诤罄m(xù)版本更新時(shí)忘記更新文檔吧。
?php
$db-query("SET interactive_timeout = 3600;");
$db-query("SET wait_timeout = 3600;");
?
重慶分公司
重慶分公司
