[[242626]]

MySQL 慢怎么辦

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

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

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

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

我們來分析一下這些現象背后隱藏的意義：

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

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

MySQL 慢的診斷思路

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

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

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

MySQL 內部觀測

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

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

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

外部資源觀測

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

1. uptime，uptime 告訴我們這個機器活了多久，以及它的平均負載是多少。
2. dmesg -T | tail，告訴我們系統日志里邊有沒有什么報錯。
3. vmstat 1，告訴我們虛擬內存的狀態，頁的換進換出有沒有問題，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 兩條按設備網卡設備的維度，看一下網絡的消耗狀態，以及總體看 TCP 的使用率和錯誤率是多少。
10. top，看一下大概的進程和線程的問題。

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

外部需求改造

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

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

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

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

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

為什么它能夠把一個關聯子查詢拆掉？

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

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

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

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

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

下面重點介紹為大家介紹系統觀測工具。

系統觀測工具介紹

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

什么叫系統觀測工具

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

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

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

系統觀測工具的數據源來自于哪里；

數據采集過程，因為采集的是系統的運行狀況，所以到底如何采集這是一個難點；

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

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

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

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

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

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

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

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

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

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

這四種觀測工具對系統傷害最輕的是誰？

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

對系統侵入性***的是誰？

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

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

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

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

bcc (eBPF 腳本集) 使用舉例

MySQL 的請求延遲分析：

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

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

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

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

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

MySQL 的慢查詢：

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

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

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

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

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

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

VFS 延遲分析：

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

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

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

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

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

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

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

短連接分析：

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

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

長連接分析：

除了短連接分析，還有長連接分析，哪一個業務端老在搞我的數據，老在往里寫，總在往里讀，搞的網絡特別慢。

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

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

eBPF 使用方法 / 限制

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

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

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

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

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

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

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

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

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

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

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

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

還有很多其他的工具：

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

黃炎，愛可生研發總監，深入鉆研分布式數據庫相關技術，擅長業界相關 MySQL 中間件產品和開發，以及分布式中間件在企業內部的應用實踐。