MySQL 效能監控工具--mysqlreport

kider

注：对此文章做个备份。

管理 MySQL 最讓人困擾的就是如何有效的掌握 MySQL 的健康狀況，因為 MySQL 雖然有提供許多系統變數值供您參考，但這些零散的數據若要手動搜集與過濾將會是一件十分沒有效率的事情(除非您寫 Scripts 去分析)。而接下來要介紹的這套 "工具" 其實是由 hackmysql.com 的站長所撰寫的 Perl Scritps，旨在協助 MySQL DBA 搜集與分析 MySQL 的運作狀況。

官方網站： http://hackmysql.com/
軟體下載： http://hackmysql.com/mysqlreport

這份文件有很大部份是參考 Daniel Nichter 的 mysqlreport Guide(http://hackmysql.com/mysqlreportguide)，但不完全是翻譯，裡面加入了一些我覺得可能會對讀者有幫助的資料，並刪除了部份我認為會對讀者產生混淆的資訊。小弟的功力不足，也許會有所錯誤，若是您發現我有地方寫錯了也請您不吝指正，謝謝。



接下來本文開始：

mysqlreport 可將重要的 MySQL 系統資訊整理為具有較高可讀性的報表，使你更容易閱讀與深入理解目前 MySQL 的實際運作狀況。除了手動執行 SHOW STATUS 指令並以人眼去過濾與判斷目前的系統狀態以外，mysqlreport 大概是較好(八成也是唯一)的替代方案。

目前的 mysqlreport 版本可以產生大量、具有完善資訊的報表，其報表完整的覆蓋了實務上所有重要的 MySQL 系統資訊，也可以產生只具有最重要資訊的較精簡報表。完整的報表包含了 14 種不同面向，超過 121 行的完整資訊；精簡的報表包含了 6 種不同面向，總計 29 行的最重要資訊。

此文件可教導您如何解讀 mysqlreport 所產生出來的各項資訊。如此一來，當您在閱讀 mysqlreport 所產生出來的報表時，您才可以回答最重要的問題：『MySQL Server 目前的運作狀況究竟如何？』

為了讓您有較深入的理解，此文件將從報表的第一行開始逐項的解釋，當您閱讀完此文件後，您應該具有完整的知識可以將 mysqlreport 佈署在任何 Server 上，並且有效的掌握 MySQL Server 的運作實況。

在開始之前，這裡有一份範例報表，我們將以此份報表為藍本開始進行教學。
（建議您將此報表列印出來和內文對照看，這樣子會比較容易理解文章內容）


PHP 語法:

   1 MySQL 5.0.3              uptime 0 0:34:26       Fri Sep  1 19:46:02 2006
   2
   3 __ Key _________________________________________________________________
   4 Buffer used   380.00k of 512.00M  %Used:   0.07
   5   Current      59.32M            %Usage:  11.59
   6 Write ratio      0.93
   7 Read ratio       0.00
   8  
   9 __ Questions ___________________________________________________________
  10 Total          98.06k   47.46/s
  11   DMS          81.23k   39.32/s  %Total:  82.84
  12   QC Hits      16.58k    8.02/s           16.91
  13   COM_QUIT        200    0.10/s            0.20
  14   Com_            131    0.06/s            0.13
  15   -Unknown         82    0.04/s            0.08
  16 Slow                0    0.00/s            0.00  %DMS:   0.00
  17 DMS            81.23k   39.32/s           82.84
  18   SELECT       64.44k   31.19/s           65.72         79.33
  19   INSERT       16.75k    8.11/s           17.08         20.61
  20   UPDATE           41    0.02/s            0.04          0.05
  21   REPLACE           0    0.00/s            0.00          0.00
  22   DELETE            0    0.00/s            0.00          0.00
  23 Com_              131    0.06/s            0.13
  24   change_db       119    0.06/s            0.12
  25   show_fields       9    0.00/s            0.01
  26   show_status       2    0.00/s            0.00
  27  
  28 __ SELECT and Sort _____________________________________________________
  29 Scan               38    0.02/s %SELECT:   0.06
  30 Range              14    0.01/s            0.02
  31 Full join           3    0.00/s            0.00
  32 Range check         0    0.00/s            0.00
  33 Full rng join       0    0.00/s            0.00
  34 Sort scan          14    0.01/s
  35 Sort range         26    0.01/s
  36 Sort mrg pass       0    0.00/s
  37
  38 __ Query Cache _________________________________________________________
  39 Memory usage   17.81M of  32.00M  %Used:  55.66
  40 Block Fragmnt  13.05%
  41 Hits           16.58k    8.02/s
  42 Inserts        48.50k   23.48/s
  43 Prunes         33.46k   16.20/s
  44 Insrt:Prune    1.45:1    7.28/s
  45 Hit:Insert     0.34:1
  46
  47 __ Table Locks _________________________________________________________
  48 Waited          1.01k    0.49/s  %Total:   1.24
  49 Immediate      80.04k   38.74/s
  50
  51 __ Tables ______________________________________________________________
  52 Open              107 of 1024    %Cache:  10.45
  53 Opened            118    0.06/s
  54
  55 __ Connections _________________________________________________________
  56 Max used           77 of  600      %Max:  12.83
  57 Total             202    0.10/s
  58
  59 __ Created Temp ________________________________________________________
  60 Disk table         10    0.00/s
  61 Table              26    0.01/s
  62 File                3    0.00/s
  63
  64 __ Threads _____________________________________________________________
  65 Running            55 of   77
  66 Cache               0              %Hit:    0.5
  67 Created           201    0.10/s
  68 Slow                0    0.00/s
  69
  70 __ Aborted _____________________________________________________________
  71 Clients             0    0.00/s
  72 Connects            8    0.00/s
  73
  74 __ Bytes _______________________________________________________________
  75 Sent           38.46M  18.62k/s
  76 Received        7.98M   3.86k/s  

Report Header: Line 1

報表的第一行包含了三樣不同的資訊：MySQL Server 的版本、自上次啟動後已經過多少時間、目前 Server 的日期與時間。有些人會定時讓系統自動產生報表(eg. cron)然後用程式去分析進行分析，此時表頭將可用來協助您辨識出不同時間點的報表。對於那些租用或使用虛擬主機的管理者，表頭可以協助您了解自己所需面對的是什麼樣的 Server。MySQL Server 版本可以指出該 Server 有提供或沒有提供那些功能，而它的 Uptime 則表示該報表具有多大的代表性。Uptime 是重要的指標，可讓您了解此份報表所包含的資訊是否可能有偏誤，一般來說 Uptime 最少要有一小時會比較適當，甚至光是一小時其實也還不夠。例如您的 Server 可能已執行了六個小時，但此六小時皆是在使用率最低的午夜，此時產生出的報表就很不具有代表性。最理想的情況下，你會希望 MySQL Server 至少已經執行了一整天，這樣子一來你就可以確定報表中的資訊已包含了 Server 負載的高峰與低峰期，而不是只包含其中之一。在範例報表中 Server 只執行了 34 分鐘，因此該報表的代表性是不足的，但因為這只是用來做範例，也就沒什麼關係。

Key Report: Lines 3 - 7

第一個主要報告區塊就是 Key Report，因為 Key(Indexes, 索引)是所有資訊中最重要的一項。雖然此報表無法告知您 Server 是否有善用 Index，但它可以告訴您 Server 對於 Shared Key Buffer 的使用狀態。請注意，這裡所指的 Key Buffer 是指 MyISAM Storage Engine 所使用的 Shared Key Buffer，InnoDB 所使用的 Key Buffer 並不包含在內。

MySQL Server 支援許多種不同類型的資料表(比較正式的說法是 Storage Engine)，你可以將它們想像為各種不同的資料結構，而不同的 Storage Engine 各有其優缺點。其中 MySQL Server 預設是使用 MyISAM Storage Engine。

MySQL Server 的 Buffer 大略可分為二種：
1. Global Buffer：由所有 Client 所共用的 Buffer

key_buffer
innodb_buffer_pool
innodb_log_buffer
innodb_additional_mem_pool
net_buffer ...等等

2. Thread Buffer：個別的 Connection 所需佔用的 Buffer

例如：
sort_buffer
myisam_sort_buffer
read_buffer
join_buffer
read_rnd_buffer ...等等

計算 Server 至少需使用的總記憶體數量的方式為：
min_memory_needed = global_buffer + (thread_buffers * max_connection)

關於 MySQL 的 Cache 機制有一點需要特別注意，各位應該都知道 MyISAM Storage Engine 將每個 table 分成三個檔案儲存在硬碟之中，例如若您有一個資料表的名稱為 example，那麼您就會在硬碟上發現 example.FRM, example.MYD, example.MYI 等三個檔案。這三個檔案所儲存的資料如下：

FRM： 儲存這個資料表的結構
MYD： Row Data，也就是你存在 example 資料表裡的資料
MYI： 此資料表的索引

接下來是重點：
當 MySQL 要 Cache 某個資料表時，請問 MySQL 會 Cache 哪些資料？

答案是：
MySQL 只會 Cache 索引，也就是 *.MYI 檔案，而 Row Data(*.MYD) 則是交由作業系統來負責 Cache。

接下來我們再回到 Key Buffer，有個很重要的問題我們一直沒有回答，就是『到底 Key Buffer 要設定多少才夠呢？』。如前所述，MySQL 只會 Cache 索引(*.MYI)，因此您只要將資料庫中所有的 MYI 檔案加總起來，你就會知道大概要設為多少。

Buffer used: Line 4

身為 MySQL 的管理者您通常會問的第一個問題是：『Server 到底用掉了多少 key buffer？』。如果您發現 MySQL 只使用了一小部份的 Key Buffer，這並不是什麼需要注意的問題，因為 MySQL 只會在需要的時候才實際分配與使用 System RAM。也就是說，當你設定 MySQL 可使用 512MB 的 RAM 時，並不代表 MySQL 啟動的時候將佔用 512MB 的 RAM(只有在 MySQL 認為需要這麼做的時候才會)。報表中的第四行(Buffer used)指出 MySQL "曾經" 耗用過的最大記憶體數量，因此目前 "正在使用" 的記憶體數量有可能少於（甚至大於）這個數字。MySQL 稱此數值為 "High Water Mark"，但在報表的下一行我們將會看到它並不總是如此。無論如何，從　Buffer used 我們通常可以看出 key_buffer_size 這個系統變數值是否設定的夠大，如果你的 MySQL 已經使用了 80~90% 以上的 Key Buffer，你就應該要調高 key_buffer_size。注意，Buffer used 永遠不會有使用率超過 95% 的情況，因為 MySQL 的官方文件中指出 Share Key Buffer 中有部份將會挪用給內部資料結構使用，因此當 Buffer used 指出 Share Key Buffer 的使用率高達 95% 時，其實在實務上等於是已使用了 100% 的 Share Key Buffer。在這個例子中 Server 只使用了 380KB(0.07%) 的 Share Key Buffer，看到這裡也許您會判斷 Server 的 Share Key Buffer 是十分充足的，但請勿太早下定論，我們必須要接著考量報表中的下一行才能做出客觀的判斷......。

Current: Line 5

mysqlreport 使用 Key_blocks_unused 這個系統變數來決定目前 MySQL "正在使用" 的 Share Key Buffer 大小，只有在 MySQL Server 4.1.2 以上的版本才會有這個功能。如果報表中的上一行(Buffer used)真的有如 MySQL 官方文件中所說的是 "High Water Mark"，那麼 Current 所載明的數值應該永遠會小於或等於它。但在接下來的例子中我們將會看到，事情並不總是如此。目前這台 Server 已經使用了大約 60MB(12%) 的 Share Key Buffer，這是一個好現象因為它代表了你的 Share Key Buffer 仍然十分充足。Current 與 Buffer used 合在一起看即可提供一個很有用的指標，告訴您目前的 key_buffer_size 是否充足。

設定 key_buffer_size 的方式也很簡單，只要直接修改 MySQL 的設定檔然後重新啟動 Server 即可。例如若要將 Key Buffer 設定為 2000MB，則只要在 /etc/my.cnf 中加上：
[mysqld]
key_buffer_size=2000M

Write ratio: Line 6

索引(Indexes, Keys)主要是在記憶體內(RAM-Based)進行操作的，索引之所以如此有用有部份原因就歸功於它們主要是在 RAM 裡面運作，因此擁有極高的存取效能，不像儲存在硬碟中的資料存取速度非常慢。然而，不可否認的是 MySQL 終究還是必須從硬碟中將索引讀入 RAM 或是將儲存在 RAM 中的索引寫回硬碟之中。Write ratio 標示著 MySQL 將索引寫入硬碟與 MySQL 將索引寫入 RAM 的比值(Write Ratio = MySQL 將索引寫入硬碟的次數 / MySQL 將索引寫入 RAM 的次數)。具有接近於 1 的 Write Ratio 並不是一件很罕見的事，就像 MySQL 官方手冊中所說的，如果你的 MySQL 最主要的活動是 Update、Insert 等等，那麼 Write Ratio 將會很接近於 1。Write Ratio 若大於 1 表示 MySQL 將索引寫入硬碟的次數大於將索引寫入 RAM 的次數，很少有 MySQL Server 的 Write Ratio 會大於 1，絕大部份都應該會小於 1，即便是負載非常重的 Server。

Read ratio: Line 7

Read Ratio 比 Write Ratio 來得重要一些，它標示了 MySQL 從硬碟讀取索引與從 RAM 讀取索引的比值(Read Ratio = MySQL 從硬碟讀取索引的次數 / MySQL 從 RAM 讀取索引的次數)。Read Ratio 的值應該要是 0.00 或 0.01，若大於這個值則表示 Server 有問題需要進一步的調查，通常此問題的成因是 Share Key Buffer 設得太小造成 MySQL 需要不斷地從硬碟中讀取所需要的索引資訊，而這個動作是十分沒有效率的並且完全抵消了使用索引可以帶來的好處。在 Server 剛啟動的頭一個小時 Read Ratio 很常會出現大於 0.01 的數值，但 Server 執行過一陣子後它應該(也必須)降低至 0.01 或是 0.00。

Questions Report: Lines 9 - 26

第二個主要的報表區塊，Questions，是第二重要的資訊，因為它可以告訴你 MySQL 到底都在忙些什麼事情。Questions 包含了 SQL queries 以及 MySQL protocol communications。大部份的人都只在意 Server 每秒可以處理多少查詢(Queries Per Second, QPS)，但若以整個 Server 的觀點來考量，QPS 其實是非常不精確的數值，它無法有效的告訴您 Server 的整體運作狀況。而 Questions 則提供了較完整的資訊，讓您一窺 Server 的全貌。

Total: Line 10

第一個欄位單純的記載 MySQL 總共回應過多少查詢，第二個欄位則記錄回應的頻率(QPS)，當大部份的人說『我的 Server 平均每秒處理 XXX 個查詢』時，他們指的其實就是第二個欄位所記錄的回應頻率。此時你應該要反問他們『在那 XXX 個查詢之中，MySQL 到底做了哪些事情？』，接下來 mysqlreport 將可以協助您回答此問題......。

Distribution of Total Queries (DTQ): Lines 11 - 15

所有的 Questions 可以大致區分為五個不同的類別：
1.Data Manipulation Statements (DMS)
2.query cache hits (QC Hits)
3.COM_QUIT
4.all other Com_ commands
5.Unknown

這五個類別將會展示在 Lines 11 至 15，但它們的順序是會改變的。mysqlreport 預設是以查詢的總數(第一個欄位)來排序，次數越多排得越上面，讓您可以快速的分辨出 MySQL 大部份時間都在忙些什麼東西。理想的情況下，你會希望 MySQL 把大部份的時間都花在 DMS 與 QC Hits 這兩個類別，因為這兩個類別才是真正在 "完成正事" 的類別。COM_QUIT、Com_、與 Unknown 也有其存在的必要，但它們應該只佔了其中的一小部份。在繼續深入介紹之前，也許你會好奇第三個欄位是做什麼用的，它代表了該分類(例如 DMS)佔全部 Queries 的百分比；若是在子分類(例如 Select)中，則表示該子分類佔所屬分類(例如 DMS)的百分比。在此範例中 DMS 佔了所有 Queries 的 82.84%，這是一個很好的現象。

Data manipulation statements(DMS) 包含了：ELECT, INSERT, REPLACE, UPDATE, 與 DELETE(技術上來說，其實不只這幾個類別但 mysqlreport 只會用到這幾類)。基本上，你可以將 DMS 想成是 MySQL 真正有在做些 "有用的事" 的情況，因此你會希望 DMS 是 MySQL 最忙著處理的事情。

QC Hits 是 MySQL 不需要實際執行 Query 而只要直接從 Query Cache 中即可找到所需資料的次數。擁有高比例的 QC Hits 是讓人夢寐以求的事，因為從 Query Cache 直接存取所需要的資料是十分快速且有效率的。然而大部份的 MySQL Server 因為各種原因，而無法具有非常有效率的 Query Cache。在本範例中 QC Hits 佔了所有 Questions 的 16.91%，這是非常好的情況。然而，千萬不要被這個數值給誤導了，在報表中的 38 至 45 行(Query Cache Report)將會告訴您完全不同的狀況。這是一個很好的範例，展示了 mysqlreport 可以做為深入、相互參照與比對的分析工具。當 QC Hits 看來似乎十分完美時，這個 Server 的 Qeury Cache Report 卻可以明確的告訴您其實事情沒有表面上看起來的那樣完美，我們在稍後會在回到這個問題。


COM_QUIT 算是比較不重要的類別，若您不是真的很有興趣其實您大可忽略這個類別的內容。

COM_ 這個類別代表著所有 MySQL 所執行過的指令，通常與 MySQL protocol 相關。在正常的情況下，你會希望這個類別所佔的比例越低越好，因為當這個數值很高的時候就表示 MySQL 正忙碌於無關緊要的事情上。若這個數值很高通常代表 MySQL 正遭遇到某些很奇怪的問題，當我們深入討論 COM_ 的子類別的時候，我們會在回來探討這個問題。

Unknown 是推論出來的類別，在理想的狀況下，之前所述的四個分類加總起來應該要等於 Questions 總數，但它們通常不會剛好等於。這是因為有些 Questions MySQL 在處理時會增加 Total Questions 的計數器，但卻沒有相對應的系統變數用來記錄所執行過的 Questions。在不同的 Server 上這個數值的變異很大，在有些 Server 上這個數值非常的高，在有些 Server 上則非常的低，但在大部份的情況下它應該要維持在很低的水準才是。如果這個數值非常的高，可能代表 MySQL Server 有什麼地方出了問題。