[[242626]]

MySQL 慢怎么辦

如果遇到 MySQL 慢的話，你的***印象是什么，MySQL 數(shù)據(jù)庫如果性能不行，你是如何處理的？

我咨詢了一些同行, 得到了以下反饋：

• ***反應是再試一次
• 第二個反應是優(yōu)化一下 SQL
• 第三個反應是調大 buffer pool，然后開始換硬件了，換一下 SSD
• ***實在不行了找個搜索引擎搜索一下“MySQL 慢怎么辦”。

如果大家用的是國內的搜索引擎的話，搜索引擎會推薦某某知道或者某某乎, 推薦一些 MySQL 調優(yōu)經(jīng)驗, 調大參數(shù) A, 調低參數(shù) B, 諸如此類，類似的網(wǎng)站能告訴你 MySQL 慢怎么辦。

我們來分析一下這些現(xiàn)象背后隱藏的意義：

• 如果再試一次能夠成功的話, 意味著你可能碰到了不可復現(xiàn)的外界因素的影響，導致 MySQL 會慢。
• 如果優(yōu)化 SQL 能解決，就意味著 SQL 的執(zhí)行復雜度遠遠大于它的需求復雜度。
• 如果調大 buffer pool 能解決，就意味著 MySQL 碰到了自身的某些限制。
• 如果換 SSD 能解決，那么意味著服務器資源受到了一定的限制。
• 如果需要搜索引擎，意味著調優(yōu)這事已經(jīng)變成了玄學。

本文向大家分享我對 MySQL 慢的診斷思路，以及向大家介紹系統(tǒng)觀測工具。

MySQL 慢的診斷思路

MySQL 慢的診斷思路，一般會從三個方向來做：

1. MySQL 內部的觀測
2. 外部資源的觀測
3. 外部需求的改造

下面依次看一下這幾個思路。

MySQL 內部觀測

常用的 MySQL 內部觀測手段是這樣的：

• ***步是 Processlist，看一下哪個 SQL 壓力不太正常；
• 第二步是 explain，解釋一下它的執(zhí)行計劃；
• 第三步要做 Profilling，如果這個 SQL 能再執(zhí)行一次的話, 就做一個 Profilling；
• 高級的 DBA 會直接動用 performance_schema ，MySQL 5.7 以后直接動用 sys_schema，sys_schema 是一個視圖，里面有便捷的各類信息，幫助大家來診斷性能；
• 再高級一點，會動用 innodb_metrics 進行一個對引擎的診斷。

除了這些手段以外，還有一些亂七八糟的手段就不列在這了，這些是常規(guī)的 MySQL 內部狀態(tài)觀測的思路。

外部資源觀測

這里引用國外一個大神寫的文章，標題是《60 秒的快速巡檢》（參考鏈接在文末）。我們來看一下它在 60 秒之內對服務器到底做了一個什么樣的巡檢。一共十條命令，下面一條一條來看一下。

1. uptime，uptime 告訴我們這個機器活了多久，以及它的平均負載是多少。
2. dmesg -T | tail，告訴我們系統(tǒng)日志里邊有沒有什么報錯。
3. vmstat 1，告訴我們虛擬內存的狀態(tài)，頁的換進換出有沒有問題，swap 有沒有使用。
4. mpstat -P ALL 1，告訴我們 CPU 壓力在各個核上是不是均勻的。
5. pidstat 1，告訴我們各個進程的對資源的占用大概是什么樣子。
6. iostat-xz 1，查看 IO 的問題。
7. free-m 內存使用率；
8. sar-n DVE 1，
9. sar-n TCP, ETCP 1，8 和 9 兩條按設備網(wǎng)卡設備的維度，看一下網(wǎng)絡的消耗狀態(tài)，以及總體看 TCP 的使用率和錯誤率是多少。
10. top，看一下大概的進程和線程的問題。

這個就是對于外部資源的診斷，這十條命令揭示了應該去診斷哪些外部資源。

外部需求改造

第三個診斷思路是外部的需求改造，在這里引用了 MySQL 官方文檔中的一章，《Examples of Common Queries 》（ https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.5/en/examples.html），文檔中介紹了常規(guī)的 SQL 怎么寫, 給出了一些例子。

下面看一下它其中提到的一個例子。

這張表有三列，article、dealer、price。它做的事情是從這個表里選取每個作者最貴的商品列在結果集中，這是它最原始的 SQL，非常符合業(yè)務的寫法，但是它是個關聯(lián)子查詢。

關聯(lián)子查詢成本是很貴的，所以上面的文檔會教你快速地把它轉成一個非關聯(lián)子查詢，大家可以看到中間的子查詢和外邊的查詢之間是沒有關聯(lián)性的。

第三步，會教大家直接把子查詢拿掉，然后轉成這樣一個 SQL，這個就叫業(yè)務改造，前后三個 SQL 的成本都不一樣，把關聯(lián)子查詢拆掉的成本，拆掉以后 SQL 會跑得非常好，但這個 SQL 已經(jīng)不能良好表義了，只有在診斷到 SQL 成本比較高的情況下才建議大家使用這種方式。

為什么它能夠把一個關聯(lián)子查詢拆掉？

這背后的原理是關系代數(shù)，所有的 SQL 都可以被表達成等價的關系代數(shù)式，關系代數(shù)式之間有等價關系，這個等價關系通過變換可以把關聯(lián)子查詢拆掉。

總結一下，對于 MySQL 慢的診斷思路如下：

***，MySQL 本身提供了很多命令來觀察 MySQL 自身的各類狀態(tài)，從上往下檢一般能檢到 SQL 的問題或者服務器的問題。

第二，從服務器的角度，我們從巡檢的腳本角度入手，服務器的資源就這幾種，觀測手法也就那么幾種，把服務器的資源全部都觀察一圈就可以了。

第三，如果實在搞不定，需求方一定要按照數(shù)據(jù)庫容易接受的方式去寫 SQL，這個成本會下降的非常快，這個是常規(guī)的 MySQL 慢的診斷思路。

下面重點介紹為大家介紹系統(tǒng)觀測工具。

系統(tǒng)觀測工具介紹

先從診斷思路的討論切換到系統(tǒng)的觀測工具，首先了解什么叫系統(tǒng)觀測工具并且看一下它的舉例，然后再回到診斷思路上，看看新的工具的引入能為我們的思路到底帶來怎樣的改變。

什么叫系統(tǒng)觀測工具

這里也參考了一篇外國人寫的文檔：

https://jvns.ca/blog/2017/07/05/linux-tracing-systems/

把這個文檔拆開，中間描述了三件事情：

系統(tǒng)觀測工具的數(shù)據(jù)源來自于哪里；

數(shù)據(jù)采集過程，因為采集的是系統(tǒng)的運行狀況，所以到底如何采集這是一個難點；

應該怎么看數(shù)據(jù)，是用圖來看，還是用表來看，它就叫數(shù)據(jù)處理前端。

***步，我們來看一下數(shù)據(jù)源，Linux 給我們提供的數(shù)據(jù)源包括操作系統(tǒng)內核態(tài)提供的觀測點和用戶態(tài)提供的觀測點，MySQL 很早之前就提供了用戶態(tài)的觀測點。

第二步，如何把數(shù)據(jù)抽出來。以下這些工具中大家最熟悉的應該是 perf 和 ftrace，sysdig 也有人在用，其它的可能有所耳聞，這是從操作系統(tǒng)里抽取數(shù)據(jù)的方法。

第三步，數(shù)據(jù)處理前端，前端常用的也是 perf 和 ftrace。如果大家對 perf 很熟悉的話會知道 perf 出來的數(shù)據(jù)是一個樹形的數(shù)據(jù)，并可以跟這棵樹進行交互，比如說: 查看某個函數(shù)運行了多久，哪一個函數(shù)的時間最長，這個是數(shù)據(jù)處理前端。

我們來對比一下常規(guī)的四類系統(tǒng)觀測工具：ftrace, perf_events，eBPF 和 Systemtap，這四個工具到底有什么不同，看看 Linux 為什么提供這么多觀測工具。

ftrace：ftrace 是 sysfs 中的一個樁，通過這個樁內核提供了一種觀測的形式——把想觀測的函數(shù)簽名打到這個樁里，然后操作系統(tǒng)就會提供這個函數(shù)運行的狀況。ftrace 的結構如左圖, 數(shù)據(jù)處理前端和采集端是 ftrace, 數(shù)據(jù)源是下面這一堆。

perf：常用的 perf 的原理是操作系統(tǒng)提供了一個系統(tǒng)調用可以將數(shù)據(jù)寫到一個緩存中, 然后客戶端把這些數(shù)據(jù)端抽取出來然后呈現(xiàn)在顯示器上。

eBPF：這是本文想重點推薦的。以上兩種方案一種是操作系統(tǒng)提供的文件系統(tǒng)上的樁，一種是操作系統(tǒng)提供的系統(tǒng)調用，而 eBPF 是將一段代碼直接插到操作系統(tǒng)內核某一個位置上的機制。

Systemtap：它的原理是將一段 C 的代碼編譯成一個內核模塊，然后將這個模塊嵌到內核里邊去，它不是由內核提供的一個機制，而是由內核的模塊機制提供的一種功能。

介紹了這四種觀測工具的不同，大家在選取觀測工具的時候就知道應該怎么選。

這四種觀測工具對系統(tǒng)傷害最輕的是誰？

對系統(tǒng)傷害最輕的是系統(tǒng)調用，這是系統(tǒng)承諾出來的服務。然后是 ftrace，這是系統(tǒng)在文件系統(tǒng)層面提供的一個口，告訴你可以通過這個口跟系統(tǒng)交互。

對系統(tǒng)侵入性***的是誰？

對系統(tǒng)侵入性***的應該是 eBPF，因為它直接將一根代碼嵌入到系統(tǒng)里邊去做，最不穩(wěn)定的應該是 System Tap，因為它是系統(tǒng)的一個模塊, 又提供了非常復雜的功能。

上圖是 eBPF 的架構圖，eBPF 先將一段程序編譯成二進制代碼，然后插入到操作系統(tǒng)里，操作系統(tǒng)運行這段代碼的時候，將采集到的數(shù)據(jù)吐到操作系統(tǒng)本身的空間里，然后再做統(tǒng)一返回。

eBPF 結構最核心的部分在于把代碼插入到操作系統(tǒng)中運行，它需要做各種安全保護才能完成這一點，所以這也是這個機制復雜的地方。

下面引用一個開源的 eBPF 腳本集 bcc, 快速看一下 eBPF 能做什么, 這些功能都是開箱即用的。

bcc (eBPF 腳本集) 使用舉例

MySQL 的請求延遲分析：

一個 MySQL 承擔了很多業(yè)務，上千個并發(fā)˙中，哪一個 SQL 最慢，到底有哪些 SQL 在一秒以上，除了 slow log 以外，還可以用這種方法來看。

這個命令的結果分為三列，它的***列是請求的延遲，指數(shù)級遞增，單位是微秒，中間一列是它的***數(shù)，如果有一個請求***了 64-127 微秒這個區(qū)間，***數(shù)會加一，***一列是它的分布圖，它在同一個報告里提供了數(shù)值的方式和圖的方式，可以很容易看到結果。

對于這臺服務器來說，我下了一個 select 的性能壓力，它大部分的請求集中于 64 到 127 微秒之間。這個數(shù)據(jù)庫的性能可能還不錯。

再來看另外一種壓力，我下了一個 select+insert 的混合壓力在一個數(shù)據(jù)庫里，它的圖又變了，它呈現(xiàn)了一個非常好的雙峰圖，我將兩個峰值用另外一種顏色標明，這兩個峰值的意思是很有可能有混合壓力在一個數(shù)據(jù)庫里，或者是上面的這部分壓力是***了某些緩存，而下面的某些壓力是由于沒有***緩存，導致這部分請求更慢一些, 形成另一個峰值，所以通過這種峰值分析可以看到數(shù)據(jù)庫大概的一個運行狀態(tài)。

如果能做得更好，你可以抽檢自己的數(shù)據(jù)庫然后做環(huán)比圖，比如今天和昨天同樣的時間，同樣的業(yè)務壓力下對數(shù)據(jù)庫的延遲進行分析，如果數(shù)據(jù)庫的延遲峰一直在往后延，就意味著數(shù)據(jù)庫的狀態(tài)在變得更糟糕一些。這是 bcc ***個能做的事情，需要再次強調的是它開箱即用直接下載過來就可以使用。

MySQL 的慢查詢：

MySQL 本身提供很好的慢查詢，為什么還要用另外一個機制來獲取 MySQL 的慢查詢呢？

MySQL 的慢查詢可能很難做，與 MySQL 的慢日志相比, 它可以低成本地完成:

• 獲取少量慢查詢
• 獲取某種模式的慢查詢
• 獲取某個用戶的慢查詢

比如說獲取少量的慢查詢，為什么是少量呢？因為不確定現(xiàn)在的線上延遲是多少，慢查詢只開一秒可能日志瞬間就被堆上去，性能就會下來，但是如果慢查詢開個十秒左右，沒有請求在這個區(qū)間***，所以要一點一點的去調這個值，比如說線上 1% 的最慢的查詢能夠***，但是在這個腳本里面，可以取一定區(qū)間的***的幾個查詢把它拎出來。

通過腳本還可以***某種模式的慢查詢, 比如說我們只關心 update 的慢查詢, 那么獲取 select 的結果就沒有太大的意義，或者是我一定要獲取某一些特定表的相關的查詢，我都可以通過腳本來做。

第三種情況，想獲取某個用戶的慢查詢，這個一般對于多租戶系統(tǒng)，因為多租戶系統(tǒng)只想針對某一個用戶進行慢查詢分析的時候，這種腳本就比較好用。

VFS 延遲分析：

對 VFS 做延遲分析，這是對數(shù)據(jù)庫進行了一個寫壓力，可以明顯看到一個雙峰圖，這是寫的兩個峰，是數(shù)據(jù)庫對于內核的寫壓力的反饋。

這個意味著什么呢？這個可能意味著因為這部分的寫是***了操作系統(tǒng)文件系統(tǒng)的緩存，而下面這部分寫是真正的寫穿到設備的，所以他們倆的延遲不一樣，這是一個典型的雙峰圖，大家需要把兩個峰拆開來去行這樣的分析。

換一個說法，如果寫壓力都集中在這里，而沒有第二個峰的情況下，需不需要去更換物理設備？有可能不需要，因為所有的東西都***了操作系統(tǒng)的緩存。

短生命周期的臨時文件檢測：

這個不一定常見，MySQL 會在某些情況下動用臨時表, 如果 SQL 沒寫好就會創(chuàng)建臨時表，這些臨時表的生命周期很短，但是量很大，所以一定要寫文件而不能內存里。

在這種情況下會對操作系統(tǒng)造成一些壓力，而這個壓力又不太好診斷，因為臨時文件的生存周期短，所以這個腳本可以幫大家提供一個方案，這個方案的結果是這樣子。

我做了一個臨時表，這個臨時表活了 5.3 秒左右，于是它展現(xiàn)在了腳本的結果里。如果掃描自己的線上 MySQL 發(fā)現(xiàn)這里有大量的東西說明在大量的使用臨時表，如果 IO 壓力在此時比較大, 就可能受了臨時表的影響。

短連接分析：

好一點的應用都會用連接池，但是我們很多的時候沒有那么好的運氣，老碰到那么好的應用，所以經(jīng)常業(yè)務會扔過來大量的短連接。

這個例子中, sysbench 上了一個大并發(fā)，但是只活了 300 多毫秒，這些連接都只活了 300 多毫秒，反復運行這個 sysbench 就可以將數(shù)據(jù)庫打死，建立一千個連接，300 毫秒以后也會銷毀，再建立一千個連接，你的業(yè)務就會忽上忽下，通過這個腳本就可以抓到這個壓力從哪個服務器來的，哪個端口來的，然后把它搞定就可以了。

長連接分析：

除了短連接分析，還有長連接分析，哪一個業(yè)務端老在搞我的數(shù)據(jù)，老在往里寫，總在往里讀，搞的網(wǎng)絡特別慢。

可以幫大家提供這樣一個視角，它有讀有寫。

以上幾個 bcc 相關的例子都是現(xiàn)成的腳本。bcc 可以觀測操作系統(tǒng)的各個方面，比如說如果有東西被 OOM kill 掉了，內存有泄露的也可以看，它基本上是我們這幾年發(fā)現(xiàn)的一個寶庫，大家直接調用這些腳本就可以完成很多的別人完成不了的分析，它的技術用的是 eBPF，直接在 github 上直接搜就行了。

eBPF 使用方法 / 限制

如果這里邊腳本滿足不了要求, 那可以自己寫。這里介紹一下腳本的寫法以及 eBPF 的限制。

拿上面提到過的 MySQL 延遲分析舉例，一個 MySQL 上面有一千個 query，這些 query 大概都落在哪個延遲時間里面那張圖，為了完成這個需求, 我需要寫兩段程序，其中***段程序是運行在內核里邊的程序。

這段程序的邏輯是這樣的：

• 在 query 開始的時候截獲一下，讓它記錄一個時間戳；
• 請求結束的時候再截獲一下記錄一個時間戳；
• 把兩個時間戳相減獲得一個延遲；
• 把這個延遲扔到結果集里邊去，程序就完成了。

我用結束時間減開始時間，減一下得到一個延遲，然后把延遲扔到一個統(tǒng)計容器里面，這個事就結束了。這是我要寫的***個程序，是嵌到內核里的程序，但是需要一個外殼的程序負責嵌入。

這個外殼程序的邏輯也非常簡單，把剛才那段內核的程序嵌到 MySQL 的觀測點上，嵌到內核里面去，然后把結果集拿出來，打印出來就結束了，這是如何寫一個 eBPF 的腳本，大家唯一需要做的事情就是這兩個程序，然后運行一下。

這個程序的核心只有 45 行，中間忽略了負責差錯處理的一部分。只需要把現(xiàn)在的腳本拿下來抄一抄，改一改就可以完成很多的功能了。

這么好的方法為什么很多人不知道呢？

• 操作系統(tǒng)內核的限制，這個功能是 Linux 4.4 引進來的，但是在 Linux 4.4 上存在統(tǒng)計的 bug，我們推薦的是 Linux 4.9+，部分好用的功能是在 4.13+ 上才開放，這個是 eBPF ***的限制。怎么辦呢？只能祝大家長壽吧！活到 Linux 4.x 內核能在生產(chǎn)環(huán)境上使用的那一天。
• 它的第二個***的限制是 MySQL 的編譯參數(shù)，MySQL 雖然在很早的時候已經(jīng)提供了 dtrace 的觀測點，這些觀測點是公用的，但是它在默認的編譯出來的官方發(fā)布的包里邊是不帶觀測點編譯的，所以在直接官方發(fā)布的二進制的包里邊是用不了這個功能的，大家需要自己編譯一下。編譯的時候需要帶這個參數(shù)，這個可能也是屬于一個比較大的限制。

所以如果大家受到限制，再推薦換一個工具：systemtap。

Linux 2.6 就已經(jīng)有了，但是它的機制是寫一個內核模塊，這種機制其實不是特別穩(wěn)定，它為了解決不是特別穩(wěn)定的問題增加了若干限制，比如說能在內核中使用的內存大小有限制，采集頻率也有限制，對整個內核性能的影響百分比也有限制，在這些限制參數(shù)都開起來的情況下，它還是比較安全的。

但是很多觀測功能就必須要把這些限制關掉，一旦關掉內核就不是很穩(wěn)定，所以這個工具，我沒有敢把它的缺點寫在上面因為確實是個好的工具，我們也很難說它的這個缺點是個致命的缺陷，但是不太推薦在生產(chǎn)環(huán)境上使用，但是在測試環(huán)境上確實是非常好玩的一個工具，如果大家用不了 eBPF 的話可以用 systemtap 來做一些診斷。

還有很多其他的工具：

至于如何選擇，大家直接谷歌一下有專門的文章教大家怎么選擇這些觀測工具。但是總的來說沒有一個科學的思路，只有嘗試，不停的嘗試。

黃炎，愛可生研發(fā)總監(jiān)，深入鉆研分布式數(shù)據(jù)庫相關技術，擅長業(yè)界相關 MySQL 中間件產(chǎn)品和開發(fā)，以及分布式中間件在企業(yè)內部的應用實踐。