MySQL CPU過高的排查方法
一. 問題鎖定
通過top命令查看服務(wù)器CPU資源使用情況,明確CPU占用率較高的是否是mysqld進(jìn)程,如果是則可以明確CUP飄高的原因就是MySQL數(shù)據(jù)庫導(dǎo)致的。
二. QPS激增會(huì)導(dǎo)致CPU飄高
分析:
(引用網(wǎng)圖)
在有監(jiān)控工具的情況下,通過查看CPU利用率曲線圖和QPS曲線圖進(jìn)行對(duì)比,如果CPU曲線圖和QPS曲線圖波動(dòng)情況基本保持一致,出現(xiàn)CPU過高則必然和QPS激增有關(guān)系,至此可以明確CUP過高是QPS上升導(dǎo)致。反之,如果CUP曲線圖對(duì)比QPS曲線圖有不同步的峰值抖動(dòng),則說明在QPS未明顯激增的情況下,CPU出現(xiàn)飄高,則大概率跟慢SQL有關(guān),可以進(jìn)行后續(xù)的慢SQL排查分析。
解決(依據(jù)情況選用以下辦法):
● 如果是實(shí)際業(yè)務(wù)激增導(dǎo)致則可以通過資源擴(kuò)充,滿足業(yè)務(wù)激增的業(yè)務(wù)需求。
● 找到具體激增的查詢所對(duì)應(yīng)應(yīng)用系統(tǒng)的接口,進(jìn)行限流控制,以保護(hù)數(shù)據(jù)庫并發(fā)訪問量。
● 應(yīng)用采用讀寫分離,降低單點(diǎn)訪問壓力。
● 查看SQL是否存在循環(huán)插入或更新的情況,改動(dòng)批量操作。
備注:
● 如果沒有監(jiān)控工具協(xié)助QPS分析的情況下,可以通過show global status like ‘Questions’ 和show global status like 'Uptime’查詢,將兩者相除得到就是QPS值。
三. 慢SQL會(huì)導(dǎo)致CPU飄高
分析(通過show processlist)
通過show processlist查看當(dāng)前MySQL線程運(yùn)行情況,主要通過 Time 連接時(shí)間和State當(dāng)前SQL所處的狀態(tài)來分析慢SQL,一般情況下如果存在Sending data說明該查詢較慢,可以將info中的SQL復(fù)制出來通過explain查看詳細(xì)的執(zhí)行計(jì)劃進(jìn)行分析。
分析(通過MySQL自帶的慢SQL日志功能)
通過root登錄數(shù)據(jù)庫開啟慢SQL查詢?nèi)罩?,set global slow_query_log = ‘ON’,并設(shè)置慢SQL過濾時(shí)間set global long_query_time = 1(超過1秒視為慢SQL),指定慢SQL日志文件存放路徑set global slow_query_log_file = ‘/var/lib/mysql/test_1116.log’。通過持續(xù)觀察該慢SQL記錄日志文件,查找出具體的慢SQL復(fù)制出來通過explain查看詳細(xì)的執(zhí)行計(jì)劃進(jìn)行分析。
解決(依據(jù)情況選用以下辦法)
● 緊急處理先通過 kill process id,先kill對(duì)應(yīng)線程,緩解問題。
● 無索引或者索引失效,新建有效索引或者優(yōu)化SQL語句。
● SQL中有大量聚合操作:簡化SQL,將邏輯提煉到業(yè)務(wù)代碼中;聚合操作異步化或預(yù)處理。
● SQL返回的數(shù)據(jù)過多:分頁查詢。
● 讀寫較多鎖競爭激烈:分庫分表或讀寫分離。
四. 大量空閑連接會(huì)導(dǎo)致CPU飄高
分析:
通過 show full processlist 查看Comand 有大量的Sleep,再根據(jù)Time查看裝填時(shí)間是否很長,如果存在大量長時(shí)間睡眠線程占用數(shù)據(jù)庫連接,會(huì)導(dǎo)致數(shù)據(jù)庫的吞吐量下降,進(jìn)而導(dǎo)致阻塞也會(huì)引發(fā)CPU飄高的現(xiàn)象。
解決:
● 修改mysql配置文件中的 wait_timeout 空閑等待時(shí)間,值越小則相對(duì)空閑線程就會(huì)越少,重啟mysql生效。
● 也可以通過 set global wat_timeout=xx 方式熱修改該參數(shù)。
五. MySQL問題排查常用命令
5.1 SQL 執(zhí)行計(jì)劃,進(jìn)行SQL執(zhí)行分析。
explain + sql 。
5.2 查看數(shù)據(jù)庫當(dāng)前執(zhí)行線程狀態(tài)
show full processlist : 其中重點(diǎn)字段包括Comand 、Time 、State、Info,可以查看當(dāng)前所有連接線程中命令、持續(xù)時(shí)間、狀態(tài)、對(duì)應(yīng)的SQL信息。
5.3 查看當(dāng)前運(yùn)行的所有事務(wù)
select * from information_schema.INNODB_TRX : 在執(zhí)行結(jié)果中可以看到是否有表鎖等待或者死鎖,如果有死鎖發(fā)生,可以通過下面的命令來殺掉當(dāng)前運(yùn)行的事務(wù):KILL trx_mysql_thread_id 。
5.4 查看當(dāng)前出現(xiàn)的鎖
select * from information_schema.INNODB_LOCKS:在改結(jié)果中可以看到鎖的類型、所屬事務(wù)ID、鎖級(jí)別、鎖模式等信息。
5.5 查看死鎖
SELECT b.trx_state, e.state, e.time, d.state AS block_state, d.time AS block_time , a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id , a.blocking_lock_id, c.trx_query AS block_trx_query, c.trx_mysql_thread_id AS block_trx_mysql_tread_id FROM information_schema.INNODB_LOCK_WAITS a LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id ORDER BY a.requesting_trx_id;
5.5 查看InnoDB狀態(tài)
SHOW ENGINE INNODB STATUS
以上就是MySQL CPU過高的排查方法的詳細(xì)內(nèi)容,更多關(guān)于MySQL CPU過高排查的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
MySQL20個(gè)高性能架構(gòu)設(shè)計(jì)原則(值得收藏)
這篇文章主要介紹了MySQL20個(gè)高性能架構(gòu)設(shè)計(jì)原則,幫助大家更好的理解和使用MySQL,感興趣的朋友可以了解下2020-08-08MySQL5.7.24版本的數(shù)據(jù)庫安裝過程圖文詳解
這篇文章主要介紹了MySQL5.7.24版本的數(shù)據(jù)庫安裝過程,需要的朋友可以參考下2018-11-11mysql中一個(gè)普通ERROR 1135 (HY000)錯(cuò)誤引發(fā)的血案
ERROR 1135 (HY000): Can’t create a new thread (errno 11);if you are not out of available memory,you can consult the manual for a possible OS-dependent bug2015-08-08十個(gè)節(jié)省時(shí)間的MySQL命令小結(jié)
編者在工作中積累起來了一些MySQL命令行客戶端技巧,這些技巧或多或少會(huì)幫助您節(jié)省大量的時(shí)間。2011-03-03深入解析Linux下MySQL數(shù)據(jù)庫的備份與還原
以下是對(duì)Linux下MySQL數(shù)據(jù)庫的備份與還原進(jìn)行了詳細(xì)的分析介紹。需要的朋友可以過來參考下2013-08-08