1. 現(xiàn)象      

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

2. 原因

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

1.程序中對mysql_close的調(diào)用不佳

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

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

3.解決方案

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

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

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

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

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

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

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

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

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

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

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

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

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

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

處理方式:

1.查看sleep默認(rèn)的超時時間SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';

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

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

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

手動處理sleep進(jìn)程的方法：

1.查詢需要kill掉的無效連接進(jìn)程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í)行查詢結(jié)果

注意:查詢結(jié)果會顯示大量的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ù)設(shè)置

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

3.2 jdbc端設(shè)置:

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"      指明連接是否被空閑連接回收器(如果有)進(jìn)行檢驗.如果檢測失敗,則連接將被從池中去除.
timeBetweenEvictionRunsMillis = "30000"  每30秒運行一次空閑連接回收器
minEvictableIdleTimeMillis = "1800000"  
//1000 * 60 * 30  連接在池中保持空閑而不被空閑連接回收器線程,(如果有)回收的最小時間值，單位毫秒
池中的連接空閑30分鐘后被回收,默認(rèn)值就是30分鐘。
numTestsPerEvictionRun="5" 在每次空閑連接回收器線程(如果有)運行時檢查的連接數(shù)量，默認(rèn)值就是5.

總結(jié)

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

最新評論