MySQL數(shù)據(jù)庫(kù)慢–排查問(wèn)題總結(jié)(整理自《抽絲剝繭之MySQL疑難雜癥排查》葉金榮)

1、常見(jiàn)瓶頸

  （1）SQL效率低

  （2）選項(xiàng)配置不當(dāng)

  （3）訪(fǎng)問(wèn)題飆升

  （4）硬件性能低

  （5）其他進(jìn)程搶資源

2、怎樣確認(rèn)是MySQL存在瓶頸

   top/free/vmstat/sar/mpstat確認(rèn)

   —確認(rèn)mysqld進(jìn)程的CPU消耗占比

   —確認(rèn)mysqld進(jìn)程的CPU消耗是%user,還是%sys高

   —確認(rèn)是否物理內(nèi)存不夠用了

   —確認(rèn)是否有swap產(chǎn)生

   —確認(rèn)CPU上是否有大量中斷（或中斷不均）

2.1、查看CPU—top

2.2、查看內(nèi)存—free

free -m

free相關(guān)命令

2.3、查看IO、CPU、內(nèi)存、交換分區(qū)、中斷—vmstat

vmstat -S m 1

從上面可以看出，CPU和I/O的壓力都不算小

輸出結(jié)果說(shuō)明：

對(duì)vmstat 命令的解釋?zhuān)?/span>

1）procs

r這一列顯示了多少進(jìn)程正在等待CPU

b列顯示了多少進(jìn)程正在不可中斷地休眠（通常意味著它們?cè)诘却?/span>I/O，例如磁盤(pán)、網(wǎng)絡(luò)、用戶(hù)輸入、等等）。

2）memory

swpd 虛擬內(nèi)存已使用的大小(顯示多少塊被換出到了磁盤(pán)（頁(yè)面交換）)，如果大于0，表示你的機(jī)器物理內(nèi)存不足了，如果不是程序內(nèi)存泄露的原因，那么你該升級(jí)內(nèi)存了或者把耗內(nèi)存的任務(wù)遷移到其他機(jī)器。

free   空閑的物理內(nèi)存的大小

buff  多少塊正在被用作緩沖  

cache 多少正在被用作操作系統(tǒng)的緩存

3）swap顯示頁(yè)面交換活動(dòng)：每秒有多少塊正在被換入（從磁盤(pán)）和換出（到磁盤(pán)）

si  每秒從磁盤(pán)讀入虛擬內(nèi)存的大小，如果這個(gè)值大于0，表示物理內(nèi)存不夠用或者內(nèi)存泄露了，要查找耗內(nèi)存進(jìn)程解決掉。

so  每秒虛擬內(nèi)存寫(xiě)入磁盤(pán)的大小，如果這個(gè)值大于0，同上。

一般情況下，si、so的值都為0，如果si、so的值長(zhǎng)期不為0，則表示系統(tǒng)內(nèi)存不足，需要考慮是否增加系統(tǒng)內(nèi)存。

4）IO顯示有多少塊從塊設(shè)備讀?。?/span>bi）和寫(xiě)出（bo）

bi  塊設(shè)備每秒接收的塊數(shù)量，這里的塊設(shè)備是指系統(tǒng)上所有的磁盤(pán)和其他塊設(shè)備，默認(rèn)塊大小是1024byte，我本機(jī)上沒(méi)什么IO操作，所以一直是0，但是我曾在處理拷貝大量數(shù)據(jù)(2-3T)的機(jī)器上看過(guò)可以達(dá)到140000/s，磁盤(pán)寫(xiě)入速度差不多140M每秒

bo 塊設(shè)備每秒發(fā)送的塊數(shù)量，例如我們讀取文件，bo就要大于0。bi和bo一般都要接近0，不然就是IO過(guò)于頻繁，需要調(diào)整。

這里設(shè)置的bi+bo參考值為1000，如果超過(guò)1000，而且wa值比較大，則表示系統(tǒng)磁盤(pán)IO性能瓶頸。

5）system顯示了每秒中斷（in）和上下文切換（cs）的數(shù)量

in 每秒CPU的中斷次數(shù)，包括時(shí)間中斷

cs 每秒上下文切換次數(shù)，例如我們調(diào)用系統(tǒng)函數(shù)，就要進(jìn)行上下文切換，線(xiàn)程的切換，也要進(jìn)程上下文切換，這個(gè)值要越小越好，太大了，要考慮調(diào)低線(xiàn)程或者進(jìn)程的數(shù)目。系統(tǒng)調(diào)用也是，每次調(diào)用系統(tǒng)函數(shù)，我們的代碼就會(huì)進(jìn)入內(nèi)核空間，導(dǎo)致上下文切換，這個(gè)是很耗資源，也要盡量避免頻繁調(diào)用系統(tǒng)函數(shù)。上下文切換次數(shù)過(guò)多表示你的CPU大部分浪費(fèi)在上下文切換，導(dǎo)致CPU干正經(jīng)事的時(shí)間少了，CPU沒(méi)有充分利用，是不可取的。

上面這兩個(gè)值越大，會(huì)看到內(nèi)核消耗的CPU時(shí)間就越多。

6）CPU

us 用戶(hù)CPU時(shí)間。us的值比較高時(shí)，說(shuō)明用戶(hù)進(jìn)程消耗的cpu時(shí)間多，但是如果長(zhǎng)期超過(guò)50%的使用，那么我們就該考慮優(yōu)化程序算法或其他措施了

sy 系統(tǒng)CPU時(shí)間，如果太高，表示系統(tǒng)調(diào)用時(shí)間長(zhǎng)，例如是IO操作頻繁。

sys的值過(guò)高時(shí)，說(shuō)明系統(tǒng)內(nèi)核消耗的cpu資源多，這個(gè)不是良性的表現(xiàn)，我們應(yīng)該檢查原因。

id  空閑 CPU時(shí)間，一般來(lái)說(shuō)，id + us + sy = 100,一般我認(rèn)為id是空閑CPU使用率，us是用戶(hù)CPU使用率，sy是系統(tǒng)CPU使用率。

wa 等待IO CPU時(shí)間。

Wa過(guò)高時(shí)，說(shuō)明io等待比較嚴(yán)重，這可能是由于磁盤(pán)大量隨機(jī)訪(fǎng)問(wèn)造成的，也有可能是磁盤(pán)的帶寬出現(xiàn)瓶頸。

st列一般不關(guān)注，虛擬機(jī)占用的時(shí)間百分比

2.4、查看CPU及IO–sar

查看CPU

sar -u 1

輸出項(xiàng)說(shuō)明：

CPU：all 表示統(tǒng)計(jì)信息為所有 CPU 的平均值。

%user：顯示在用戶(hù)級(jí)別(application)運(yùn)行使用 CPU 總時(shí)間的百分比。

%nice：通過(guò)nice改變了進(jìn)程調(diào)度優(yōu)先級(jí)的進(jìn)程，在用戶(hù)模式下消耗的CPU時(shí)間的比例

%system：在核心級(jí)別(kernel)運(yùn)行所使用 CPU 總時(shí)間的百分比。

%iowait：顯示用于等待I/O操作占用 CPU 總時(shí)間的百分比。

%steal：管理程序(hypervisor)為另一個(gè)虛擬進(jìn)程提供服務(wù)而等待虛擬 CPU 的百分比。

%idle：顯示 CPU 空閑時(shí)間占用 CPU 總時(shí)間的百分比。

1. 若 %iowait 的值過(guò)高，表示硬盤(pán)存在I/O瓶頸

2. 若 %idle 的值高但系統(tǒng)響應(yīng)慢時(shí)，有可能是 CPU 等待分配內(nèi)存，此時(shí)應(yīng)加大內(nèi)存容量

3. 若 %idle 的值持續(xù)低于1，則系統(tǒng)的 CPU 處理能力相對(duì)較低，表明系統(tǒng)中最需要解決的資源是 CPU 。

查看IO狀態(tài)

tps:每秒從物理磁盤(pán)I/O的次數(shù).多個(gè)邏輯請(qǐng)求會(huì)被合并為一個(gè)I/O磁盤(pán)請(qǐng)求,一次傳輸?shù)拇笮∈遣淮_定的.

rd_sec/s:每秒讀扇區(qū)的次數(shù).

wr_sec/s:每秒寫(xiě)扇區(qū)的次數(shù).

avgrq-sz:平均每次設(shè)備I/O操作的數(shù)據(jù)大小(扇區(qū)).

avgqu-sz:磁盤(pán)請(qǐng)求隊(duì)列的平均長(zhǎng)度.

await:從請(qǐng)求磁盤(pán)操作到系統(tǒng)完成處理,每次請(qǐng)求的平均消耗時(shí)間,包括請(qǐng)求隊(duì)列等待時(shí)間,單位是毫秒(1秒=1000毫秒).

svctm:系統(tǒng)處理每次請(qǐng)求的平均時(shí)間,不包括在請(qǐng)求隊(duì)列中消耗的時(shí)間.

%util:I/O請(qǐng)求占CPU的百分比,比率越大,說(shuō)明越飽和.

1. avgqu-sz 的值較低時(shí)，設(shè)備的利用率較高。

2. 當(dāng)%util的值接近 1% 時(shí)，表示設(shè)備帶寬已經(jīng)占滿(mǎn)。

2.5、查看中斷情況

mpstat -P ALL -I SUM 1 100

3、查看MySQL在干嘛

3.1、顯示哪些線(xiàn)程正在運(yùn)行

show processlist;或show full processlist

狀態(tài)一、Sending data

mysql> show processlist\G

從以上可以看出是長(zhǎng)時(shí)間的sending data

Sending data:表示從引擎層讀取數(shù)據(jù)返回給Server端的狀態(tài)

長(zhǎng)時(shí)間存在原因：

（1）     沒(méi)適當(dāng)?shù)乃饕?，查?xún)效率低

（2）     讀取大量數(shù)據(jù)，讀取緩慢

（3）     系統(tǒng)負(fù)載高，讀取緩慢

解決方法：

（1）     加上合適的索引

（2）     或者改寫(xiě)SQL，提高效率

（3）     增加LIMIT限制每次讀取數(shù)據(jù)量

（4）     檢查&升級(jí)I/O設(shè)備性能

狀態(tài)二、Waiting for table metadata lock

show processlist;或show full processlist

從以上可以看出：長(zhǎng)時(shí)間等待MDL鎖

原因：

（1）     DDL被阻塞，進(jìn)而阻塞他后續(xù)SQL

（2）     DDL之前的SQL長(zhǎng)時(shí)間未結(jié)束

解決方法：

（1）     提高每個(gè)SQL的效率

（2）     干掉長(zhǎng)時(shí)間運(yùn)行的SQL

（3）     把DDL放在半夜等低谷時(shí)段

（4）     采用pt-osc執(zhí)行DDL

狀態(tài)三、Sleep

從以上可以看出：Sleep

看似無(wú)害，實(shí)則可能是大害蟲(chóng)

（1）     占用連接數(shù)

（2）     消耗內(nèi)存未釋放

（3）     可能有行鎖（甚至是表鎖）未釋放

解決方法：

（1）     適當(dāng)調(diào)低timeout

（2）     主動(dòng)Kill超時(shí)不活躍連接

（3）     定期檢查鎖、鎖等待

（4）     可以利用pt-kill工具

狀態(tài)四：其他狀態(tài)

(1)狀態(tài)：Copy to tmp table

原因：

 1）執(zhí)行alter table 修改表結(jié)構(gòu)，需要生成臨時(shí)表

 2）建議放在夜間低谷執(zhí)行，或者用pt-osc

(2)Copying to tmp table [on disk]

 Creating tmp table

 常見(jiàn)于group by 沒(méi)有索引的情況

 需要拷貝數(shù)據(jù)到臨時(shí)表[內(nèi)存/磁盤(pán)上]

 執(zhí)行計(jì)劃中會(huì)出現(xiàn)Using temporary關(guān)鍵字

 建議創(chuàng)建合適的索引，消除臨時(shí)表

（3) Creating sort index

常見(jiàn)于order by 沒(méi)有索引的情況

需要進(jìn)行filesort排序

執(zhí)行計(jì)劃中會(huì)出現(xiàn)Using filesort關(guān)鍵字

建議創(chuàng)建排序索引

（4）其他狀態(tài)

Waiting for global read lock

Waiting for query cache lock

Waiting for table level lock

Waiting for table metadata lock

3.2、查看鎖– mysql鎖排查過(guò)程

mysql> select * from information_schema.innodb_trx;

mysql> select * from information_schema.innodb_locks;

查看鎖等待

mysql> select * from information_schema.innodb_lock_waits;

mysql> select * from sys.innodb_lock_waits; 

mysql鎖排查過(guò)程

1）查看當(dāng)前鎖等待的情況

INNODB_TRX的鎖情況：

mysql> SELECT  * FROM INNODB_TRX\G;

2）查看鎖等待和持有鎖的相互關(guān)系

mysql> SELECT * FROM INNODB_LOCK_WAITS\G;

3）查看鎖等待的原因

mysql> SELECT * FROM INNODB_LOCKS\G;

3.3、查看Innodb的狀態(tài)

show engine innodb status\G

查看MySQL線(xiàn)程狀態(tài)

3.4、查看慢日志

4、如何預(yù)防

4.1、業(yè)務(wù)上線(xiàn)前

（1）提前消滅垃圾SQL,

（2）在開(kāi)發(fā)或壓測(cè)環(huán)境中

  調(diào)底long_query_time的值，甚至設(shè)為0

  開(kāi)啟log_queries_not_using_indexes

  分析slow query log，并消除潛在隱患SQL

4.2、用更好的設(shè)務(wù)

  （1）CPU更快更多核

  （2）內(nèi)存更快更大

  （3）用更快的I/O設(shè)備

  （4）用更好的網(wǎng)絡(luò)設(shè)備

4.3、磁盤(pán)文件系統(tǒng)及調(diào)度算法

(1)采用xfs/ext4文件系統(tǒng)

（2）采用noop/deading io scheduler