1、常見瓶頸

(1)SQL效率低

(2)選項配置不當

(3)訪問題飆升

(4)硬件性能低

(5)其他進程搶資源

2、怎樣確認是MySQL存在瓶頸

top/free/vmstat/sar/mpstat確認

—確認mysqld進程的CPU消耗占比

—確認mysqld進程的CPU消耗是%user,還是%sys高

—確認是否物理內存不夠用了

—確認是否有swap產生

—確認CPU上是否有大量中斷(或中斷不均)

2.1、查看CPU—top

2.2、查看內存—free

free -m

free相關命令

2.3、查看IO、CPU、內存、交換分區、中斷—vmstat

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列一般不關注，虛擬機占用的時間百分比

2.4、查看CPU及IO–sar

查看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

3、查看MySQL在干嘛

3.1、顯示哪些線程正在運行

show processlist;或show full processlist

狀態一、Sending data

mysql> show processlist\G

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

Sending data:表示從引擎層讀取數據返回給Server端的狀態

長時間存在原因：

(1)沒適當的索引，查詢效率低

(2)讀取大量數據，讀取緩慢

(3)系統負載高，讀取緩慢

解決方法：

(1)加上合適的索引

(2)或者改寫SQL，提高效率

(3)增加LIMIT限制每次讀取數據量

(4)檢查&升級I/O設備性能

狀態二、Waiting for table metadata lock

show processlist;或show full processlist

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

原因：

(1)DDL被阻塞，進而阻塞他后續SQL

(2)DDL之前的SQL長時間未結束

解決方法：

(1)提高每個SQL的效率

(2)干掉長時間運行的SQL

(3)把DDL放在半夜等低谷時段

(4)采用pt-osc執行DDL

狀態三、Sleep

從以上可以看出：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

3.2、查看鎖–?mysql鎖排查過程

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;

3.3、查看Innodb的狀態

show engine innodb status\G

查看MySQL線程狀態

3.4、查看慢日志

4、如何預防

4.1、業務上線前

(1)提前消滅垃圾SQL,

(2)在開發或壓測環境中

調底long_query_time的值，甚至設為0

開啟log_queries_not_using_indexes

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

4.2、用更好的設務

(1)CPU更快更多核

(2)內存更快更大

(3)用更快的I/O設備

(4)用更好的網絡設備

4.3、磁盤文件系統及調度算法

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

(2)采用noop/deading io scheduler

總結

以上是生活随笔為你收集整理的mysql迁移导致数据库效率低_MySQL数据库慢–排查问题总结(整理自《抽丝剥茧之MySQL疑难杂症排查》叶金荣)...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。