基于 Prometheus 的云上 MySQL 監控實踐
一、背景
MySQL 8.0是當前Oracle公司一直在大力宣傳的新版本，從架構到性能均有顯著變化，同時，隨著kubernetes的普及，為更好的提升資源利用率，可以進行MySQL上云的探索。MySQL上云如何進行運行狀態的監控呢?MySQL運行狀態監控需要滿足：監控數據實時準備，報警機制響應迅速，支持異地集中監控。本文將探索云上MySQL的監控方案。
二、方案對比
方案一：
Zabbix監控系統，基于c+php開發的開源監控系統，支持多種監控采集方式，應用廣泛，支持比較成熟，社區活躍，缺點是對容器支持度比較差。
方案二：
Prometheus監控系統，基于go開發的開源監控系統，支持pull和push兩種采集模式，有完整的監控、報警、展示、數據異地傳輸能力，配置簡單，對容器支持良好。
由于目前使用的MySQL在云上部署，而且公司現有對PaaS云監控基于Promehteus，因此，方案二是更佳選項，既能滿足MySQL監控，又能充分利用現有資源。
三、Prometheus監控系統概述
Prometheus是由SoundeCloud公司基于go語言開發的一款開源的監控報警解決方案，基于時間序列監控數據。
1、組件及架構
組件：
- promethues server：主要獲取和存儲時間序列數據
- Exporters(導出器)：主要是作為agent收集數據發送到prometheus server，不同的數據收集由不同的exporters實
- pushgateway：允許短暫和批處理的jobs推送它們的數據到prometheus;然后由prometheus拉取數據。
- alertmanager：實現prometheus的告警功能。
組件間關系如下：
圖1 組件架構圖
2、Prometheus特點
- 指標收集：prometheus服務器定義了名為目標(target)的配置，執行抓取所需要的信息。
- 服務發現：可以通過通過多種方式來處理要監控的資源。包括：靜態資源列表、基于文件發現、自動發現。
- 聚合和報警：在服務器上可以查詢和聚合時間序列數據。通過規則記錄常用的查詢并做聚合。可以設置報警規則，滿足報警條件時會觸發報警，把報警信息推送的alertmanager。
- 自治：不依賴分布式存儲，單個服務器節點是自主的。
- 冗余和高可用性：可部署多臺prmehteus服務器，實現監控系統的高可用性。
- 查詢語言：prometheus服務器提供了查詢語言PromQL，用于對時序數據進行篩選和運算。
- 可視化：prometheus內置表達式瀏覽器可提供可視化，可與grafana配合實現監控數據可視化展示。
四、MySQL數據庫監控
1、監控方案
Prometheus官方提供了mysqld_exporter導出器，可實現對MySQL監控。該導出器通過MySQL用戶連接數據庫，查詢相關數據庫表、狀態信息，通過http服務的方式暴露監控數據。
方案不足：導出器可實現單節點和主從復制相關監控項，但對于MGR模式相關監控目前還不能很好地支持。
方案改進：prometheus提供了client libraries，可實現對監控指標進行定制化采集。故可用python語言定制腳本的方式采集MGR相關數據。mysqld_exporter與python腳本能夠滿足全部監控信息的導出。
2、部署方案
關于Paas云上MySQL監控部署，有兩種方案：
方案一：
MySQL、mysqld_exporter、my_exporter_python監控腳本三部分同在一個鏡像中，運行該容器可實現對MySQL的監控。
方案二：
MySQL、mysqld_exporter、my_exporter_python監控腳本分別屬于不同的鏡像，MySQL主容器與監控容器按順序運行。監控容器以sidecar的方式訪問MySQL。
方案對比：
MySQL數據庫服務對于應用是非常重要的一環，要確保MySQL安全可靠。方案一，如果MySQL異常或出現錯誤，對問題診斷與排錯方面，監控導出器可能會干擾項，不利于后期MySQL運維管理。方案二，由于三部分在不同的容器中運行，不會產生互相干擾的可能性，因此方案二為最佳。
五、監控具體實現
1、創建MySQL監控用戶并授權
2、my_exporter_python腳本說明
9000端口提供http提供服務
start_http_server(9000)
設置Gauge對象
連接接MySQL查詢數據
設置MGR相關的metrics
3、鏡像拉取與定制
mysqld_exporter鏡像pull：
docker pull prom/mysqld_exporter
my_exporter_python鏡像制作
Dockerfile內容
FROM centos7_python36:v1
RUN pip install prometheus_client pymysql
RUN pip install requests
COPY ./my_exporter_python_v2.py /my_exporter_python_v2.py
WORKDIR /
EXPOSE 9000
CMD ["python","my_exporter_python_v2.py"]
4、鏡像部署yaml文件部分內容：
- apiVersion: apps/v1
- kind: StatefulSet
- metadata:
- ......
- containers:
- - env:
- - name: TZ
- value: Asia/Shanghai
- - name: DATA_SOURCE_NAME
- value: 'exporter:userpassword@(localhost:3306)/'
- - name: TARGET
- value: 'http://localhost:9104/metrics'
- image: 'registry.paas.test.abc/library/mysqld-exporter-python:v5'
- imagePullPolicy: Always
- name: mysqld-python
- ports:
- - containerPort: 9000
- name: mysqld-python
- protocol: TCP
- resources:
- limits:
- cpu: '2'
- memory: 4Gi
- terminationMessagePath: /dev/termination-log
- terminationMessagePolicy: File
- - env:
- - name: TZ
- value: Asia/Shanghai
- - name: DATA_SOURCE_NAME
- value: 'testuser:userpassword@(localhost:3306)/'
- image: 'registry.paas.test.abc/library/mysqld-exporter:latest'
- imagePullPolicy: Always
- name: mysqld-exporter
- ports:
- - containerPort: 9104
- name: mysqld-exporter
- protocol: TCP
- resources:
- limits:
- cpu: '2'
- memory: 4Gi
- terminationMessagePath: /dev/termination-log
- terminationMessagePolicy: File
- ......
5、Prometehus server設置target
- job_name: kubernetes-pods
- scrape_interval: 30s
- scrape_timeout: 10s
- metrics_path: /metrics
- scheme: http
- kubernetes_sd_configs:
- - api_server: null
- role: pod
- namespaces:
- names: []
- relabel_configs:
- - source_labels: [__meta_kubernetes_pod_annotation_prometheus_io_scrape]
- separator: ;
- regex: "true"
- replacement: $1
- action: keep
- - source_labels: [__meta_kubernetes_pod_annotation_prometheus_io_path]
- separator: ;
- regex: (.+)
- target_label: __metrics_path__
- replacement: $1
- action: replace
- - source_labels: [__address__, __meta_kubernetes_pod_annotation_prometheus_io_port]
- separator: ;
- regex: ([^:]+)(?::\d+)?;(\d+)
- target_label: __address__
- replacement: $1:$2
- action: replace
- - separator: ;
- regex: __meta_kubernetes_pod_label_(.+)
- replacement: $1
- action: labelmap
- - source_labels: [__meta_kubernetes_namespace]
- separator: ;
- regex: (.*)
- target_label: kubernetes_namespace
- replacement: $1
- action: replace
- - source_labels: [__meta_kubernetes_pod_name]
- separator: ;
- regex: (.*)
- target_label: kubernetes_pod_name
- replacement: $1
- action: replace
六、采集指標解釋
- 查詢mysql上線時間
- mysql> show status like '%uptime%';
- +---------------------------+---------+
- | Variable_name | Value |
- +---------------------------+---------+
- | Uptime | 1284686 |
- | Uptime_since_flush_status | 1284686 |
- +---------------------------+---------+
Uptime即為mysql上線時間，單位為秒，對應輸出的監控指標為：Mysql_uptime。可以對監控指標運算得到相應時間單位，例如轉為天數，mysql_uptime/60/60/24。
- 查詢mysql服務端口mysql
- mysql> show variables like 'port';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | port | 3306 |
- +---------------+-------+
對應輸出監控指標為：mysql_global_variables_port
- 查看mysql服務器是否在線
如果mysqld_exporter連接mysql服務器成功，表示服務器在線，否則表示離線狀態，對應輸出的監指標：mysql_up。數值為1表示在線，數值0表示離線。
查看數據庫連接數
- mysql> show status like 'Threads%';
- +-------------------+-------+
- | Variable_name | Value |
- +-------------------+-------+
- | Threads_cached | 2 |
- | Threads_connected | 1 |
- | Threads_created | 3 |
- | Threads_running | 2 |
- +-------------------+-------+
- mysql> show variables like '%max_connection%';
- +------------------------+-------+
- | Variable_name | Value |
- +------------------------+-------+
- | max_connections | 151 |
- | mysqlx_max_connections | 100 |
- +------------------------+-------+
- mysql> show global status like 'max_used_connections';
- +----------------------+-------+
- | Variable_name | Value |
- +----------------------+-------+
- | Max_used_connections | 3 |
- +----------------------+-------+
Thread_connected：表示打開的鏈接數，對應輸出的監控指標為：mysql_global_status_threads_connected。
Threads_running：表示激活的連接數，并發數，對應輸出的監控指標為：mysql_global_status_threads_running。
max_used_connections：表示當前使用過的最大連接數，對應輸出的監控指標為：mysql_global_status_max_used_connections。
max_connections：表示并發執行的最大連接數，對應輸出的監控指標為：mysql_global_variables_max_connections。
- 查看慢查詢數量
- mysql> show global status like '%Slow_queries%';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Slow_queries | 0 |
- +---------------+-------+
對應輸出監控指標為：mysql_global_status_slow_queries
此指標為當前慢查詢的總數，如果想要更精確的顯示慢查詢額狀態，可以使用promQL，將監控指標顯示為每秒慢查詢的數量，可以如下所示：irate(mysql_global_status_slow_queries[5m])，顯示5分鐘內，每秒慢查詢的數量。
- 查詢QPS
- mysql> show global status like 'questions';
- +---------------+--------+
- | Variable_name | Value |
- +---------------+--------+
- | Questions | 407158 |
- +---------------+--------+
Questions：表示為收到的總請求的次數，對應輸出的監控指標為：mysql_global_status_questions。如果想要得到沒秒請求的數量，可以如下方法所示：
irate(mysql_global_status_questions[5m])，顯示5分鐘內每秒請求的數量，即QPS。
- 查詢innodb_buffer_pool命中率
- mysql> show global status like 'innodb_buffer_pool_read%';
- +---------------------------------------+-------+
- | Variable_name | Value |
- +---------------------------------------+-------+
- | Innodb_buffer_pool_read_ahead_rnd | 0 |
- | Innodb_buffer_pool_read_ahead | 0 |
- | Innodb_buffer_pool_read_ahead_evicted | 0 |
- | Innodb_buffer_pool_read_requests | 19268 |
- | Innodb_buffer_pool_reads | 887 |
- +---------------------------------------+-------+
Innodb_buffer_pool_reads：表示直接從磁盤讀的次數，對應輸出的監控指標為：
mysql_global_status_innodb_buffer_pool_reads。
Innodb_buffer_pool_read_requests：表示邏輯讀的次數，
對應輸出的監控指標為：
mysql_global_status_innodb_buffer_pool_read_requests。
計算邏輯讀的命中率，公式為：100 - 100 * (mysql_global_status_innodb_buffer_pool_reads/
mysql_global_status_innodb_buffer_pool_read_requests)。
計算邏輯讀的命中率，公式為：100 - 100 * (mysql_global_status_innodb_buffer_pool_reads/
mysql_global_status_innodb_buffer_pool_read_requests)。
- 查詢打開表的數量
- mysql> show global status like 'open_tables';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Open_tables | 371 |
- +---------------+-------+
對應輸出的監控指標為：mysql_global_status_open_tables
- 查詢表緩存命中率
- mysql> show global status like 'threads_created';
- +-----------------+-------+
- | Variable_name | Value |
- +-----------------+-------+
- | Threads_created | 3 |
- +-----------------+-------+
- mysql> show global status like 'connections';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Connections | 33479 |
- +---------------+-------+
Threads_created：表示創建過的線程數，對應輸出的監控指標為：mysql_global_status_threads_created。
Connections：表示試圖鏈接mysql服務器的次數，對應輸出的監控指標為：mysql_global_status_connections。
表緩存命中率為：(1-mysql_global_status_threads_created/mysql_global_status_connections)*100 。
- 查詢鎖狀態
- mysql> show global status like 'table_locks%';
- +-----------------------+--------+
- | Variable_name | Value |
- +-----------------------+--------+
- | Table_locks_immediate | 156335 |
- | Table_locks_waited | 0 |
- +-----------------------+--------+
Table_locks_immediate：表示行鎖總數量，對應輸出監控指標為：mysql_global_status_table_locks_immediate，可以計算每秒行鎖數量，如：
irate(mysql_global_status_table_locks_immediate[5m])。
Table_locks_waited 表示為表鎖數量，對應輸出監控指標為：mysql_global_status_table_locks_waited。
- 查詢臨時表狀態
- mysql> show global status like '%tmp%';
- +-------------------------+--------+
- | Variable_name | Value |
- +-------------------------+--------+
- | Created_tmp_disk_tables | 0 |
- | Created_tmp_files | 6 |
- | Created_tmp_tables | 111563 |
- +-------------------------+--------+
Created_tmp_disk_tables：表示為創建磁盤臨時表數量，對應輸出監控指標為：mysql_global_status_created_tmp_disk_tables。
Created_tmp_tables：表示服務器內部創建臨時表的數量，對應輸出指標為：mysql_global_status_created_tmp_tables。
臨時表比例計算為：
mysql_global_status_created_tmp_disk_tables/mysql_global_status_created_tmp_tables。
python腳本實現監控指標及sql語句
mysql組復制相關信息記錄在performance_schema庫中的replicaion_conection_status、replication_group_member_stats、replication_group_members表中，通過關聯查詢能夠得到組復制相關的監控項
- 查詢當前mysql待應用的事務數
- SELECT
- @@GLOBAL .server_uuid,
- GTID_SUBTRACT(RECEIVED_TRANSACTION_SET,
- @@GLOBAL .GTID_EXECUTED)
- FROM
- performance_schema.replication_connection_status
- WHERE
- channel_name = 'group_replication_applier'
對應輸出的監控指標為：mysql_mgr_apply_queue
- 查詢當前mysql待認證的事務數
- SELECT
- MEMBER_ID, Count_Transactions_in_queue
- FROM
- performance_schema.replication_group_member_stats
- WHERE
- member_id = @@GLOBAL .server_uuid
對應輸出的監控指標為：mysql_mgr_cert_queue
- 查尋當前mysql的節點狀態，如果是online返回1，如果是offline返回2，如果是error返回3，如果是recovering返回4.
- SELECT
- member_id,
- CASE
- WHEN MEMBER_STATE = 'ONLINE' THEN 1
- WHEN MEMBER_STATE = 'OFFLINE' THEN 2
- WHEN MEMBER_STATE = 'ERROR' THEN 3
- WHEN MEMBER_STATE = 'RECOVERING' THEN 4
- ELSE 0
- END AS MEMBER_STATE
- FROM
- performance_schema.replication_group_members
- WHERE
- MEMBER_ID = @@GLOBAL .server_uuid
- OR MEMBER_ID = ''
對應輸出的監控指標為：mysql_mgr_node_status
- 查詢當前mysql節點的健康狀態情況，如果online返回1，如果offline返回0。
- SELECT
- member_id,
- IF(MEMBER_STATE = 'ONLINE'
- AND ((SELECT
- COUNT(*)
- FROM
- performance_schema.replication_group_members
- WHERE
- MEMBER_STATE != 'ONLINE') >= ((SELECT
- COUNT(*)
- FROM
- performance_schema.replication_group_members) / 2) = 0),
- '1',
- '0')
- FROM
- performance_schema.replication_group_members
- JOIN
- performance_schema.replication_group_member_stats USING (member_id)
- WHERE
- member_id = @@GLOBAL .server_uuid
對應輸出的監控指標為：mysql_mgr_node_health
- 查詢當前mysql節點角色情況，如果是主庫返回1，如果是非主庫返回0。
- SELECT
- @@server_uuid,
- IF(@@GLOBAL .group_replication_single_primary_mode,
- (SELECT
- COUNT(1)
- FROM
- performance_schema.global_status
- WHERE
- variable_value = @@server_uuid),
- (SELECT
- COUNT(1)
- FROM
- performance_schema.replication_group_members
- WHERE
- member_id = @@server_uuid
- AND member_state = 'ONLINE')) AS isPrimary
對應輸出的監控指標為：mysql_mgr_role
- 大事務查詢
- SELECT
- @@GLOBAL .server_uuid, COUNT(trx_id)
- FROM
- information_schema.INNODB_TRX,
- sys.session AS se
- WHERE
- trx_mysql_thread_id = conn_id
對應輸出的監控指標為：mysql_big_trx，由于此查詢需要調用MySQL系統sys相關視圖，所以需要為exporter額外授權。
- grant usage on *.* to exporter@'%';
- grant select,execute on sys.* to exporter@'%';
七、grafana面板
grafana是一款帶面板展示效果的開源應用。通過配置拉取prometheus服務器的指標數據，支持時序數據的查詢及展示。擁有查詢編譯器功能，能夠對時序數據運算后進行可視化展示。只需把prometheus提供的http服務配置即可。以下為MySQL監控指標圖像化展示效果。
