MySQL 監控指標

標籤：ODB   情境   空間   讀寫   state   緩衝   檔案的   ping   sync   

為了排查問題，對資料庫的監控是必不可少的，在此介紹下 MySQL 中的常用監控指標。

簡介

MySQL 有多個分支版本，常見的有 MySQL、Percona、MariaDB，各個版本所對應的監控項也會有些區別，在此僅介紹一些通用的監控項。

通常，監控項的源碼是在 mysql/mysqld.cc 檔案中定義，其內容如下所示。

SHOW_VAR status_vars[]= {    {"Aborted_clients",          (char*) &aborted_threads,        SHOW_LONG},    {"Aborted_connects",         (char*) &aborted_connects,       SHOW_LONG},    {"Acl",                      (char*) acl_statistics,          SHOW_ARRAY},    // ... ...};

監控

對於資料庫，通常可以主動式監控以下四個與效能及資源使用率相關的指標：

• 查詢輸送量
• 查詢執行效能
• 串連情況
• 緩衝池使用方式

輸送量

在 MySQL 中有各種針對不同命令的統計，其監控項指標以 Com_XXX 方式命名，其中比較常用的統計項包括了 TPS/QPS。

而 MySQL 與 QPS 相關的包括了三個監控項，分別為 Queries、Questions、Com_select，一般會採用 Com_select 作為採集項；對於 TPS，一般認為是 Com_insert + Com_update + Com_delete 三個統計項的和。

Queries 和 Questioins 區別

如下是 Server Status Variables 中對這兩個值的介紹。

Queries : The number of statements executed by the server. This variableincludes statements executed within stored programs, unlike the Questionsvariable. It does not count COM_PING or COM_STATISTICS commands.Questions : The number of statements executed by the server. This includesonly statements sent to the server by clients and not statements executedwithin stored programs, unlike the Queries variable. This variable doesnot count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, orCOM_STMT_RESET commands.

也就是說，如果不使用 prepared statements ，那麼兩者的區別是 Questions 會將預存程序作為一個語句；而 Queries 會統計預存程序中的各個執行的語句。

mysql> SHOW STATUS LIKE "questions";        ← 查看本串連執行的查詢SQL數+---------------+-------+| Variable_name | Value |+---------------+-------+| Questions     | 2     |+---------------+-------+1 row in set (0.00 sec)mysql> SHOW STATUS LIKE "queries";+---------------+----------+| Variable_name | Value    |+---------------+----------+| Queries       | 21241470 |+---------------+----------+1 row in set (0.00 sec)

上述的 questions 是會話層級的，當然可以通過 global 查看全域的變數。

另外，MySQL 會在執行 SQL 之前開始增加上述的統計計數；而當前正在執行的 SQL 數量則可以通過 threads_running 查看。

總結

Questions        已執行的由用戶端發出的語句Com_select       執行SELECT語句的數量，通常也就是QPSWrites           Com_insert+Com_update+Com_delete，也就是TPS

執行效能

目前，有幾種方式可以用來查看 MySQL 的執行效能問題，可以參考如下。

performance_schema

在 events_statements_summary_by_digest 表中儲存了許多關鍵計量，均以微秒為單位，該表會忽略數值、正常化空格與大小寫。

如果要以微秒為單位查看各個 database 的平均已耗用時間，或者出現的錯誤語句總數，可以通過如下方式查看：

mysql> 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;mysql> 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;

sys

sys 儲存引擎預設在 5.7.7 中添加，對於 5.6 可以手動安裝，詳細可以參考 github - sys schema 。

慢查詢

MySQL 提供了一個 Slow_queries 計數器，當查詢的執行時間超過 long_query_time 參數指定的值之後，該計數器就會增加，預設設定為 10 秒。

mysql> SHOW VARIABLES LIKE ‘long_query_time‘;+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)mysql> SET GLOBAL long_query_time = 5;Query OK, 0 rows affected (0.00 sec)

需要注意的是，需要關閉會話然後重新串連之後，才能使該參數生效。

總結

Slow_queries         慢查詢的數量

其它的一些比較適合發現效能問題後用於排查。

串連情況

監控用戶端串連情況相當重要，因為一旦可用串連耗盡，新的用戶端串連就會遭到拒絕，MySQL 預設的串連數限制為 151，可通過下面的方法在設定檔中進行設定。

[mysqld]max_connections = 200

通過如下方法查詢和臨時設定。

mysql> SHOW VARIABLES LIKE ‘max_connections‘;+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+1 row in set (0.00 sec)mysql> SET GLOBAL max_connections = 200;Query OK, 0 rows affected (0.00 sec)

通常 Linux 可以處理 500~1000 個串連，如果 RAM 資源足夠，可處理 1W+ 個串連，而 Windows 由於採用 Posix 相容層，能處理的串連數一般不超過 2048 個。

監控串連使用率

如果採用每個串連一個線程的方式，可以通過 Threads_connected 查看，監控該指標與先前設定的串連限制，可以確保伺服器擁有足夠的容量處理新的串連。

另外，通過 Threads_running 指標，可以查看正在處理請求的線程，可以用來判斷那些串連被佔用但是卻沒有處理任何請求。

如果達到 max_connections 就會拒絕新的串連請求，Connection_errors_max_connections 指標就會開始增加，同時，追蹤所有失敗串連嘗試的 Aborted_connects 指標也會開始增加。

另外，通過 Connection_errors_internal 這個指標，可以用來監控來自伺服器本身導致的錯誤，例如記憶體不足。

總結

Threads_connected                   已經建立的串連Threads_running                     正在啟動並執行串連Connection_errors_internal          由於伺服器內部本身導致的錯誤Aborted_connects                    嘗試與伺服器建立串連但是失敗的次數Connection_errors_max_connections   由於到達最大串連數導致的錯誤

緩衝池使用方式

InnoDB 使用一片記憶體地區作為緩衝區，用來快取資料表與索引資料，緩衝區太小可能會導致資料庫效能下滑，磁碟 I/O 攀升。

預設值一般是 128MiB，建議將其設定為實體記憶體的 80%；不過需要注意的是，InnoDB 可能會使用超過緩衝池 10%，如果耗盡記憶體，則會使用分頁，從而使資料庫效能受損。

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，實際參數為 innodb_buffer_pool_size 。

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

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

監控指標

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 * 100%(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) /    Innodb_buffer_pool_read_requests * 100%

如果資料庫從磁碟進行大量讀取，而緩衝池還有許多閑置空間，這可能是因為緩衝最近才清理過，還處於預熱階段。

總結

Innodb_buffer_pool_pages_total          BP中總頁面數Buffer pool utilization                 BP中頁面的使用率Innodb_buffer_pool_read_requests        BP的讀請求Innodb_buffer_pool_reads                需要讀取磁碟的請求數

回應時間

“回應時間” (Response Time, RT) 在資料庫應用中，尤其是 OLTP 的情境，非常重要，但官方版本中一直沒有加上這個統計功能。開始使用的是 tcpdump+mk-query-digest，再後來 tcprstat，很快 Percona 提供了回應時間統計外掛程式。

對於 MariaDB 也存在類似的方式，可以通過如下的方式安裝、測試。

----- 1. 查看是否存在外掛程式$ ls /opt/mariadb/lib/plugin | grep responsequery_response_time.so----- 2. 安裝外掛程式mysql> INSTALL PLUGIN query_response_time_audit SONAME ‘query_response_time.so‘;Query OK, 0 rows affected (0.00 sec)mysql> INSTALL PLUGIN query_response_time SONAME ‘query_response_time.so‘;Query OK, 0 rows affected (0.00 sec)----- 3. 開啟統計功能mysql> SET GLOBAL query_response_time_stats = ‘ON‘;Query OK, 0 rows affected (0.00 sec)mysql> SET GLOBAL query_response_time_flush = ‘ON‘;Query OK, 0 rows affected (0.00 sec)----- 4. 查看統計值mysql> SHOW QUERY_RESPONSE_TIME;mysql> SELECT * FROM information_schema.query_response_time;+----------------+-------+----------------+| Time           | Count | Total          |+----------------+-------+----------------+|       0.000001 |     0 |       0.000000 ||       0.000010 |     0 |       0.000000 ||       0.000100 |     1 |       0.000089 ||       0.001000 |    14 |       0.010173 ||       0.010000 |     0 |       0.000000 ||       0.100000 |     0 |       0.000000 ||       1.000000 |     0 |       0.000000 ||      10.000000 |     0 |       0.000000 ||     100.000000 |     0 |       0.000000 ||    1000.000000 |     0 |       0.000000 ||   10000.000000 |     0 |       0.000000 ||  100000.000000 |     0 |       0.000000 || 1000000.000000 |     0 |       0.000000 || TOO LONG       |     0 | TOO LONG       |+----------------+-------+----------------+14 rows in set (0.00 sec)

預設的時間統計區間是按照基數 10 增長的，也就是說預設的區間如下：

(0;10^-6], (10^-6;10^-5], (10^-5;10^-4], ..., (10^-1;10^1], (10^1; 10^2], ...

可以通過修改參數 query_response_time_range_base 來縮小時間區間，預設該是 10，實際的統計時間區間如上，可以修改為 2，則區間如下：

(0;2^-19], (2^-19;2^-18], (2^-18;2^-17], ..., (2^-1; 2^1], (2^1; 2^2], ...

第一個區間總是最接近 0.000001 的區間開始；最後區間是到最接近且小於 10000000 處結束。

當然，有些比較從網上摘錄的不錯 SQL，可以根據自己需求修改。

SELECT    case TRIM(time)        when ‘0.000001‘ then ‘<  1us‘        when ‘0.000010‘ then ‘< 10us‘        when ‘0.000100‘ then ‘<100us‘        when ‘0.001000‘ then ‘<  1ms‘        when ‘0.010000‘ then ‘< 10ms‘        when ‘0.100000‘ then ‘<100ms‘        when ‘1.000000‘ then ‘<   1s‘        when ‘10.000000‘ then ‘<  10s‘        when ‘100.000000‘ then ‘<100s‘        else ‘>100s‘    END as `RT area`,    CONCAT(ROUND(100*count/query_count,2),"%") as percent,    countFROM(    SELECT        c.count,        c.time,        (            SELECT SUM(a.count)            FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a            WHERE a.count != 0        ) as query_count    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c    WHERE c.count > 0) d;

其它監控項

除了上述的監控項，常見的還有如下常用的方法。

1. 是否可用

可以使用如下幾條命令來查看當前 MySQL 服務是否處於運行狀態。

mysqladmin -h 127.1 -u root -pnew-password pingmysqladmin -h 127.1 -u root -pnew-password status

2. 使用者管理

嚴禁對使用者的 “host” 部分採用 “%”，除非你想從世界任何一個地方登陸；預設不要使用 GRANT ALL ON . 給使用者過度賦權，

mysql> CREATE USER ‘user‘ IDENTIFIED BY ‘password‘;mysql> GRANT privileges TO ‘user‘@‘host‘ [WITH GRANT OPTION];mysql> FLUSH PRIVILEGS;mysql> GRANT privileges  TO ‘user‘@‘host‘ IDENTIFIED BY ‘password‘ [WITH GRANT OPTION];mysql> FLUSH PRIVILEGS;

3. 串連數是否正常

主要查看客戶是否有由於沒正確關閉串連而死掉的串連，有多少失敗的串連，是否有惡意串連等。

----- 查看串連數，root會看到所有，其它使用者只能看到自己的串連mysql> SHOW FULL PROCESSLIST;$ mysqladmin -h host -u user -p processlist----- 當前失敗串連數mysql> SHOW GLOBAL STATUS LIKE ‘aborted_connects‘;----- 由於客戶沒有正確關閉串連而死掉的串連數mysql> SHOW GLOBAL STATUS LIKE ‘aborted_clients‘;----- 最大串連數mysql> SHOW CLOBAL VARIABLES LIKE ‘max_connections‘;mysql> SHOW GLOBAL STATUS LIKE ‘max_connections‘;

4. 慢查詢日誌

慢查詢日誌對 SQL 調優來說是非常重要的，它記錄了超過指定時間 long_query_time 的查詢語句；一般只在需要時開啟。

MyISAM

在對 MyISAM 儲存引擎調優時，很多文章推薦使用 Key_read_requests 和 Key_reads 的比例作為調優的參考，來設定 key_buffer_size 參數的值，而實際上這是錯誤的，詳細可以參考 Why you should ignore MySQL’s key cache hit ratio 這篇文章。

簡單介紹如下。

rate VS. ratio

首先需要注意的是，這裡有兩個重要的概念：”miss rate” 一般是每秒 miss 的數目；”miss ratio” 表示從磁碟讀取和從 cache 讀取的比例，該參數沒有單位。

如下的兩個參數可以通過 SHOW GLOBAL STATUS 命令查看，官方文檔的解釋如下。

Key_read_requestsThe number of requests to read a key block from the cache.Key_readsThe number of physical reads of a key block from disk.

也就是說，兩者分別對應了：A) 從緩衝讀取索引的請求次數；B) 從磁碟讀取索引的請求次數。

NOTE: 實際上，Key_reads 統計的並非嚴格意義上的讀磁碟，嚴格來說應該是發送系統請求的次數。如果檔案系統中有緩衝的話，實際耗時就是系統調用，並沒有磁碟讀取的耗時。

很多人認為 Key_reads/Key_read_requests 越小越好，否則就應該增大 key_buffer_size 的設定，但通過計數器的比例來調優有兩個問題：

1. 比例並不顯示數量的絕對值大小，並不知道總共的請求有多少；
2. 計數器並沒有考慮時間因素。

假設有兩台機器，其 miss ratio 分別為 23% 和 0.1% ，因為沒有讀的請求量，很難判斷那台機器需要進行調優。比如，前者是 23/100，後者則是 10K/10M 。

參數指標

雖說 Key_read_requests 大比小好，但是對於系統調優而言，更有意義的應該是單位時間內的 Key_reads，通常可以通過 Key_reads / Uptime 計算；該參數可以通過如下命令得到：

$ mysqladmin ext -ri10 | grep Key_reads

其中第一行表示的是系統啟動後的總請求，在此可以忽略，下面的每行數值都表示 10 秒內的資料變化，這樣就可以大致評估每秒有多少的磁碟請求，而且可以根據你的磁碟效能進行評估是否合理。

NOTE: 命令裡的 mysqladmin ext 其實就是 mysqladmin extended-status，你甚至可以簡寫成 mysqladmin e 。

結論

通過 Key_reads / Uptime 替換 Key_reads / Key_read_requests 。

xtools

----- 標準資料統計，主要是命令次數的統計，transaction    ins  Com_insert                               (diff)    upd  Com_update                               (diff)    del  Com_delete                               (diff)    sel  Com_select                               (diff)    tps  Com_insert + Com_update + Com_delete     (diff)----- 線程處理，threads    run  Threads_running    con  Threads_connected    cre  Threads_created                          (diff)    cac  Threads_cached----- 網路位元組數，bytes   recv  Bytes_received                           (diff)   sent  Bytes_sent                               (diff)----- buffer pool的快取命中率         Innodb_buffer_pool_read_requests         邏輯讀總次數         Innodb_buffer_pool_reads                 物理讀總次數   read  Innodb_buffer_pool_read_requests         每秒讀請求(diff)   hits  (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests----- buffer pool頁的狀態，innodb bp pages status   data  Innodb_buffer_pool_pages_data            已經使用快取頁面數   free  Innodb_buffer_pool_pages_free            空閑快取頁面數  dirty  Innodb_buffer_pool_pages_dirty           髒頁數目  flush  Innodb_buffer_pool_pages_flushed         每秒重新整理頁數(diff)----- innoDB相關的操作，innodb rows status    ins  Innodb_rows_inserted                     (diff)    upd  Innodb_rows_updated                      (diff)    del  Innodb_rows_deleted                      (diff)   read  Innodb_rows_read                         (diff)----- 資料讀寫請求數，innodb data status  reads  Innodb_data_reads                        資料讀總次數(diff) writes  Innodb_data_writes                       資料寫的總次數(diff)   read  Innodb_data_read                         至此已經讀的資料量(diff)written  Innodb_data_written                      至此已經寫的資料量(diff)----- 日誌寫入磁碟請求，innodb log fsyncs  Innodb_os_log_fsyncs                     向記錄檔寫的總次數(diff)written  Innodb_os_log_written                    寫入記錄檔的位元組數(diff)

參考

可以參考官方文檔 Reference Manual - Server Status Variables，主要介紹各個監控項的含義。

Monitoring MySQL performance metrics，一篇很不錯的介紹 MySQL 監控項文章，包括上述的輸送量、執行效能、連結情況、緩衝池使用方式等。

Why you should ignore MySQL’s key cache hit ratio 這篇文章介紹了 MyISAM 緩衝的調優，其中的思想其它參數也可以考慮，也可以參考 本地文檔 。

 

原文:https://jin-yang.github.io/post/mysql-monitor.html

MySQL 監控指標