實例解析MySQL性能瓶頸排查定位

導讀

從一個現場說起，全程解析如何定位性能瓶頸。

排查過程

收到線上某業務后端的MySQL實例負載比較高的告警信息，于是登入服務器檢查確認。

1. 首先我們進行OS層面的檢查確認

登入服務器后，我們的目的是首先要確認當前到底是哪些進程引起的負載高，以及這些進程卡在什么地方，瓶頸是什么。

通常來說， 服務器上最容易成為瓶頸的是磁盤I/O子系統 ，因為它的讀寫速度通常是最慢的。即便是現在的PCIe SSD，其隨機I/O讀寫速度也是不如內存來得快。當然了，引起磁盤I/O慢得原因也有多種，需要確認哪種引起的。

第一步，我們一般先看整體負載如何，負載高的話，肯定所有的進程跑起來都慢。

可以執行指令 w 或者 sar -q 1 來查看負載數據，例如：

[yejr@imysql.com:~ ]# w
 11:52:58 up 702 days, 56 min,  1 user,  load average: 7.20, 6.70, 6.47
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT
root     pts/0    1.xx.xx.xx        11:51    0.00s  0.03s  0.00s w

或者 sar -q 的觀察結果：

[yejr@imysql.com:~ ]# sar -q 1
Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com)     01/13/2016     _x86_64_    (24 CPU)
02:51:18 PM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15   blocked
02:51:19 PM         4      2305      6.41      6.98      7.12         3
02:51:20 PM         2      2301      6.41      6.98      7.12         4
02:51:21 PM         0      2300      6.41      6.98      7.12         5
02:51:22 PM         6      2301      6.41      6.98      7.12         8
02:51:23 PM         2      2290      6.41      6.98      7.12         8

load average大意表示當前CPU中有多少任務在排隊等待，等待越多說明負載越高，跑數據庫的服務器上，一般load值超過5的話，已經算是比較高的了。

引起load高的原因也可能有多種：

• 某些進程/服務消耗更多CPU資源(服務響應更多請求或存在某些應用瓶頸)；

• 發生比較嚴重的swap(可用物理內存不足)；

• 發生比較嚴重的中斷(因為SSD或網絡的原因發生中斷)；

• 磁盤I/O比較慢(會導致CPU一直等待磁盤I/O請求)；

這時我們可以執行下面的命令來判斷到底瓶頸在哪個子系統：

[yejr@imysql.com:~ ]# top
top - 11:53:04 up 702 days, 56 min,  1 user,  load average: 7.18, 6.70, 6.47
Tasks: 576 total,   1 running, 575 sleeping,   0 stopped,   0 zombie
Cpu(s):  7.7%us,  3.4%sy,  0.0%ni, 77.6%id, 11.0%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:  49374024k total, 32018844k used, 17355180k free,   115416k buffers
Swap: 16777208k total,   117612k used, 16659596k free,  5689020k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14165 mysql     20   0 8822m 3.1g 4672 S 162.3  6.6  89839:59 mysqld
40610 mysql     20   0 25.6g  14g 8336 S 121.7 31.5 282809:08 mysqld
49023 mysql     20   0 16.9g 5.1g 4772 S  4.6 10.8   34940:09 mysqld

很明顯是前面兩個mysqld進程導致整體負載較高。

而且，從 Cpu(s) 這行的統計結果也能看的出來， %us 和 %wa 的值較高，表示 當前比較大的瓶頸可能是在用戶進程消耗的CPU以及磁盤I/O等待上 。

我們先分析下磁盤I/O的情況。

執行 sar -d 確認磁盤I/O是否真的較大：

[yejr@imysql.com:~ ]# sar -d 1
Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com)     01/13/2016     _x86_64_    (24 CPU)
11:54:32 AM    dev8-0   5338.00 162784.00   1394.00     30.76      5.24      0.98      0.19    100.00
11:54:33 AM    dev8-0   5134.00 148032.00  32365.00     35.14      6.93      1.34      0.19    100.10
11:54:34 AM    dev8-0   5233.00 161376.00    996.00     31.03      9.77      1.88      0.19    100.00
11:54:35 AM    dev8-0   4566.00 139232.00   1166.00     30.75      5.37      1.18      0.22    100.00
11:54:36 AM    dev8-0   4665.00 145920.00    630.00     31.41      5.94      1.27      0.21    100.00
11:54:37 AM    dev8-0   4994.00 156544.00    546.00     31.46      7.07      1.42      0.20    100.00

再利用 iotop 確認到底哪些進程消耗的磁盤I/O資源最多：

[yejr@imysql.com:~ ]# iotop
Total DISK READ: 60.38 M/s | Total DISK WRITE: 640.34 K/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
16397 be/4 mysql       8.92 M/s    0.00 B/s  0.00 % 94.77 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
 7295 be/4 mysql      10.98 M/s    0.00 B/s  0.00 % 93.59 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
14295 be/4 mysql      10.50 M/s    0.00 B/s  0.00 % 93.57 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
14288 be/4 mysql      14.30 M/s    0.00 B/s  0.00 % 91.86 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
14292 be/4 mysql      14.37 M/s    0.00 B/s  0.00 % 91.23 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320

可以看到，端口號是3320的實例消耗的磁盤I/O資源比較多，那就看看這個實例里都有什么查詢在跑吧。

2. MySQL層面檢查確認

首先看下當前都有哪些查詢在運行：

[yejr@imysql.com(db)]> mysqladmin pr|grep -v Sleep
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| Id |User| Host     | db |Command|Time | State        | Info                                                                                          |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| 25 | x | 10.x:8519 | db | Query | 68  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404612 order by Fvideoid) t1 |
| 26 | x | 10.x:8520 | db | Query | 65  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>484915 order by Fvideoid) t1 |
| 28 | x | 10.x:8522 | db | Query | 130 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404641 order by Fvideoid) t1 |
| 27 | x | 10.x:8521 | db | Query | 167 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324157 order by Fvideoid) t1 |
| 36 | x | 10.x:8727 | db | Query | 174 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324346 order by Fvideoid) t1 |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+

可以看到有不少慢查詢還未完成，從slow query log中也能發現，這類SQL發生的頻率很高。

這是一個非常低效的SQL寫法，導致需要對整個主鍵進行掃描，但實際上只需要取得一個最大值而已，從slow query log中可看到：

Rows_sent: 1  Rows_examined: 5502460

每次都要掃描500多萬行數據，卻只為讀取一個最大值，效率非常低。

經過分析，這個SQL稍做簡單改造即可在個位數毫秒級內完成，原先則是需要150-180秒才能完成，提升了N次方。

改造的方法是： 對查詢結果做一次倒序排序，取得第一條記錄即可 。而原先的做法是對結果正序排序，取最后一條記錄，汗啊。。。

寫在最后，小結

在這個例子中，產生瓶頸的原因比較好定位，SQL優化也不難，實際線上環境中，通常有以下幾種常見的原因導致負載較高：

• 一次請求讀寫的數據量太大，導致磁盤I/O讀寫值較大，例如一個SQL里要讀取或更新幾萬行數據甚至更多，這種最好是想辦法減少一次讀寫的數據量；

• SQL查詢中沒有適當的索引可以用來完成條件過濾、排序(ORDER BY)、分組(GROUP BY)、數據聚合(MIN/MAX/COUNT/AVG等)，添加索引或者進行SQL改寫吧；

• 瞬間突發有大量請求，這種一般只要能扛過峰值就好，保險起見還是要適當提高服務器的配置，萬一峰值抗不過去就可能發生雪崩效應；

• 因為某些定時任務引起的負載升高，比如做數據統計分析和備份，這種對CPU、內存、磁盤I/O消耗都很大，最好放在獨立的slave服務器上執行；

• 服務器自身的節能策略發現負載較低時會讓CPU降頻，當發現負載升高時再自動升頻，但通常不是那么及時，結果導致CPU性能不足，抗不過突發的請求；

• 使用raid卡的時候，通常配備BBU(cache模塊的備用電池)，早期一般采用鋰電池技術，需要定期充放電(DELL服務器90天一次，IBM是30天)，我們可以通過監控在下一次充放電的時間前在業務低谷時提前對其進行放電，不過新一代服務器大多采用電容式電池，也就不存在這個問題了。

• 文件系統采用ext4甚至ext3，而不是xfs，在高I/O壓力時，很可能導致%util已經跑到100%了，但iops卻無法再提升，換成xfs一般可獲得大幅提升；

• 內核的io scheduler策略采用cfq而非deadline或noop，可以在線直接調整，也可獲得大幅提升。

關于MySQL的方方面面大家想了解什么，可以直接留言回復，我會從中選擇一些熱門話題進行分享。 同時希望大家多多 轉發 ，多一些閱讀量是老葉繼續努力分享的絕佳助力，謝謝大家