天天看點
傳回

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監控手冊。

MySQL 性能監控 4 大名額
名稱 描述 名額類型 可用性
Questions 已執行語句（由用戶端發出）計數 Work：吞吐量 伺服器狀态變量
Com_select SELECT 語句
Writes 插入，更新或删除 根據伺服器狀态變量計算得到

在監控任何系統時，你最關心的應該是確定系統能夠高效地完成工作。資料庫的工作是運作查詢，是以在本例中，你的首要任務是確定 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 性能監控 4 大名額
查詢運作時間 每種模式下的平均運作時間 Work：性能 性能模式查詢
查詢錯誤 出現錯誤的 SQL 語句數量 Work：錯誤
Slow_queries 超過可配置的

long_query_time

  限制的查詢數量

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 性能監控 4 大名額
Threads_connected 目前開放的連接配接 資源: 使用率
Threads_running 目前運作的連接配接
Connection_errors_internal 由伺服器錯誤導緻的失敗連接配接數 資源: 錯誤
Aborted_connects 嘗試與伺服器進行連接配接結果失敗的次數
Connection_errors_max_connections 由 

max_connections

  限制導緻的失敗連接配接數

檢查并設定連接配接限制

監控用戶端連接配接情況相當重要，因為一旦可用連接配接耗盡，新的用戶端連接配接就會遭到拒絕。MySQL 預設的連接配接數限制為 151，可通過下面的查詢加以驗證：

SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

MySQL 的文檔指出，健壯的伺服器應該能夠處理成百上千的連接配接數。

“正常情況下，Linux 或 Solaris 應該能夠支援 500 到 1000 個同時連接配接。如果可用的 RAM 較大，且每個連接配接的工作量較低或目标響應時間較為寬松，則最多可處理 10000 個連接配接。而 Windows 能處理的連接配接數一般不超過 2048 個，這是由于該平台上使用的 Posix 相容層。”

連接配接數限制可以在系統運作時進行調整：

SET GLOBAL max_connections = 200;

然而，此設定會在伺服器重新開機時恢複為預設值。想要永久地改變連接配接數限制，可以在 

my.cnf

 配置檔案中添加如下配置（檢視本文了解如何定位配置檔案）：

max_connections = 200

監控連接配接使用率

MySQL 提供了 

Threads_connected

 名額以記錄連接配接的線程數——每個連接配接對應一個線程。通過監控該名額與先前設定的連接配接限制，你可以確定伺服器擁有足夠的容量處理新的連接配接。MySQL 還提供了

Threads_running

 名額，幫助你分隔在任意時間正在積極處理查詢的線程與那些雖然可用但是閑置的連接配接。

如果伺服器真的達到 

max_connections

 限制，它就會開始拒絕新的連接配接。在這種情況下，

Connection_errors_max_connections

 名額就會開始增加，同時，追蹤所有失敗連接配接嘗試的

Aborted_connects

 名額也會開始增加。

MySQL 提供了許多有關連接配接錯誤的名額，幫助你調查連接配接問題。

Connection_errors_internal

 是個很值得關注的名額，因為該名額隻會在錯誤源自伺服器本身時增加。内部錯誤可能反映了記憶體不足狀況，或者伺服器無法開啟新的線程。

應該設定告警的名額

  • Threads_connected

     ：當所有可用連接配接都被占用時，如果一個用戶端試圖連接配接至 MySQL，後者會傳回 “Too many connections(連接配接數過多)” 錯誤，同時将

    Connection_errors_max_connections

      的值增加。為了防止出現此類情況，你應該監控可用連接配接的數量，并確定其值保持在 

    max_connections

      限制以内。

  • Aborted_connects

     ：如果該計數器在不斷增長，意味着使用者嘗試連接配接到資料庫的努力全都失敗了。此時，應該借助 

    Connection_errors_max_connections

      與  

    Connection_errors_internal

      之類細粒度高的名額調查該問題的根源。

MySQL 性能監控 4 大名額
Innodb_buffer_pool_pages_total 緩沖池中的總頁數
緩沖池使用率 緩沖池中已使用頁數所占的比率
Innodb_buffer_pool_read_requests 向緩沖池發送的請求
Innodb_buffer_pool_reads 緩沖池無法滿足的請求 資源: 飽和度

MySQL 預設的存儲引擎 InnoDB 使用了一片稱為緩沖池的記憶體區域，用于緩存資料表與索引的資料。緩沖池名額屬于資源名額，而非工作名額，前者更多地用于調查（而非檢測）性能問題。如果資料庫性能開始下滑，而磁盤 I/O 在不斷攀升，擴大緩沖池往往能帶來性能回升。

檢查緩沖池的大小

預設設定下，緩沖池的大小通常相對較小，為 128MiB。不過，MySQL 建議可将其擴大至專用資料庫伺服器實體記憶體的 80% 大小。然而，MySQL 也指出了一些注意事項：InnoDB 的記憶體開銷可能提高超過緩沖池大小 10% 的記憶體占用。并且，如果你耗盡了實體記憶體，系統會求助于分頁，導緻資料庫性能嚴重受損。

緩沖池也可以劃分為不同的區域，稱為執行個體。使用多個執行個體可以提高大容量 (多 GiB) 緩沖池的并發性。

緩沖池大小調整操作是分塊進行的，緩沖池的大小必須為塊的大小乘以執行個體的數目再乘以某個倍數。

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size 
                           * innodb_buffer_pool_instances

塊的預設大小為 128 MiB，但是從 MySQL 5.7.5 開始可以自行配置。以上兩個參數的值都可以通過如下方式進行檢查：

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";
SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

如果 

innodb_buffer_pool_chunk_size

 查詢沒有傳回結果，則表示在你使用的 MySQL 版本中此參數無法更改，其值為 128 MiB。

在伺服器啟動時，你可以這樣設定緩沖池的大小以及執行個體的數量：

$ mysqld --innodb_buffer_pool_size=8G 
--innodb_buffer_pool_instances=16

在 MySQL 5.7.5 版本，你可以通過 

SET

 指令在系統運作時修改緩沖池的大小，并精确到位元組數。例如，假設有兩個緩沖池執行個體，你可以将其總大小設定為 8 GiB，這樣每個執行個體的大小即為 4 GiB。

SET GLOBAL innodb_buffer_pool_size=8589934592;

關鍵的 InnoDB 緩沖池名額

MySQL 提供了許多關于緩沖池及其使用率的名額。其中一些有用的名額能夠追蹤緩沖池的總大小，緩沖池的使用量，以及其處理讀取操作的效率。

名額 

Innodb_buffer_pool_read_requests

 及 

Innodb_buffer_pool_reads

 對于了解緩沖池使用率都非常關鍵。

Innodb_buffer_pool_read_requests

 追蹤合理讀取請求的數量，而

Innodb_buffer_pool_reads

 追蹤緩沖池無法滿足，因而隻能從磁盤讀取的請求數量。我們知道，從記憶體讀取的速度比從磁盤讀取通常要快好幾個數量級，是以，如果 

Innodb_buffer_pool_reads

 的值開始增加，意味着資料庫性能大有問題。

緩沖池使用率是在考慮擴大緩沖池之前應該檢查的重要名額。使用率名額無法直接讀取，但是可以通過下面的方式簡單地計算得到：

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / 
 Innodb_buffer_pool_pages_total

如果你的資料庫從磁盤進行大量讀取，而緩沖池還有許多閑置空間，這可能是因為緩存最近才清理過，還處于熱身階段。如果你的緩沖池并未填滿，但能有效處理讀取請求，則說明你的資料工作集相當适應目前的記憶體配置。

然而，較高的緩沖池使用率并不一定意味着壞消息，因為舊資料或不常使用的資料會根據 LRU 算法 自動從緩存中清理出去。但是，如果緩沖池無法有效滿足你的讀取工作量，這可能說明擴大緩存的時機已至。

将緩沖池名額轉化為位元組

大多數緩沖池名額都以記憶體頁面為機關進行記錄，但是這些名額也可以轉化為位元組，進而使其更容易與緩沖池的實際大小相關聯。例如，你可以使用追蹤緩沖池中記憶體頁面總數的伺服器狀态變量找出緩沖池的總大小（以位元組為機關）：

Innodb_buffer_pool_pages_total * innodb_page_size

InnoDB 頁面大小是可調整的，但是預設設定為 16 KiB，或 16,384 位元組。你可以使用 

SHOW VARIABLES

 查詢了解其目前值：

SHOW VARIABLES LIKE "innodb_page_size";

結論

在本文中，我們介紹了許多你應該加以監控進而了解 MySQL 活動與性能表現的重要名額。如果你正在躊躇 MySQL 監控方案，抓取下面列出的名額能讓你真正了解資料庫的使用模式與可能的限制情況。這些名額也能幫助你發現，何時擴充伺服器記憶體或将資料庫移至更為強大的主機，進而保持良好的應用性能。

  • 查詢延遲與錯誤
  • 用戶端連接配接與錯誤
  • 緩沖池使用率

鳴謝

非常感謝來自 Oracle 的 Dave Stokes 與 VividCortex 的 Ewen Fortune，他們在本文釋出之前提供了許多寶貴的回報意見。

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

原文位址：https://www.datadoghq.com/blog/monitoring-mysql-performance-metrics/

http://blog.oneapm.com/apm-tech/754.html

http://blog.oneapm.com/apm-tech/755.html

上一篇: MYSQL: Handler_read_%參數說明
下一篇: count(1) count(*)