MySQL效能監控4大指標——第一部分

【編者按】本文作者為 John Matson，主要介紹 mysql 效能監控應該關注的4大指標。 第一部分將詳細介紹前兩個指標： 查詢吞吐量與查詢執行效能。文章系國內 ITOM 管理平臺 OneAPM 編譯呈現。

MySQL 是什麼？

MySQL 是現而今最流行的開源關係型資料庫伺服器。由 Oracle 所有，MySQL 提供了可以免費下載的社群版及包含更多特性與支援的商業版。從1995年首發以來，MySQL 衍生出多款備受矚目的分支，諸如具有相當競爭力的 MariaDB 及 Percona。

關鍵 MySQL 統計指標

如果你的資料庫執行緩慢，或者出於某種原因無法響應查詢，技術棧中每個依賴資料庫的元件都會遭受效能問題。為了保證資料庫的平穩執行，你可以主動監控以下四個與效能及資源利用率相關的指標：

• 查詢吞吐量
• 查詢執行效能
• 連線情況
• 緩衝池使用情況

MySQL 使用者可以接觸到數百個資料庫指標，因此，在本文中，筆者將專注於能幫助我們實時瞭解資料庫健康與效能的關鍵指標。

本文參考了我們在監控入門系列文章中介紹的指標術語，後者為指標收集與告警提供了基礎框架。

不同版本與技術的相容性

本系列文章討論的一些監控策略只適用於 MySQL 5.6與5.7版本。這些版本間的差異將在後文中提及。

本文列出的大多數指標與監控策略同樣適用於與 MySQL 相容的技術，諸如 MariaDB 與 Percona 伺服器，不過帶有一些明顯的差別。例如，MySQL Workbench(工作臺)中的一些特性（在本系列第二篇中有詳細介紹）就與當下的一些 MariaDB 版本不相容。

Amazon RDS 使用者應該檢視我們專門製作的 MySQL 在 RDS 以及與 MySQL 相容的 Amazon Aurora 監控手冊。

查詢吞吐量

名稱	描述	指標型別 可用性
Questions	已執行語句（由客戶端發出）計數	Work：吞吐量 伺服器狀態變數
Com_select	SELECT 語句	Work：吞吐量 伺服器狀態變數
Writes	插入，更新或刪除	Work：吞吐量 根據伺服器狀態變數計算得到

在監控任何系統時，你最關心的應該是確保系統能夠高效地完成工作。資料庫的工作是執行查詢，因此在本例中，你的首要任務是確保 MySQL 能夠如期執行查詢。

MySQL 有一個名為 Questions 的內部計數器（根據 MySQL 用語，這是一個伺服器狀態變數），客戶端每傳送一個查詢語句，其值就會加一。由 Questions 指標帶來的以客戶端為中心的視角常常比相關的 Queries 計數器更容易解釋。作為儲存程式的一部分，後者也會計算已執行語句的數量，以及諸如 PREPARE 和 DEALLOCATE PREPARE 指令執行的次數，作為伺服器端預處理語句的一部分。

通過以下指令，查詢諸如 Questions 或 Com_select 伺服器狀態變數的值：

SHOW GLOBAL STATUS LIKE "Questions";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 254408 |
+---------------+--------+

你也可以監控讀、寫指令的分解情況，從而更好地理解資料庫的工作負載、找到可能的瓶頸。通常，讀取查詢會由 Com_select 指標抓取，而寫入查詢則可能增加三個狀態變數中某一個的值，這取決於具體的指令：

Writes = Com_insert + Com_update + Com_delete

應該設定告警的指標：Questions

當前的查詢速率通常會有起伏，因此，如果基於固定的臨界值，查詢速率常常不是一個可操作的指標。但是，對於查詢數量的突變設定告警非常重要——尤其是查詢量的驟降，可能暗示著某個嚴重的問題。

查詢效能

名稱	描述	指標型別 可用性
查詢執行時間	每種模式下的平均執行時間	Work：效能 效能模式查詢
查詢錯誤	出現錯誤的 SQL 語句數量	Work：錯誤 效能模式查詢
Slow_queries	超過可配置的long_query_time 限制的查詢數量	Work：效能 伺服器狀態變數

MySQL 使用者監控查詢延遲的方式有很多，既可以通過 MySQL 內建的指標，也可以通過查詢效能模式。從 MySQL 5.6.6 版本開始預設啟用，MySQL 的 performance_schema 資料庫中的表格儲存著伺服器事件與查詢執行的低水平統計資料。

效能模式語句摘要

效能模式的 events_statements_summary_by_digest 表格中儲存著許多關鍵指標，抓取了與每條標準化語句有關的延遲、錯誤和查詢量資訊。從該表擷取的一行樣例顯示，某條語句被執行了兩次，平均執行用時為 325 毫秒（所有計時器的測量值都以微微秒為單位）：

*************************** 1. row *************************** 
               SCHEMA_NAME: employees                     
                    DIGEST: 0c6318da9de53353a3a1bacea70b4fce                
               DIGEST_TEXT: SELECT * FROM `employees` WHERE `emp_no` > ? 
                COUNT_STAR: 2             
            SUM_TIMER_WAIT: 650358383000             
            MIN_TIMER_WAIT: 292045159000             
            AVG_TIMER_WAIT: 325179191000             
            MAX_TIMER_WAIT: 358313224000              
             SUM_LOCK_TIME: 520000000                 
                SUM_ERRORS: 0               
              SUM_WARNINGS: 0         
         SUM_ROWS_AFFECTED: 0              
             SUM_ROWS_SENT: 520048          
          SUM_ROWS_EXAMINED: 520048
          ...          
          
          SUM_NO_INDEX_USED: 0     
     SUM_NO_GOOD_INDEX_USED: 0                 
                 FIRST_SEEN: 2016-03-24 14:25:32                  
                  LAST_SEEN: 2016-03-24 14:25:55

摘要表會標準化所有語句（如上面的 DIGEST_TEXT 一欄所示），忽略資料值，規範化空格與大小寫，因此，下面的兩條查詢會被認為是相同的：

select * from employees where emp_no >200;SELECT * FROM employees WHERE emp_no > 80000;

想要按模式抽取出以微秒為單位的平均執行時間，你可以這樣查詢效能模式：

SELECT schema_name
     , SUM(count_star) count     
     , ROUND(   (SUM(sum_timer_wait) / SUM(count_star))              
     / 1000000) AS avg_microsec  
     
     FROM performance_schema.events_statements_summary_by_digest 
     
 WHERE schema_name IS NOT NULL 
 GROUP BY schema_name;
+--------------------+-------+--------------+
| schema_name        | count | avg_microsec |
+--------------------+-------+--------------+
| employees          |   223 |       171940 |
| performance_schema |    37 |        20761 |
| sys                |     4 |          748 |
+--------------------+-------+--------------+

相似地，按模式計算出現錯誤的語句總數，可以這麼做：

SELECT schema_name
     , SUM(sum_errors) err_count
  FROM performance_schema.events_statements_summary_by_digest 
  WHERE schema_name IS NOT NULL 
  GROUP BY schema_name;
+--------------------+-----------+
| schema_name        | err_count |
+--------------------+-----------+
| employees          |         8 |
| performance_schema |         1 |
| sys                |         3 |
+--------------------+-----------+

sys 模式

用上面的方式查詢效能模式能以程式設計方式有效地從資料庫中檢索出指標。然而，對於特別查詢或調查，使用 MySQL 的 sys 模式通常更為簡單。sys 模式以人們更易讀的格式提供了一個有條理的指標集合，使得對應的查詢更加簡單。例如，想要找出最慢的語句（執行時間在95名開外）：

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

或者檢視哪些標準化語句出現了錯誤：

SELECT * FROM sys.statements_with_errors_or_warnings;

在 sys 模式的文件中，詳細介紹了許多有用的例子。sys 模式在 MySQL 5.7.7 版本中是預設包含的。不過，MySQL 5.6 使用者通過簡單的幾個指令就能安裝它。

慢查詢

除了效能模式與 sys 模式中豐富的效能資料，MySQL 還提供了一個 Slow_queries 計數器，每當查詢的執行時間超過 long_query_time 引數指定的值之後，該計數器就會增加。預設情況下，該臨界值設定為10秒。

SHOW VARIABLES LIKE `long_query_time`;
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

long_query_time 引數的值可通過一條指令進行調整。例如，將慢查詢臨界值設定為5秒：

SET GLOBAL long_query_time = 5;

（請注意，你可能要關閉會話，再重新連線至資料庫，這些更改才能在會話層生效。）

調查查詢效能問題

如果你的查詢執行得比預期要慢，很可能是某條最近修改的查詢在搗鬼。如果沒有發現特別緩慢的查詢，接下來就該評估系統級指標，尋找核心資源（CPU，磁碟 I/O，記憶體以及網路）的限制。CPU 飽和與 I/O 瓶頸是常見的問題根源。你可能還想檢查 Innodb_row_lock_waits 指標，該指標記錄著 InnoDB 儲存引擎不得不停下來獲得某行的鎖定的次數。從 MySQL 5.5 版本起，InnoDB 就是預設的儲存引擎，MySQL 對 InnoDB 表使用行級鎖定。

為了提高讀取與寫入操作的速度，許多使用者會想通過調整 InnoDB 使用的緩衝池大小來快取表與索引資料。本文的第二部分會對監控與調整緩衝池大小做詳細解讀。

應該設定告警的指標：

• 查詢執行時間：管理關鍵資料庫的延遲至關重要。如果生產環境中資料庫的平均查詢執行時間開始下降，應該尋找資料庫例項的資源限制，行鎖或表鎖間可能的爭奪，以及客戶端查詢模式的變化情況。

• 查詢錯誤：查詢錯誤的猛增可能暗示著客戶端應用或資料庫本身的問題。你可以使用 sys 模式快速查詢可能導致問題的查詢。例如，列舉出返回錯誤數最多的10條標準化語句：

     SELECT * FROM sys.statements_with_errors_or_warnings 
     ORDER BY errors DESC LIMIT 10;
  

• Slow_queries：如何定義慢查詢（並由此設定 long_query_time 引數）取決於你的使用者案例。但是，無論你如何定義“慢”，你都會想知道慢查詢的數量是否超出了基準水平。為了找出真正執行緩慢的查詢，你可以詢問 sys 模式，或深入瞭解 MySQL 提供的慢查詢日誌（該功能預設是禁用的）。有關啟用並讀取慢查詢日誌的更多信心，請參考 MySQL 文件。

敬請期待本文第二部分，主要介紹 MySQL 連線與緩衝池。

本文轉自 OneAPM 官方部落格