一、mysql中的wait_timeout坑

mysql> show variables like '%timeout%';

首先你要明白：

• wait_timeout — 指的是mysql在關(guān)閉一個(gè)非交互的連接之前所要等待的秒數(shù)，其取值范圍為1-2147483(Windows)，1-31536000(linux)，默認(rèn)值28800。
• nteractive_time — 指的是mysql在關(guān)閉一個(gè)交互的連接之前所要等待的秒數(shù)(交互連接如mysql gui tool中的連接)，其取值范圍隨wait_timeout變動(dòng)，默認(rèn)值28800。

所謂的交互式連接，即在mysql_real_connect()函數(shù)中使用了CLIENT_INTERACTIVE選項(xiàng)。說(shuō)得直白一點(diǎn)，通過(guò)mysql客戶端連接數(shù)據(jù)庫(kù)是交互式連接，通過(guò)jdbc連接數(shù)據(jù)庫(kù)是非交互式連接。

MySQL 的默認(rèn)設(shè)置下，當(dāng)一個(gè)連接的空閑時(shí)間超過(guò)8小時(shí)后，一到高峰期肯定會(huì)造成，會(huì)有太多的TCP連接沒(méi)關(guān)閉，數(shù)據(jù)庫(kù)連接數(shù)肯定是不夠。

從而會(huì)產(chǎn)生CPU占用過(guò)高，服務(wù)器告警等問(wèn)題。因EPG的一個(gè)訪問(wèn)一次對(duì)數(shù)據(jù)庫(kù)操作量不大，查詢完數(shù)據(jù)就完成ok了，wait_timeout 設(shè)置在120s內(nèi)就很完美了。

1、第一種修改方式 需重啟MySQL(6.5為例)

1.1、修改參數(shù)配置文件　　 

vi /etc/my.cnf

[mysqld]
wait_timeout?? ?= 90
interactive_timeout?? ?=? 90
#增加以上兩列即可，因?yàn)楣俜轿臋n要求修改此參數(shù)必須同時(shí)修改interactive_timeout

1.2、重啟數(shù)據(jù)庫(kù)　

service mysqld restart?　

或者

/etc/init.d/mysql restart

1.3、查看數(shù)據(jù)庫(kù)參數(shù)是否修改成功 

連接MySQL 然后查看 

show variables like ‘wait_timeout';

2、第二種修改方式 不需重啟MySQL（數(shù)據(jù)庫(kù)重啟就失效）

mysql>?show?variables?like?'%timeout';

　　　　+-------------------------+-------+
　　　　|?Variable_name???????????|?Value?|
　　　　+-------------------------+-------+
　　　　|?connect_timeout?????????|?10????|
　　　　|?delayed_insert_timeout??|?300???|
　　　　|?interactive_timeout?????|?200???|
　　　　|?net_read_timeout????????|?30????|
　　　　|?net_write_timeout???????|?60????|
　　　　|?slave_net_timeout???????|?3600??|
　　　　|?table_lock_wait_timeout?|?50????|
　　　　|?wait_timeout????????????|?200???|
　　　　+-------------------------+-------+

　　mysql>?set?global?interactive_timeout=100;

　　　　Query?OK,?0?rows?affected?(0.00?sec)

退出后重新登錄mysql

如果修改interactive_timeout的話wait_timeout也會(huì)跟著變，而只修改wait_timeout是不生效的。

二、排查有沒(méi)有地方占用SQL資源過(guò)多

show databases;

3、use  數(shù)據(jù)庫(kù)名;         #切換到常用數(shù)據(jù)庫(kù)

4、show full processlist;    #顯示哪些線程正在運(yùn)行

或者：

-- select * from information_schema.PROCESSLIST?where info is not null;

說(shuō)明各列的含義和用途

• id列: 一個(gè)標(biāo)識(shí)，你要kill 一個(gè)語(yǔ)句的時(shí)候很有用。
• user列:  顯示當(dāng)前用戶，如果不是root，這個(gè)命令就只顯示你權(quán)限范圍內(nèi)的sql語(yǔ)句。
• host列: 顯示這個(gè)語(yǔ)句是從哪個(gè)ip 的哪個(gè)端口上發(fā)出的?？捎脕?lái)追蹤出問(wèn)題語(yǔ)句的用戶。
• db列: 顯示這個(gè)進(jìn)程目前連接的是哪個(gè)數(shù)據(jù)庫(kù)。
• command列: 顯示當(dāng)前連接的執(zhí)行的命令，一般就是休眠（sleep），查詢（query），連接（connect）。

 通常代表資源未釋放，如果是通過(guò)連接池，sleep狀態(tài)應(yīng)該恒定在一定數(shù)量范圍內(nèi)

 實(shí)戰(zhàn)范例：因前端數(shù)據(jù)輸出時(shí)（特別是輸出到用戶終端）未及時(shí)關(guān)閉數(shù)據(jù)庫(kù)連接，導(dǎo)致因網(wǎng)絡(luò)連接速度產(chǎn)生大量sleep連接，在網(wǎng)速出現(xiàn)異常時(shí)，數(shù)據(jù)庫(kù)too many connections掛死。

簡(jiǎn)單解讀，數(shù)據(jù)查詢和執(zhí)行通常只需要不到0.01秒，而網(wǎng)絡(luò)輸出通常需要1秒左右甚至更長(zhǎng)，原本數(shù)據(jù)連接在0.01秒即可釋放，但是因?yàn)榍岸顺绦蛭磮?zhí)行close操作，直接輸出結(jié)果，那么在結(jié)果未展現(xiàn)在用戶桌面前，該數(shù)據(jù)庫(kù)連接一直維持在sleep狀態(tài)！

• time列: 此這個(gè)狀態(tài)持續(xù)的時(shí)間，單位是秒。
• state列: 顯示使用當(dāng)前連接的sql語(yǔ)句的狀態(tài)，很重要的列，后續(xù)會(huì)有所有的狀態(tài)的描述，請(qǐng)注意，state只是語(yǔ)句執(zhí)行中的某一個(gè)狀態(tài)，一個(gè)sql語(yǔ)句，已查詢?yōu)槔?，可能需要?jīng)過(guò)copying to tmp table，Sorting result，Sending data等狀態(tài)才可以完成。
• info列: 顯示這個(gè)sql語(yǔ)句，因?yàn)殚L(zhǎng)度有限，所以長(zhǎng)的sql語(yǔ)句就顯示不全，但是一個(gè)判斷問(wèn)題語(yǔ)句的重要依據(jù)。

其中state的狀態(tài)十分關(guān)鍵

下表列出state主要狀態(tài)和描述：

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論