MySQL性能瓶頸排查定位實(shí)例詳解

更新時(shí)間：2016年04月26日 11:31:28 作者：OurMySQL

這篇文章主要介紹了MySQL性能瓶頸排查定位的方法,結(jié)合實(shí)例形式詳細(xì)分析了MySQL排查性能瓶頸問題的步驟與相關(guān)技巧,需要的朋友可以參考下

本文實(shí)例講述了MySQL性能瓶頸排查定位的方法。分享給大家供大家參考，具體如下：

導(dǎo)讀

從一個(gè)現(xiàn)場說起，全程解析如何定位性能瓶頸。

排查過程

收到線上某業(yè)務(wù)后端的MySQL實(shí)例負(fù)載比較高的告警信息，于是登入服務(wù)器檢查確認(rèn)。

1. 首先我們進(jìn)行OS層面的檢查確認(rèn)

登入服務(wù)器后，我們的目的是首先要確認(rèn)當(dāng)前到底是哪些進(jìn)程引起的負(fù)載高，以及這些進(jìn)程卡在什么地方，瓶頸是什么。

通常來說，服務(wù)器上最容易成為瓶頸的是磁盤I/O子系統(tǒng)，因?yàn)樗淖x寫速度通常是最慢的。即便是現(xiàn)在的PCIe SSD，其隨機(jī)I/O讀寫速度也是不如內(nèi)存來得快。當(dāng)然了，引起磁盤I/O慢得原因也有多種，需要確認(rèn)哪種引起的。

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

可以執(zhí)行指令 w 或者 sar -q 1 來查看負(fù)載數(shù)據(jù)，例如：

[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 的觀察結(jié)果：

[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大意表示當(dāng)前CPU中有多少任務(wù)在排隊(duì)等待，等待越多說明負(fù)載越高，跑數(shù)據(jù)庫的服務(wù)器上，一般load值超過5的話，已經(jīng)算是比較高的了。

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

某些進(jìn)程/服務(wù)消耗更多CPU資源(服務(wù)響應(yīng)更多請求或存在某些應(yīng)用瓶頸)；

發(fā)生比較嚴(yán)重的swap(可用物理內(nèi)存不足)；

發(fā)生比較嚴(yán)重的中斷(因?yàn)镾SD或網(wǎng)絡(luò)的原因發(fā)生中斷)；

磁盤I/O比較慢(會(huì)導(dǎo)致CPU一直等待磁盤I/O請求)；

這時(shí)我們可以執(zhí)行下面的命令來判斷到底瓶頸在哪個(gè)子系統(tǒng)：

[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

很明顯是前面兩個(gè)mysqld進(jìn)程導(dǎo)致整體負(fù)載較高。

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

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

執(zhí)行 sar -d 確認(rèn)磁盤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 確認(rèn)到底哪些進(jìn)程消耗的磁盤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的實(shí)例消耗的磁盤I/O資源比較多，那就看看這個(gè)實(shí)例里都有什么查詢在跑吧。

2. MySQL層面檢查確認(rèn)

首先看下當(dāng)前都有哪些查詢在運(yùn)行：

[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中也能發(fā)現(xiàn)，這類SQL發(fā)生的頻率很高。

這是一個(gè)非常低效的SQL寫法，導(dǎo)致需要對整個(gè)主鍵進(jìn)行掃描，但實(shí)際上只需要取得一個(gè)最大值而已，從slow query log中可看到：

Rows_sent: 1 Rows_examined: 5502460
每次都要掃描500多萬行數(shù)據(jù)，卻只為讀取一個(gè)最大值，效率非常低。

經(jīng)過分析，這個(gè)SQL稍做簡單改造即可在個(gè)位數(shù)毫秒級內(nèi)完成，原先則是需要150-180秒才能完成，提升了N次方。

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

寫在最后，小結(jié)

在這個(gè)例子中，產(chǎn)生瓶頸的原因比較好定位，SQL優(yōu)化也不難，實(shí)際線上環(huán)境中，通常有以下幾種常見的原因?qū)е仑?fù)載較高：

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

SQL查詢中沒有適當(dāng)?shù)乃饕梢杂脕硗瓿蓷l件過濾、排序(ORDER BY)、分組(GROUP BY)、數(shù)據(jù)聚合(MIN/MAX/COUNT/AVG等)，添加索引或者進(jìn)行SQL改寫吧；

瞬間突發(fā)有大量請求，這種一般只要能扛過峰值就好，保險(xiǎn)起見還是要適當(dāng)提高服務(wù)器的配置，萬一峰值抗不過去就可能發(fā)生雪崩效應(yīng)；

因?yàn)槟承┒〞r(shí)任務(wù)引起的負(fù)載升高，比如做數(shù)據(jù)統(tǒng)計(jì)分析和備份，這種對CPU、內(nèi)存、磁盤I/O消耗都很大，最好放在獨(dú)立的slave服務(wù)器上執(zhí)行；

服務(wù)器自身的節(jié)能策略發(fā)現(xiàn)負(fù)載較低時(shí)會(huì)讓CPU降頻，當(dāng)發(fā)現(xiàn)負(fù)載升高時(shí)再自動(dòng)升頻，但通常不是那么及時(shí)，結(jié)果導(dǎo)致CPU性能不足，抗不過突發(fā)的請求；

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

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

內(nèi)核的io scheduler策略采用cfq而非deadline或noop，可以在線直接調(diào)整，也可獲得大幅提升。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過程技巧大全》、《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》

希望本文所述對大家MySQL數(shù)據(jù)庫計(jì)有所幫助。

您可能感興趣的文章: