1. 現(xiàn)象      

在日常的mysql運維中我們在巡檢的時候經(jīng)常會發(fā)現(xiàn)大量的sleep進程，如下圖：

2. 原因

這種現(xiàn)象一般由三種情況導致的：

1.程序中對mysql_close的調用不佳

2.數(shù)據(jù)庫中sql的查詢時間過長

3.wait_timeout、interactive_timeout兩個參數(shù)的值過大

3.解決方案

常見的問題是第三種，我們先來理解一下wait_timeout、interactive_timeout這兩個參數(shù)的含義：

根據(jù)mysql5.7官方文檔的說明：

wait_timeout可設置為全局、會話級別，嗎，默認值28800，最小值1，最大值3153600 ，單位是秒 。

他是指服務器在關閉非交互式連接之前等待該連接活動的秒數(shù)。

在線程啟動時，會話wait_timeout值從全局wait_timeout值或從全局interactive_timeout值初始化，這取決于客戶端的類型(由mysql_real_connect()的CLIENT_INTERACTIVE connect選項定義)。另請參見interactive_timeout。

interactive_timeout可設置為全局、會話級別，嗎，默認值28800，最小值1，最大值3153600 ，單位是秒 。

服務器在關閉交互式連接之前等待其活動的秒數(shù)。交互式客戶端定義為使用mysql_real_connect()的CLIENT_INTERACTIVE選項的客戶端。另請參見wait_timeout。

在配置這兩個參數(shù)的時候要同時配置保持一致。

下面我們理解一下這兩個參數(shù)真正的作用：

wait_timeout 用來控制jdbc等應用程序連接數(shù)據(jù)庫長時間不活躍MySQL主動斷開連接的時間

interactive_timeout 用來控制mysql客戶端ide工具連接數(shù)據(jù)庫長時間不活躍MySQL主動斷開連接的時間

在MySQL中，Sleep是一個狀態(tài)，表示連接已建立，但當前沒有任何活動。Sleep進程是指MySQL中當前處于Sleep狀態(tài)的進程，這些進程占用了MySQL資源，但卻沒有實際運作。當連接建立之后，如果沒有任何操作，連接將一直保持在Sleep狀態(tài)，直到連接超時或被關閉。

Mysql通過wait_timeout 、interactive_timeout 這兩個參數(shù)完成對Sleep進程的釋放，當sleep的時間達到wait_timeout 、interactive_timeout的閾值時會被mysql自動清理。

注意：這里引申一個概念：一般數(shù)據(jù)庫與中間件連接的時候都會通過連接池去管理客戶端對數(shù)據(jù)庫發(fā)起的連接，在代碼里conn.close()方法，在實際意義上只是把這個連接交還給了連接池，和數(shù)據(jù)庫的連接并沒有真正斷開。wait_timeout時間到了以后,服務端MySQL斷掉連接后，并不會通知（也沒法通知）連接池，所以連接池里的連接如果被拿去使用時，就會產(chǎn)生Communications link failure 報錯。

處理方式:

1.查看sleep默認的超時時間SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';

2.設置超時時間編程30秒，注意：必須兩個參數(shù)同時設置才會生效。set global wait_timeout=30;set global interactive_timeout=30;

具體的參數(shù)值要結合連接池的超時時間，最大sql執(zhí)行時間，業(yè)務的最大持續(xù)時間來設定。比如阿里的默認配置時間是120s。對于高并發(fā)業(yè)務對于這兩個值的配置建議在1~3分鐘之內(nèi)。

建議寫到mysql配置文件中否則下次重啟mysql參數(shù)失效。

手動處理sleep進程的方法：

1.查詢需要kill掉的無效連接進程SELECT GROUP_CONCAT(CONCAT('kill ',Id) SEPARATOR';') AS cmd FROM information_schema.processlist WHERE command='Sleep'USER='root'AND command='Sleep'and db = 'test';

具體條件根據(jù)實際情況修改

2.執(zhí)行查詢結果

注意:查詢結果會顯示大量的kill語句，受到字符長度限制在執(zhí)行后驗證并繼續(xù)執(zhí)行.

3.重新執(zhí)行查詢驗證sleep是否消失SELECT*FROMinformation_schema.processlist WHERE USER='root'AND command='Sleep'and db = 'test'order by time desc;

具體條件根據(jù)實際情況修改

配置示例

3.最佳實踐：

3.1 MySQL參數(shù)設置

wait_timeout :900秒 #通常設置為900秒超時，各業(yè)務線可以根據(jù)實際情況調整
interactive_timeout :900秒 #建議和wait_timeout保持一致

3.2 jdbc端設置:

jdbc.connection-pool.max-idle-time  <  wait_timeout
jdbc.connection-pool.idle-test-interval  <  interactive_timeout

3.3 apache連接池配置

validationQuery = "SELECT 1"  驗證連接是否可用，使用的SQL語句
testWhileIdle = "true"      指明連接是否被空閑連接回收器(如果有)進行檢驗.如果檢測失敗,則連接將被從池中去除.
timeBetweenEvictionRunsMillis = "30000"  每30秒運行一次空閑連接回收器
minEvictableIdleTimeMillis = "1800000"  
//1000 * 60 * 30  連接在池中保持空閑而不被空閑連接回收器線程,(如果有)回收的最小時間值，單位毫秒
池中的連接空閑30分鐘后被回收,默認值就是30分鐘。
numTestsPerEvictionRun="5" 在每次空閑連接回收器線程(如果有)運行時檢查的連接數(shù)量，默認值就是5.

總結

到此這篇關于mysql5.7大量sleep進程常規(guī)處理方式及配置的文章就介紹到這了,更多相關mysql5.7大量sleep進程處理內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論