一、mysql中的wait_timeout坑

mysql> show variables like '%timeout%';

首先你要明白：

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

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

MySQL 的默認設置下，當一個連接的空閑時間超過8小時后，一到高峰期肯定會造成，會有太多的TCP連接沒關閉，數(shù)據(jù)庫連接數(shù)肯定是不夠。

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

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

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

vi /etc/my.cnf

[mysqld]
wait_timeout?? ?= 90
interactive_timeout?? ?=? 90
#增加以上兩列即可，因為官方文檔要求修改此參數(shù)必須同時修改interactive_timeout

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

service mysqld restart?　

或者

/etc/init.d/mysql restart

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

連接MySQL 然后查看 

show variables like ‘wait_timeout';

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

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也會跟著變，而只修改wait_timeout是不生效的。

二、排查有沒有地方占用SQL資源過多

show databases;

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

4、show full processlist;    #顯示哪些線程正在運行

或者：

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

說明各列的含義和用途

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

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

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

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

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

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

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

總結

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

最新評論