MySQL監控平臺的構建方法

概述

對于MySQL的監控平臺，相信大家實現起來有很多了：基于天兔的監控，還有基于zabbix相關的二次開發。相信很多同行都應該已經開始玩起來了。我這邊的選型是prometheus + granafa的實現方式。簡而言之就是我現在的生產環境使用的是prometheus，還有就是granafa滿足的我的日常工作需要。在入門的簡介和安裝，大家可以參考這里：https://blog.51cto.com/cloumn/detail/77

1、首先看下我們的監控效果、mysql主從

2、mysql狀態：

3、緩沖池狀態：

exporter 相關部署

1、安裝exporter

[root@controller2 opt]# https://github.com/prometheus/mysqld_exporter/releases/download/v0.10.0/mysqld_exporter-0.10.0.linux-amd64.tar.gz
[root@controller2 opt]# tar -xf mysqld_exporter-0.10.0.linux-amd64.tar.gz 

2、添加mysql 賬戶：

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'exporter'@'%' IDENTIFIED BY 'localhost';
flush privileges;

3、編輯配置文件：

[root@controller2 mysqld_exporter-0.10.0.linux-amd64]# cat /opt/mysqld_exporter-0.10.0.linux-amd64/.my.cnf 
[client]
user=exporter
password=123456

4、設置配置文件：

[root@controller2 mysqld_exporter-0.10.0.linux-amd64]# cat /etc/systemd/system/mysql_exporter.service 
[Unit]
Description=mysql Monitoring System
Documentation=mysql Monitoring System

[Service]
ExecStart=/opt/mysqld_exporter-0.10.0.linux-amd64/mysqld_exporter \
         -collect.info_schema.processlist \
         -collect.info_schema.innodb_tablespaces \
         -collect.info_schema.innodb_metrics  \
         -collect.perf_schema.tableiowaits \
         -collect.perf_schema.indexiowaits \
         -collect.perf_schema.tablelocks \
         -collect.engine_innodb_status \
         -collect.perf_schema.file_events \
         -collect.info_schema.processlist \
         -collect.binlog_size \
         -collect.info_schema.clientstats \
         -collect.perf_schema.eventswaits \
         -config.my-cnf=/opt/mysqld_exporter-0.10.0.linux-amd64/.my.cnf

[Install]
WantedBy=multi-user.target

5、添加配置到prometheus server

  - job_name: 'mysql'
    static_configs:
     - targets: ['192.168.1.11:9104','192.168.1.12:9104']

6、測試看有沒有返回數值：

http://192.168.1.12:9104/metrics

正常我們通過mysql_up可以查詢倒mysql監控是否已經生效，是否起起來

#HELP mysql_up Whether the MySQL server is up.
#TYPE mysql_up gauge
mysql_up 1

監控相關指標

在做任何一個東西監控的時候，我們要時刻明白我們要監控的是什么，指標是啥才能更好的去監控我們的服務，在mysql里面我們通常可以通過一下指標去衡量mysql的運行情況：mysql主從運行情況、查詢吞吐量、慢查詢情況、連接數情況、緩沖池使用情況以及查詢執行性能等。

主從復制運行指標：

1、主從復制線程監控：

大部分情況下，很多企業使用的都是主從復制的環境，監控兩個線程是非常重要的，在mysql里面我們通常是通過命令：

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000045
          Read_Master_Log_Pos: 72904854
               Relay_Log_File: mariadb-relay-bin.000127
                Relay_Log_Pos: 72905142
        Relay_Master_Log_File: mysql-bin.000045
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#Slave_IO_Running、Slave_SQL_Running兩個線程正常那么說明我們的復制集群是健康狀態的。

MySQLD Exporter中返回的樣本數據中通過mysql_slave_status_slave_sql_running來獲取主從集群的健康狀況。

# HELP mysql_slave_status_slave_sql_running Generic metric from SHOW SLAVE STATUS.
# TYPE mysql_slave_status_slave_sql_running untyped
mysql_slave_status_slave_sql_running{channel_name="",connection_name="",master_host="172.16.1.1",master_uuid=""} 1

2、主從復制落后時間：

在使用show slave status 里面還有一個關鍵的參數Seconds_Behind_Master。Seconds_Behind_Master表示slave上SQL thread與IO thread之間的延遲，我們都知道在MySQL的復制環境中，slave先從master上將binlog拉取到本地（通過IO thread），然后通過SQL thread將binlog重放，而Seconds_Behind_Master表示本地relaylog中未被執行完的那部分的差值。所以如果slave拉取到本地的relaylog（實際上就是binlog，只是在slave上習慣稱呼relaylog而已）都執行完，此時通過show slave status看到的會是0

Seconds_Behind_Master: 0

MySQLD Exporter中返回的樣本數據中通過mysql_slave_status_seconds_behind_master 來獲取相關狀態。

# HELP mysql_slave_status_seconds_behind_master Generic metric from SHOW SLAVE STATUS.
# TYPE mysql_slave_status_seconds_behind_master untyped
mysql_slave_status_seconds_behind_master{channel_name="",connection_name="",master_host="172.16.1.1",master_uuid=""} 0

查詢吞吐量：

說到吞吐量，那么我們如何從那方面來衡量呢？
通常來說我們可以根據mysql 的插入、查詢、刪除、更新等操作來

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

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE "Questions";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 15071 |
+---------------+-------+

MySQLD Exporter中返回的樣本數據中通過mysql_global_status_questions反映當前Questions計數器的大小：

# HELP mysql_global_status_questions Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_questions untyped
mysql_global_status_questions 13253

當然由于prometheus 具有非常豐富的查詢語言，我們可以通過這個累加的計數器來查詢某一短時間內的查詢增長率情況，可以做相關的閾值告警處理、例如一下查詢2分鐘時間內的查詢情況：

rate(mysql_global_status_questions[2m])

當然上面是總量，我們可以分別從監控讀、寫指令的分解情況，從而更好地理解數據庫的工作負載、找到可能的瓶頸。通常，通常，讀取查詢會由 Com_select 指標抓取，而寫入查詢則可能增加三個狀態變量中某一個的值，這取決于具體的指令：

Writes = Com_insert + Com_update + Com_delete

下面我們通過命令獲取插入的情況：

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE "Com_insert";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 10578 |
+---------------+-------+

從MySQLD Exporter的/metrics返回的監控樣本中，可以通過global_status_commands_total獲取當前實例各類指令執行的次數：

# HELP mysql_global_status_commands_total Total number of executed MySQL commands.
# TYPE mysql_global_status_commands_total counter
mysql_global_status_commands_total{command="create_trigger"} 0
mysql_global_status_commands_total{command="create_udf"} 0
mysql_global_status_commands_total{command="create_user"} 1
mysql_global_status_commands_total{command="create_view"} 0
mysql_global_status_commands_total{command="dealloc_sql"} 0
mysql_global_status_commands_total{command="delete"} 3369
mysql_global_status_commands_total{command="delete_multi"} 0

慢查詢性能

查詢性能方面，慢查詢也是查詢告警的一個重要的指標。MySQL還提供了一個Slow_queries的計數器，當查詢的執行時間超過long_query_time的值后，計數器就會+1，其默認值為10秒，可以通過以下指令在MySQL中查詢當前long_query_time的設置：

MariaDB [(none)]> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

#當然我們也可以修改時間

MariaDB [(none)]> SET GLOBAL long_query_time = 5;
Query OK, 0 rows affected (0.00 sec)

然后我們而已通過sql語言查詢MySQL實例中Slow_queries的數量：

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE "Slow_queries";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

MySQLD Exporter返回的樣本數據中，通過mysql_global_status_slow_queries指標展示當前的Slow_queries的值：

# HELP mysql_global_status_slow_queries Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_slow_queries untyped
mysql_global_status_slow_queries 0

同樣的，更具根據Prometheus 慢查詢語句我們也可以查詢倒他某段時間內的增長率：

rate(mysql_global_status_slow_queries[5m])

連接數監控

監控客戶端連接情況相當重要，因為一旦可用連接耗盡，新的客戶端連接就會遭到拒絕。MySQL 默認的連接數限制為 151。

MariaDB [(none)]> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

當然我們可以修改配置文件的形式來增加這個數值。與之對應的就是當前連接數量，當我們當前連接出來超過系統設置的最大值之后常會出現我們看到的Too many connections(連接數過多)，下面我查找一下當前連接數：

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE "Threads_connected";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 41     |
+-------------------+-------

當然mysql 還提供Threads_running 這個指標，幫助你分隔在任意時間正在積極處理查詢的線程與那些雖然可用但是閑置的連接。

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE "Threads_running";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 10     |
+-----------------+-------+

如果服務器真的達到 max_connections 限制，它就會開始拒絕新的連接。在這種情況下，Connection_errors_max_connections 指標就會開始增加，同時，追蹤所有失敗連接嘗試的Aborted_connects 指標也會開始增加。

MySQLD Exporter返回的樣本數據中:

# HELP mysql_global_variables_max_connections Generic gauge metric from SHOW GLOBAL VARIABLES.
# TYPE mysql_global_variables_max_connections gauge
mysql_global_variables_max_connections 151         

#表示最大連接數

# HELP mysql_global_status_threads_connected Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_threads_connected untyped
mysql_global_status_threads_connected 41

#表示當前的連接數

# HELP mysql_global_status_threads_running Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_threads_running untyped
mysql_global_status_threads_running 1

#表示當前活躍的連接數

# HELP mysql_global_status_aborted_connects Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_aborted_connects untyped
mysql_global_status_aborted_connects 31

#累計所有的連接數

# HELP mysql_global_status_connection_errors_total Total number of MySQL connection errors.
# TYPE mysql_global_status_connection_errors_total counter
mysql_global_status_connection_errors_total{error="internal"} 0
#服務器內部引起的錯誤、如內存硬盤等
mysql_global_status_connection_errors_total{error="max_connections"} 0
#超出連接處引起的錯誤

當然根據prom表達式，我們可以查詢當前剩余可用的連接數：

mysql_global_variables_max_connections - mysql_global_status_threads_connected

查詢mysq拒絕連接數

mysql_global_status_aborted_connects

緩沖池情況：

MySQL 默認的存儲引擎 InnoDB 使用了一片稱為緩沖池的內存區域，用于緩存數據表與索引的數據。緩沖池指標屬于資源指標，而非工作指標，前者更多地用于調查（而非檢測）性能問題。如果數據庫性能開始下滑，而磁盤 I/O 在不斷攀升，擴大緩沖池往往能帶來性能回升。
默認設置下，緩沖池的大小通常相對較小，為 128MiB。不過，MySQL 建議可將其擴大至專用數據庫服務器物理內存的 80% 大小。我們可以查看一下：

MariaDB [(none)]> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

MySQLD Exporter返回的樣本數據中，使用mysql_global_variables_innodb_buffer_pool_size來表示。

# HELP mysql_global_variables_innodb_buffer_pool_size Generic gauge metric from SHOW GLOBAL VARIABLES.
# TYPE mysql_global_variables_innodb_buffer_pool_size gauge
mysql_global_variables_innodb_buffer_pool_size 1.34217728e+08

Innodb_buffer_pool_read_requests記錄了正常從緩沖池讀取數據的請求數量。可以通過以下指令查看

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_read_requests";
+----------------------------------+-------------+
| Variable_name                    | Value       |
+----------------------------------+-------------+
| Innodb_buffer_pool_read_requests | 38465 |
+----------------------------------+-------------+

MySQLD Exporter返回的樣本數據中，使用mysql_global_status_innodb_buffer_pool_read_requests來表示。

# HELP mysql_global_status_innodb_buffer_pool_read_requests Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_innodb_buffer_pool_read_requests untyped
mysql_global_status_innodb_buffer_pool_read_requests 2.7711547168e+10

當緩沖池無法滿足時，MySQL只能從磁盤中讀取數據。Innodb_buffer_pool_reads即記錄了從磁盤讀取數據的請求數量。通常來說從內存中讀取數據的速度要比從磁盤中讀取快很多，因此，如果Innodb_buffer_pool_reads的值開始增加，可能意味著數據庫的性能有問題。 可以通過以下只能查看Innodb_buffer_pool_reads的數量

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 138  |
+--------------------------+-------+
1 row in set (0.00 sec)

MySQLD Exporter返回的樣本數據中，使用mysql_global_status_innodb_buffer_pool_read_requests來表示。

# HELP mysql_global_status_innodb_buffer_pool_reads Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_innodb_buffer_pool_reads untyped
mysql_global_status_innodb_buffer_pool_reads 138

通過以上監控指標，以及實際監控的場景，我們可以利用PromQL快速建立多個監控項。可以查看兩分鐘內讀取磁盤的增長率的增長率：

rate(mysql_global_status_innodb_buffer_pool_reads[2m])

官方模板ID

上面是我們簡單列舉的一些指標，下面我們使用granafa給 MySQLD_Exporter添加監控圖表：

• 主從主群監控(模板7371)：
• 相關mysql 狀態監控7362：
• 緩沖池狀態7365：

• 簡單的告警規則

  除了相關模板之外，沒有告警規則那么我們的監控就是不完美的，下面列一下我們的監控告警規則

groups:
- name: MySQL-rules
  rules:
  - alert: MySQL Status 
    expr: up == 0
    for: 5s 
    labels:
      severity: warning
    annotations:
      summary: "{{$labels.instance}}: MySQL has stop !!!"
      description: "檢測MySQL數據庫運行狀態"

  - alert: MySQL Slave IO Thread Status
    expr: mysql_slave_status_slave_io_running == 0
    for: 5s 
    labels:
      severity: warning
    annotations: 
      summary: "{{$labels.instance}}: MySQL Slave IO Thread has stop !!!"
      description: "檢測MySQL主從IO線程運行狀態"

  - alert: MySQL Slave SQL Thread Status 
    expr: mysql_slave_status_slave_sql_running == 0
    for: 5s 
    labels:
      severity: warning
    annotations: 
      summary: "{{$labels.instance}}: MySQL Slave SQL Thread has stop !!!"
      description: "檢測MySQL主從SQL線程運行狀態"

  - alert: MySQL Slave Delay Status 
    expr: mysql_slave_status_sql_delay == 30
    for: 5s 
    labels:
      severity: warning
    annotations: 
      summary: "{{$labels.instance}}: MySQL Slave Delay has more than 30s !!!"
      description: "檢測MySQL主從延時狀態"

  - alert: Mysql_Too_Many_Connections
    expr: rate(mysql_global_status_threads_connected[5m]) > 200
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "{{$labels.instance}}: 連接數過多"
      description: "{{$labels.instance}}: 連接數過多，請處理 ,(current value is: {{ $value }})"  

  - alert: Mysql_Too_Many_slow_queries
    expr: rate(mysql_global_status_slow_queries[5m]) > 3
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "{{$labels.instance}}: 慢查詢有點多，請檢查處理"
      description: "{{$labels.instance}}: Mysql slow_queries is more than 3 per second ,(current value is: {{ $value }})"

2、添加規則到prometheus：

rule_files:
  - "rules/*.yml" 

3、打開web ui我們可以看到規則生效了：

總結

到處監控mysql的相關狀態已經完成，大家可以根據mysql更多的監控指標去完善自己的監控，當然這一套就是我用在線上環境的，可以參考參考。