1. 現(xiàn)象      

在日常的mysql運(yùn)維中我們?cè)谘矙z的時(shí)候經(jīng)常會(huì)發(fā)現(xiàn)大量的sleep進(jìn)程，如下圖：

2. 原因

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

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

2.數(shù)據(jù)庫(kù)中sql的查詢時(shí)間過長(zhǎng)

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

3.解決方案

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

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

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

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

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

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

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

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

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

wait_timeout 用來(lái)控制jdbc等應(yīng)用程序連接數(shù)據(jù)庫(kù)長(zhǎng)時(shí)間不活躍MySQL主動(dòng)斷開連接的時(shí)間

interactive_timeout 用來(lái)控制mysql客戶端ide工具連接數(shù)據(jù)庫(kù)長(zhǎng)時(shí)間不活躍MySQL主動(dòng)斷開連接的時(shí)間

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

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

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

處理方式:

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

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

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

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

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

1.查詢需要kill掉的無(wú)效連接進(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ù)實(shí)際情況修改

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

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

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

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

配置示例

3.最佳實(shí)踐：

3.1 MySQL參數(shù)設(shè)置

wait_timeout :900秒 #通常設(shè)置為900秒超時(shí)，各業(yè)務(wù)線可以根據(jù)實(shí)際情況調(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"  驗(yàn)證連接是否可用，使用的SQL語(yǔ)句
testWhileIdle = "true"      指明連接是否被空閑連接回收器(如果有)進(jìn)行檢驗(yàn).如果檢測(cè)失敗,則連接將被從池中去除.
timeBetweenEvictionRunsMillis = "30000"  每30秒運(yùn)行一次空閑連接回收器
minEvictableIdleTimeMillis = "1800000"  
//1000 * 60 * 30  連接在池中保持空閑而不被空閑連接回收器線程,(如果有)回收的最小時(shí)間值，單位毫秒
池中的連接空閑30分鐘后被回收,默認(rèn)值就是30分鐘。
numTestsPerEvictionRun="5" 在每次空閑連接回收器線程(如果有)運(yùn)行時(shí)檢查的連接數(shù)量，默認(rèn)值就是5.

總結(jié)

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

最新評(píng)論