討論 - 辨識 MySQL Slow Queries 的一般性原則

[Admin1]

本篇文章是由 Daniel Nichter 的 "Non-technical Guide to Isolating Slow MySQL Queries" 翻譯而來。



本文開始

由於 MySQL 的普及，我們不難發現有許多 MySQL 的非專業使用者，他們十分依賴 MySQL 進行日常營運但卻不想(也不打算)進一步的成為 MySQL 專家，對他們而言只要可以用就好了。當與 Query 相關的效能瓶頸出現時，這些使用者將沒有能力解決所糟遇的難題，因為在這世界上並不存在對於 Slow Query 的通用解決方案；你所糟遇到的所有情況都是獨一無二的。

此份文件並非技術文件，而是記載辨識 MySQL Slow Queries 的一般性原則。你不需要是 MySQL 專家、也不需要知道如何分析 Query，也可以懂得如何辨識出對系統效能產生最多影響的 Slow Query。一但你辨識出這些 Queries 後，你就可以求助於 MySQL 專家來解決此問題。



一、建立基準(Baseline)

在你對 MySQL Server 進行任何改變之前，一定要先建立目前 MySQL Server 的效能基準，不然的話你就無法得知後續對於 Server 的調校是否真的可以提升效能。要建立基準的最簡單方式就是使用 mysqlreport，你只要先讓 MySQL Server 至少先運作個一整天然後再使用 mysqlreport 產生分析報表即可掌握 Server 目前的運作狀況。如果你沒辦法讓 MySQL Server 至少運作個一整天，那麼產生出來的報表可能會具有偏誤、比較不具代表性。

二、評估基準

mysqlreport 所產生出來的報表包含有非常多的資訊，但我們目前只需要關注在其中三項即可。首先要看的是 "Read ratio" (line 6 or 7)，它應該要低於 0.01，若它超過 0.01 你就必須要分配多一點的 RAM 給 MySQL Server 使用。務必要確定系統有足夠的 RAM 可使用，若系統整體的 RAM 使用量已超出負荷，然後你又調高 MySQL 可使用的 RAM 上限，將會造成 MySQL 不斷地進行 SWAP 而讓問題變得更嚴重。

接下來要看的是 "Slow" (line 16)。Slow 表示 Slow Queries 的數量，系統預設將執行時間超過 10 秒的 Query 判定為 Slow Queries，而這個項目的最後一個欄位之數值應該要低於 0.05，若它高於 0.30 你八成會發現系統有些很明顯的問題發生。我們必須要儘全力地去降低 Slow 的數值。

最後一個要注意的項目是 "Waited" (line 48)，尤其是最後一個欄位的數值。這個數值表示 MySQL 需要等待以取得 Table Lock 的次數之多寡，通常這個數值會低於 10%，若是它超過 10% 通常是因為 Slow Queries 所造成的。

你並不需要了解這些數值所代表的詳細意義，但是它們的確讓我們對於 MySQL 的運作狀況有一個整體的概念。若是這些數值偏高，那麼負責處理這些問題的 MySQL 專家將可以很容易的就找出問題點；但若是這些數值偏低，但 MySQL Server 卻運作的十分沒有效率，MySQL 專家們應該還是可以找出確切的問題點。

三、記錄 Slow Queries 並且等待

MySQL 預設並不會記錄 Slow Queries，你必須要修改 MySQL Server 的系統設定檔，例如 /etc/my.cnf:

[mysqld]
log-slow-queries
long_query_time = 1

重新啟動 MySQL 並且等待至少一整天的時間，MySQL 將會記錄所有執行時間超過一秒的 Slow Queries。Slow Query Log 的檔案名稱預設是 slow_queries，它們會存在 MySQL 的 Data Dir 之中，若是你不知道 MySQL Data Dir 在什麼地方，可以在登入 MySQL 指用以下的指令來找出："SHOW VARIABLES LIKE 'datadir';"。在 Linux 系統中它通常會是 /var/lib/mysql/，因此 Slow Query Log 就會是 /var/lib/mysql/slow_queries.log。

四、辨識出 Top 10 Slow Queries

要辨識出 Top 10 Slow Queries 的最簡單方式就是使用 mysqlsla 來分析 slow loq，你可以將 mysqlsla 所產生出來的報表交給 MySQL 專家，讓它們協助你解決問題。在大部份的情況下，就算是只有 Top 3 的 Slow Queries 可以被克服，MySQL Server 的整體效能仍然可能會有大幅度的提升。從這裡開始，之後的工作就要交由 MySQL 專家來處理。

五、確認系統效能是否已改善

假設您的 MySQL 專家具有解決 Top Slow Queries 的能力，那麼你的最後工作就是確認系統效能已確實的改善。重新啟動 MySQL 並且讓它至少執行個一整天，再以 mysqlreport 來評估系統的運作效能，然後再將結果與之前的報表做比較，尤其注意第二步驟中所提到的那三個項目(Read ratio, Slow, and Waited)。這三個項目的值應該會很顯著的降低才是，若沒有，則進一步的向您的 MySQL 專家詢問，他們將會告訴你為什麼這不是一個可以輕易達成的 "simple fix"。

事先告知

當你的 MySQL Server 順利的運轉了幾個星期之後同樣的問題卻再度發生時，不要覺得驚訝，因為效能的調校往往牽涉到許多複雜的層面，而不是單一的問題。當你今日解決了 MySQL 的效能問題後，有可能在幾個月後你會需要再重新進行一次整個步驟，這不是 MySQL 的錯，而是 "成長" 所造成的負作用。有可能只是單純的因為你的資料庫使用者發現 MySQL 運作的更順暢，因此他們就更頻繁的使用，而更頻繁的使用就再度的加重系統的負載。當越多的負載加諸在您的 MySQL Server 上時，自然就需要越多的效能調校。