MySQL資料庫慢–排查問題總結(整理自《抽絲剝繭之MySQL疑難雜症排查》葉金榮)1、常見瓶頸 2、怎樣确認是MySQL存在瓶頸 3、檢視MySQL在幹嘛 4、如何預防

  （1）SQL效率低

  （2）選項配置不當

  （3）通路題飙升

  （4）硬體性能低

  （5）其他程序搶資源

   top/free/vmstat/sar/mpstat确認

   —确認mysqld程序的CPU消耗占比

   —确認mysqld程序的CPU消耗是%user,還是%sys高

   —确認是否實體記憶體不夠用了

   —确認是否有swap産生

   —确認CPU上是否有大量中斷（或中斷不均）

free -m

free相關指令

vmstat -S m 1

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

輸出結果說明：

對vmstat 指令的解釋：

1）procs

r這一列顯示了多少程序正在等待CPU

b列顯示了多少程序正在不可中斷地休眠（通常意味着它們在等待I/O，例如磁盤、網絡、使用者輸入、等等）。

2）memory

swpd 虛拟記憶體已使用的大小(顯示多少塊被換出到了磁盤（頁面交換）)，如果大于0，表示你的機器實體記憶體不足了，如果不是程式記憶體洩露的原因，那麼你該更新記憶體了或者把耗記憶體的任務遷移到其他機器。

free   空閑的實體記憶體的大小

buff  多少塊正在被用作緩沖  

cache 多少正在被用作作業系統的緩存

3）swap顯示頁面交換活動：每秒有多少塊正在被換入（從磁盤）和換出（到磁盤）

si  每秒從磁盤讀入虛拟記憶體的大小，如果這個值大于0，表示實體記憶體不夠用或者記憶體洩露了，要查找耗記憶體程序解決掉。

so  每秒虛拟記憶體寫入磁盤的大小，如果這個值大于0，同上。

一般情況下，si、so的值都為0，如果si、so的值長期不為0，則表示系統記憶體不足，需要考慮是否增加系統記憶體。

4）IO顯示有多少塊從塊裝置讀取（bi）和寫出（bo）

bi  塊裝置每秒接收的塊數量，這裡的塊裝置是指系統上所有的磁盤和其他塊裝置，預設塊大小是1024byte，我本機上沒什麼IO操作，是以一直是0，但是我曾在處理拷貝大量資料(2-3T)的機器上看過可以達到140000/s，磁盤寫入速度差不多140M每秒

bo 塊裝置每秒發送的塊數量，例如我們讀取檔案，bo就要大于0。bi和bo一般都要接近0，不然就是IO過于頻繁，需要調整。

這裡設定的bi+bo參考值為1000，如果超過1000，而且wa值比較大，則表示系統磁盤IO性能瓶頸。

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

in 每秒CPU的中斷次數，包括時間中斷

cs 每秒上下文切換次數，例如我們調用系統函數，就要進行上下文切換，線程的切換，也要程序上下文切換，這個值要越小越好，太大了，要考慮調低線程或者程序的數目。系統調用也是，每次調用系統函數，我們的代碼就會進入核心空間，導緻上下文切換，這個是很耗資源，也要盡量避免頻繁調用系統函數。上下文切換次數過多表示你的CPU大部分浪費在上下文切換，導緻CPU幹正經事的時間少了，CPU沒有充分利用，是不可取的。

上面這兩個值越大，會看到核心消耗的CPU時間就越多。

6）CPU

us 使用者CPU時間。us的值比較高時，說明使用者程序消耗的cpu時間多，但是如果長期超過50%的使用，那麼我們就該考慮優化程式算法或其他措施了

sy 系統CPU時間，如果太高，表示系統調用時間長，例如是IO操作頻繁。

sys的值過高時，說明系統核心消耗的cpu資源多，這個不是良性的表現，我們應該檢查原因。

id  空閑 CPU時間，一般來說，id + us + sy = 100,一般我認為id是空閑CPU使用率，us是使用者CPU使用率，sy是系統CPU使用率。

wa 等待IO CPU時間。

Wa過高時，說明io等待比較嚴重，這可能是由于磁盤大量随機通路造成的，也有可能是磁盤的帶寬出現瓶頸。

st列一般不關注，虛拟機占用的時間百分比

檢視CPU

sar -u 1

輸出項說明：

CPU：all 表示統計資訊為所有 CPU 的平均值。

%user：顯示在使用者級别(application)運作使用 CPU 總時間的百分比。

%nice：通過nice改變了程序排程優先級的程序，在使用者模式下消耗的CPU時間的比例

%system：在核心級别(kernel)運作所使用 CPU 總時間的百分比。

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

%steal：管理程式(hypervisor)為另一個虛拟程序提供服務而等待虛拟 CPU 的百分比。

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

1. 若 %iowait 的值過高，表示硬碟存在I/O瓶頸

2. 若 %idle 的值高但系統響應慢時，有可能是 CPU 等待配置設定記憶體，此時應加大記憶體容量

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

檢視IO狀态

tps:每秒從實體磁盤I/O的次數.多個邏輯請求會被合并為一個I/O磁盤請求,一次傳輸的大小是不确定的.

rd_sec/s:每秒讀扇區的次數.

wr_sec/s:每秒寫扇區的次數.

avgrq-sz:平均每次裝置I/O操作的資料大小(扇區).

avgqu-sz:磁盤請求隊列的平均長度.

await:從請求磁盤操作到系統完成處理,每次請求的平均消耗時間,包括請求隊列等待時間,機關是毫秒(1秒=1000毫秒).

svctm:系統處理每次請求的平均時間,不包括在請求隊列中消耗的時間.

%util:I/O請求占CPU的百分比,比率越大,說明越飽和.

1. avgqu-sz 的值較低時，裝置的使用率較高。

2. 當%util的值接近 1% 時，表示裝置帶寬已經占滿。

2.5、檢視中斷情況

mpstat -P ALL -I SUM 1 100

show processlist;或show full processlist

mysql> show processlist\G

從以上可以看出是長時間的sending data

Sending data:表示從引擎層讀取資料傳回給Server端的狀态

長時間存在原因：

（1）     沒适當的索引，查詢效率低

（2）     讀取大量資料，讀取緩慢

（3）     系統負載高，讀取緩慢

解決方法：

（1）     加上合适的索引

（2）     或者改寫SQL，提高效率

（3）     增加LIMIT限制每次讀取資料量

（4）     檢查&更新I/O裝置性能

從以上可以看出：長時間等待MDL鎖

原因：

（1）     DDL被阻塞，進而阻塞他後續SQL

（2）     DDL之前的SQL長時間未結束

（1）     提高每個SQL的效率

（2）     幹掉長時間運作的SQL

（3）     把DDL放在半夜等低谷時段

（4）     采用pt-osc執行DDL

從以上可以看出：Sleep

看似無害，實則可能是大害蟲

（1）     占用連接配接數

（2）     消耗記憶體未釋放

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

（1）     适當調低timeout

（2）     主動Kill逾時不活躍連接配接

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

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

(1)狀态：Copy to tmp table

 1）執行alter table 修改表結構，需要生成臨時表

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

(2)Copying to tmp table [on disk]

 Creating tmp table

 常見于group by 沒有索引的情況

 需要拷貝資料到臨時表[記憶體/磁盤上]

 執行計劃中會出現Using temporary關鍵字

 建議建立合适的索引，消除臨時表

（3) Creating sort index

常見于order by 沒有索引的情況

需要進行filesort排序

執行計劃中會出現Using filesort關鍵字

建議建立排序索引

（4）其他狀态

Waiting for global read lock

Waiting for query cache lock

Waiting for table level lock

Waiting for table metadata lock

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鎖排查過程

1）檢視目前鎖等待的情況

INNODB_TRX的鎖情況：

mysql> SELECT  * FROM INNODB_TRX\G;

2）檢視鎖等待和持有鎖的互相關系

mysql> SELECT * FROM INNODB_LOCK_WAITS\G;

3）檢視鎖等待的原因

mysql> SELECT * FROM INNODB_LOCKS\G;

show engine innodb status\G

檢視MySQL線程狀态

（1）提前消滅垃圾SQL,

（2）在開發或壓測環境中

  調底long_query_time的值，甚至設為0

  開啟log_queries_not_using_indexes

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

  （1）CPU更快更多核

  （2）記憶體更快更大

  （3）用更快的I/O裝置

  （4）用更好的網絡裝置

(1)采用xfs/ext4檔案系統

（2）采用noop/deading io scheduler

本文轉自 corasql 51CTO部落格，原文連結：http://blog.51cto.com/corasql/1906155，如需轉載請自行聯系原作者