【編者按】本文作者為 John Matson,主要介紹 mysql 性能監(jiān)控應(yīng)該關(guān)注的4大指標(biāo). 第一部分將詳細(xì)介紹前兩個(gè)指標(biāo)： 查詢吞吐量與查詢執(zhí)行性能.文章系國(guó)內(nèi) ITOM 管理平臺(tái) OneAPM 編譯呈現(xiàn).

MySQL 是什么?

MySQL 是現(xiàn)而今最流行的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù)服務(wù)器.由 Oracle 所有,MySQL 提供了可以免費(fèi)下載的社區(qū)版及包含更多特性與支持的商業(yè)版.從1995年首發(fā)以來(lái),MySQL 衍生出多款備受矚目的分支,諸如具有相當(dāng)競(jìng)爭(zhēng)力的 MariaDB 及 Percona.

關(guān)鍵 MySQL 統(tǒng)計(jì)指標(biāo)

如果你的數(shù)據(jù)庫(kù)運(yùn)行緩慢,或者出于某種原因無(wú)法響應(yīng)查詢,技術(shù)棧中每個(gè)依賴數(shù)據(jù)庫(kù)的組件都會(huì)遭受性能問(wèn)題.為了保證數(shù)據(jù)庫(kù)的安穩(wěn)運(yùn)行,你可以主動(dòng)監(jiān)控以下四個(gè)與性能及資源利用率相關(guān)的指標(biāo)：

• 查詢吞吐量

• 查詢執(zhí)行性能

• 連接情況

• 緩沖池使用情況

MySQL 用戶可以接觸到數(shù)百個(gè)數(shù)據(jù)庫(kù)指標(biāo),因此,在本文中,筆者將專(zhuān)注于能幫助我們實(shí)時(shí)了解數(shù)據(jù)庫(kù)健康與性能的關(guān)鍵指標(biāo).

本文參考了我們?cè)诒O(jiān)控入門(mén)系列文章中介紹的指標(biāo)術(shù)語(yǔ),后者為指標(biāo)收集與告警提供了基礎(chǔ)框架.

不同版本與技術(shù)的兼容性

本系列文章討論的一些監(jiān)控策略只適用于 MySQL 5.6與5.7版本.這些版本間的差異將在后文中提及.

本文列出的大多數(shù)指標(biāo)與監(jiān)控策略同樣適用于與 MySQL 兼容的技術(shù),諸如 MariaDB 與 Percona 服務(wù)器,不過(guò)帶有一些明顯的差別.例如,MySQL Workbench(工作臺(tái))中的一些特性(在本系列第二篇中有詳細(xì)介紹)就與當(dāng)下的一些 MariaDB 版本不兼容.

Amazon RDS 用戶應(yīng)該查看我們專(zhuān)門(mén)制作的 MySQL 在 RDS 以及與 MySQL 兼容的 Amazon Aurora 監(jiān)控手冊(cè).

查詢吞吐量

在監(jiān)控任何系統(tǒng)時(shí),你最關(guān)心的應(yīng)該是確保系統(tǒng)能夠高效地完成工作.數(shù)據(jù)庫(kù)的工作是運(yùn)行查詢,因此在本例中,你的首要任務(wù)是確保 MySQL 能夠如期執(zhí)行查詢.

MySQL 有一個(gè)名為 Questions 的內(nèi)部計(jì)數(shù)器(根據(jù) MySQL 用語(yǔ),這是一個(gè)服務(wù)器狀態(tài)變量),客戶端每發(fā)送一個(gè)查詢語(yǔ)句,其值就會(huì)加一.由 Questions 指標(biāo)帶來(lái)的以客戶端為中心的視角常常比相關(guān)的Queries 計(jì)數(shù)器更容易解釋.作為存儲(chǔ)程序的一部分,后者也會(huì)計(jì)算已執(zhí)行語(yǔ)句的數(shù)量,以及諸如PREPARE 和 DEALLOCATE PREPARE 指令運(yùn)行的次數(shù),作為服務(wù)器端預(yù)處理語(yǔ)句的一部分.

通過(guò)以下指令,查詢諸如 Questions 或 Com_select 服務(wù)器狀態(tài)變量的值：

SHOW GLOBAL STATUS LIKE "Questions";

+---------------+--------+

| Variable_name | Value |

+---------------+--------+

| Questions | 254408 |

+---------------+--------+

你也可以監(jiān)控讀、寫(xiě)指令的分解情況,從而更好地理解數(shù)據(jù)庫(kù)的工作負(fù)載、找到可能的瓶頸.通常,讀取查詢會(huì)由 Com_select 指標(biāo)抓取,而寫(xiě)入查詢則可能增加三個(gè)狀態(tài)變量中某一個(gè)的值,這取決于具體的指令：

Writes = Com_insert + Com_update + Com_delete

應(yīng)該設(shè)置告警的指標(biāo)：Questions

當(dāng)前的查詢速率通常會(huì)有起伏,因此,如果基于固定的臨界值,查詢速率常常不是一個(gè)可操作的指標(biāo).但是,對(duì)于查詢數(shù)量的突變?cè)O(shè)置告警非常重要——尤其是查詢量的驟降,可能暗示著某個(gè)嚴(yán)重的問(wèn)題.

查詢性能

MySQL 用戶監(jiān)控查詢延遲的方式有很多,既可以通過(guò) MySQL 內(nèi)置的指標(biāo),也可以通過(guò)查詢性能模式.從MySQL 5.6.6 版本開(kāi)始默認(rèn)啟用,MySQL 的 performance_schema 數(shù)據(jù)庫(kù)中的表格存儲(chǔ)著服務(wù)器事件與查詢執(zhí)行的低水平統(tǒng)計(jì)數(shù)據(jù).

性能模式語(yǔ)句摘要

性能模式的 events_statements_summary_by_digest 表格中保存著許多關(guān)鍵指標(biāo),抓取了與每條標(biāo)準(zhǔn)化語(yǔ)句有關(guān)的延遲、錯(cuò)誤和查詢量信息.從該表截取的一行樣例顯示,某條語(yǔ)句被執(zhí)行了兩次,平均執(zhí)行用時(shí)為 325 毫秒(所有計(jì)時(shí)器的測(cè)量值都以微微秒為單位)：

*************************** 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

摘要表會(huì)標(biāo)準(zhǔn)化所有語(yǔ)句(如上面的 DIGEST_TEXT 一欄所示),忽略數(shù)據(jù)值,規(guī)范化空格與大小寫(xiě),因此,下面的兩條查詢會(huì)被認(rèn)為是相同的：

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

想要按模式抽取出以微秒為單位的平均運(yùn)行時(shí)間,你可以這樣查詢性能模式：

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 |

+--------------------+-------+--------------+

相似地,按模式計(jì)算出現(xiàn)錯(cuò)誤的語(yǔ)句總數(shù),可以這么做：

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 模式

用上面的方式查詢性能模式能以編程方式有效地從數(shù)據(jù)庫(kù)中檢索出指標(biāo).然而,對(duì)于特別查詢或調(diào)查,使用 MySQL 的 sys 模式通常更為簡(jiǎn)單.sys 模式以人們更易讀的格式提供了一個(gè)有條理的指標(biāo)集合,使得對(duì)應(yīng)的查詢更加簡(jiǎn)單.例如,想要找出最慢的語(yǔ)句(運(yùn)行時(shí)間在95名開(kāi)外)：

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

或者查看哪些標(biāo)準(zhǔn)化語(yǔ)句出現(xiàn)了錯(cuò)誤：

SELECT * FROM sys.statements_with_errors_or_warnings;

在 sys 模式的文檔中,詳細(xì)介紹了許多有用的例子.sys 模式在 MySQL 5.7.7 版本中是默認(rèn)包含的.不過(guò),MySQL 5.6 用戶通過(guò)簡(jiǎn)單的幾個(gè)指令就能安裝它.

慢查詢

除了性能模式與 sys 模式中豐富的性能數(shù)據(jù),MySQL 還提供了一個(gè) Slow_queries 計(jì)數(shù)器,每當(dāng)查詢的執(zhí)行時(shí)間超過(guò) long_query_time 參數(shù)指定的值之后,該計(jì)數(shù)器就會(huì)增加.默認(rèn)情況下,該臨界值設(shè)置為10秒.

SHOW VARIABLES LIKE 'long_query_time';

+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

long_query_time 參數(shù)的值可通過(guò)一條指令進(jìn)行調(diào)整.例如,將慢查詢臨界值設(shè)置為5秒：

SET GLOBAL long_query_time = 5;

(請(qǐng)注意,你可能要關(guān)閉會(huì)話,再重新連接至數(shù)據(jù)庫(kù),這些更改才能在會(huì)話層生效.)

調(diào)查查詢性能問(wèn)題

如果你的查詢運(yùn)行得比預(yù)期要慢,很可能是某條最近修改的查詢?cè)趽v鬼.如果沒(méi)有發(fā)現(xiàn)特別緩慢的查詢,接下來(lái)就該評(píng)估系統(tǒng)級(jí)指標(biāo),尋找核心資源(CPU,磁盤(pán) I/O,內(nèi)存以及網(wǎng)絡(luò))的限制.CPU 飽和與 I/O 瓶頸是常見(jiàn)的問(wèn)題根源.你可能還想檢查 Innodb_row_lock_waits 指標(biāo),該指標(biāo)記錄著 InnoDB 存儲(chǔ)引擎不得不停下來(lái)獲得某行的鎖定的次數(shù).從 MySQL 5.5 版本起,InnoDB 就是默認(rèn)的存儲(chǔ)引擎,MySQL 對(duì) InnoDB 表使用行級(jí)鎖定.

為了提高讀取與寫(xiě)入操作的速度,許多用戶會(huì)想通過(guò)調(diào)整 InnoDB 使用的緩沖池大小來(lái)緩存表與索引數(shù)據(jù).本文的第二部分會(huì)對(duì)監(jiān)控與調(diào)整緩沖池大小做詳細(xì)解讀.

應(yīng)該設(shè)置告警的指標(biāo)：

• 查詢運(yùn)行時(shí)間：管理關(guān)鍵數(shù)據(jù)庫(kù)的延遲至關(guān)重要.如果生產(chǎn)環(huán)境中數(shù)據(jù)庫(kù)的平均查詢運(yùn)行時(shí)間開(kāi)始下降,應(yīng)該尋找數(shù)據(jù)庫(kù)實(shí)例的資源限制,行鎖或表鎖間可能的爭(zhēng)奪,以及客戶端查詢模式的變化情況.

• 查詢錯(cuò)誤：查詢錯(cuò)誤的猛增可能暗示著客戶端應(yīng)用或數(shù)據(jù)庫(kù)本身的問(wèn)題.你可以使用 sys 模式快速查找可能導(dǎo)致問(wèn)題的查詢.例如,列舉出返回錯(cuò)誤數(shù)最多的10條標(biāo)準(zhǔn)化語(yǔ)句：

SELECT * FROM sys.statements_with_errors_or_warnings

ORDER BY errors DESC LIMIT 10;

• Slow_queries：如何定義慢查詢(并由此設(shè)置 long_query_time 參數(shù))取決于你的用戶案例.但是,無(wú)論你如何定義“慢”,你都會(huì)想知道慢查詢的數(shù)量是否超出了基準(zhǔn)水平.為了找出真正執(zhí)行緩慢的查詢,你可以詢問(wèn) sys 模式,或深入了解 MySQL 提供的慢查詢?nèi)罩?該功能默認(rèn)是禁用的).有關(guān)啟用并讀取慢查詢?nèi)罩镜母嘈判?請(qǐng)參考 MySQL 文檔.

本文系 OneAPM 工程師編譯整理.OneAPM Cloud Insight 集監(jiān)控、管理、計(jì)算、協(xié)作、可視化于一身,幫助所有 IT 公司,減少在系統(tǒng)監(jiān)控上的人力和時(shí)間成本投入,讓運(yùn)維工作更加高效、簡(jiǎn)單.想閱讀更多技術(shù)文章,請(qǐng)拜訪 OneAPM 官方技術(shù)博客.