【編者按】本文作者為 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 監控手冊.

查詢吞吐量

在監控任何系統時,你最關心的應該是確保系統能夠高效地完成工作.數據庫的工作是運行查詢,因此在本例中,你的首要任務是確保 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

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

查詢性能

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 文檔.

本文系 OneAPM 工程師編譯整理.OneAPM Cloud Insight 集監控、管理、計算、協作、可視化于一身,幫助所有 IT 公司,減少在系統監控上的人力和時間成本投入,讓運維工作更加高效、簡單.想閱讀更多技術文章,請拜訪 OneAPM 官方技術博客.