|
|
51CTO旗下网站
|
|
移动端

基于 Prometheus 的云上 MySQL 监控实践

MySQL 8.0是当前Oracle公司一直在大力宣传的新版本,从架构到性能均有显著变化,同时,随着kubernetes的普及,为更好的提升资源利用率,可以进行MySQL上云的探索。

作者:wt社区来源:twt企业IT社区|2020-05-18 12:04

一、背景

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文件部分内容:

  1. apiVersion: apps/v1 
  2. kind: StatefulSet 
  3. metadata: 
  4. ...... 
  5.       containers: 
  6.         - env: 
  7.             - name: TZ 
  8.               value: Asia/Shanghai 
  9.             - name: DATA_SOURCE_NAME 
  10.               value: 'exporter:userpassword@(localhost:3306)/' 
  11.             - name: TARGET 
  12.               value: 'http://localhost:9104/metrics' 
  13.           image: 'registry.paas.test.abc/library/mysqld-exporter-python:v5' 
  14.           imagePullPolicy: Always 
  15.           name: mysqld-python 
  16.           ports: 
  17.             - containerPort: 9000 
  18.               name: mysqld-python 
  19.               protocol: TCP 
  20.           resources: 
  21.             limits: 
  22.               cpu: '2' 
  23.               memory: 4Gi 
  24.           terminationMessagePath: /dev/termination-log 
  25.           terminationMessagePolicy: File 
  26.         - env: 
  27.             - name: TZ 
  28.               value: Asia/Shanghai 
  29.             - name: DATA_SOURCE_NAME 
  30.               value: 'testuser:userpassword@(localhost:3306)/' 
  31.           image: 'registry.paas.test.abc/library/mysqld-exporter:latest' 
  32.           imagePullPolicy: Always 
  33.           name: mysqld-exporter 
  34.           ports: 
  35.             - containerPort: 9104 
  36.               name: mysqld-exporter 
  37.               protocol: TCP 
  38.           resources: 
  39.             limits: 
  40.               cpu: '2' 
  41.               memory: 4Gi 
  42.           terminationMessagePath: /dev/termination-log 
  43.           terminationMessagePolicy: File 
  44. ...... 

5、Prometehus server设置target

  1. job_name: kubernetes-pods 
  2.   scrape_interval: 30s 
  3.   scrape_timeout: 10s 
  4.   metrics_path: /metrics 
  5.   scheme: http 
  6.   kubernetes_sd_configs: 
  7.   - api_server: null 
  8.     role: pod 
  9.     namespaces: 
  10.       names: [] 
  11.   relabel_configs: 
  12.   - source_labels: [__meta_kubernetes_pod_annotation_prometheus_io_scrape] 
  13.     separator: ; 
  14.     regex: "true" 
  15.     replacement: $1 
  16.     action: keep 
  17.   - source_labels: [__meta_kubernetes_pod_annotation_prometheus_io_path] 
  18.     separator: ; 
  19.     regex: (.+) 
  20.     target_label: __metrics_path__ 
  21.     replacement: $1 
  22.     actionreplace 
  23.   - source_labels: [__address__, __meta_kubernetes_pod_annotation_prometheus_io_port] 
  24.     separator: ; 
  25.     regex: ([^:]+)(?::\d+)?;(\d+) 
  26.     target_label: __address__ 
  27.     replacement: $1:$2 
  28.     actionreplace 
  29.   - separator: ; 
  30.     regex: __meta_kubernetes_pod_label_(.+) 
  31.     replacement: $1 
  32.     action: labelmap 
  33.   - source_labels: [__meta_kubernetes_namespace] 
  34.     separator: ; 
  35.     regex: (.*) 
  36.     target_label: kubernetes_namespace 
  37.     replacement: $1 
  38.     actionreplace 
  39.   - source_labels: [__meta_kubernetes_pod_name] 
  40.     separator: ; 
  41.     regex: (.*) 
  42.     target_label: kubernetes_pod_name 
  43.     replacement: $1 
  44.     actionreplace 

六、采集指标解释

  • 查询mysql上线时间
  1. mysql> show status like '%uptime%'
  2. +---------------------------+---------+ 
  3. | Variable_name             | Value   | 
  4. +---------------------------+---------+ 
  5. | Uptime                    | 1284686 | 
  6. | Uptime_since_flush_status | 1284686 | 
  7. +---------------------------+---------+ 

Uptime即为mysql上线时间,单位为秒,对应输出的监控指标为:Mysql_uptime。可以对监控指标运算得到相应时间单位,例如转为天数,mysql_uptime/60/60/24。

  • 查询mysql服务端口mysql
  1. mysql> show variables like 'port'
  2. +---------------+-------+ 
  3. | Variable_name | Value | 
  4. +---------------+-------+ 
  5. | port          | 3306  | 
  6. +---------------+-------+ 

对应输出监控指标为:mysql_global_variables_port

  • 查看mysql服务器是否在线

如果mysqld_exporter连接mysql服务器成功,表示服务器在线,否则表示离线状态,对应输出的监指标:mysql_up。数值为1表示在线,数值0表示离线。

查看数据库连接数

  1. mysql> show status like 'Threads%'
  2. +-------------------+-------+ 
  3. | Variable_name     | Value | 
  4. +-------------------+-------+ 
  5. | Threads_cached    | 2     | 
  6. | Threads_connected | 1     | 
  7. | Threads_created   | 3     | 
  8. | Threads_running   | 2     | 
  9. +-------------------+-------+ 
  10. mysql> show variables like '%max_connection%'
  11. +------------------------+-------+ 
  12. | Variable_name          | Value | 
  13. +------------------------+-------+ 
  14. | max_connections        | 151   | 
  15. | mysqlx_max_connections | 100   | 
  16. +------------------------+-------+ 
  17. mysql> show global status like 'max_used_connections'
  18. +----------------------+-------+ 
  19. | Variable_name        | Value | 
  20. +----------------------+-------+ 
  21. | Max_used_connections | 3     | 
  22. +----------------------+-------+ 

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。

  • 查看慢查询数量
  1. mysql> show global status like '%Slow_queries%'
  2. +---------------+-------+ 
  3. | Variable_name | Value | 
  4. +---------------+-------+ 
  5. | Slow_queries  | 0     | 
  6. +---------------+-------+ 

对应输出监控指标为:mysql_global_status_slow_queries

此指标为当前慢查询的总数,如果想要更精确的显示慢查询额状态,可以使用promQL,将监控指标显示为每秒慢查询的数量,可以如下所示:irate(mysql_global_status_slow_queries[5m]),显示5分钟内,每秒慢查询的数量。

  • 查询QPS
  1. mysql> show global status like 'questions';  
  2. +---------------+--------+ 
  3. | Variable_name | Value  | 
  4. +---------------+--------+ 
  5. | Questions     | 407158 | 
  6. +---------------+--------+ 

Questions:表示为收到的总请求的次数,对应输出的监控指标为:mysql_global_status_questions。如果想要得到没秒请求的数量,可以如下方法所示:

irate(mysql_global_status_questions[5m]),显示5分钟内每秒请求的数量,即QPS。

  • 查询innodb_buffer_pool命中率
  1. mysql> show global status like 'innodb_buffer_pool_read%'
  2. +---------------------------------------+-------+ 
  3. | Variable_name                         | Value | 
  4. +---------------------------------------+-------+ 
  5. | Innodb_buffer_pool_read_ahead_rnd     | 0     | 
  6. | Innodb_buffer_pool_read_ahead         | 0     | 
  7. | Innodb_buffer_pool_read_ahead_evicted | 0     | 
  8. | Innodb_buffer_pool_read_requests      | 19268 | 
  9. | Innodb_buffer_pool_reads              | 887   | 
  10. +---------------------------------------+-------+ 

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)。

  • 查询打开表的数量
  1. mysql> show global status like 'open_tables'
  2. +---------------+-------+ 
  3. | Variable_name | Value | 
  4. +---------------+-------+ 
  5. | Open_tables   | 371   | 
  6. +---------------+-------+ 

对应输出的监控指标为:mysql_global_status_open_tables

  • 查询表缓存命中率
  1. mysql> show global status like 'threads_created'
  2. +-----------------+-------+ 
  3. | Variable_name   | Value | 
  4. +-----------------+-------+ 
  5. | Threads_created | 3     | 
  6. +-----------------+-------+ 
  7. mysql> show global status like 'connections'
  8. +---------------+-------+ 
  9. | Variable_name | Value | 
  10. +---------------+-------+ 
  11. | Connections   | 33479 | 
  12. +---------------+-------+ 

Threads_created:表示创建过的线程数,对应输出的监控指标为:mysql_global_status_threads_created。

Connections:表示试图链接mysql服务器的次数,对应输出的监控指标为:mysql_global_status_connections。

表缓存命中率为:(1-mysql_global_status_threads_created/mysql_global_status_connections)*100 。

  • 查询锁状态
  1. mysql> show global status like 'table_locks%'
  2. +-----------------------+--------+ 
  3. | Variable_name         | Value  | 
  4. +-----------------------+--------+ 
  5. | Table_locks_immediate | 156335 | 
  6. | Table_locks_waited    | 0      | 
  7. +-----------------------+--------+ 

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。

  • 查询临时表状态
  1. mysql> show global status like '%tmp%'
  2. +-------------------------+--------+ 
  3. | Variable_name           | Value  | 
  4. +-------------------------+--------+ 
  5. | Created_tmp_disk_tables | 0      | 
  6. | Created_tmp_files       | 6      | 
  7. | Created_tmp_tables      | 111563 | 
  8. +-------------------------+--------+ 

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待应用的事务数
  1. SELECT  
  2.     @@GLOBAL .server_uuid, 
  3.     GTID_SUBTRACT(RECEIVED_TRANSACTION_SET, 
  4.             @@GLOBAL .GTID_EXECUTED) 
  5. FROM 
  6.     performance_schema.replication_connection_status 
  7. WHERE 
  8.     channel_name = 'group_replication_applier' 

对应输出的监控指标为:mysql_mgr_apply_queue

  • 查询当前mysql待认证的事务数
  1. SELECT  
  2.     MEMBER_ID, Count_Transactions_in_queue 
  3. FROM 
  4.     performance_schema.replication_group_member_stats 
  5. WHERE 
  6.     member_id = @@GLOBAL .server_uuid 

对应输出的监控指标为:mysql_mgr_cert_queue

  • 查寻当前mysql的节点状态,如果是online返回1,如果是offline返回2,如果是error返回3,如果是recovering返回4.
  1. SELECT  
  2.     member_id, 
  3.     CASE 
  4.         WHEN MEMBER_STATE = 'ONLINE' THEN 1 
  5.         WHEN MEMBER_STATE = 'OFFLINE' THEN 2 
  6.         WHEN MEMBER_STATE = 'ERROR' THEN 3 
  7.         WHEN MEMBER_STATE = 'RECOVERING' THEN 4 
  8.         ELSE 0 
  9.     END AS MEMBER_STATE 
  10. FROM 
  11.     performance_schema.replication_group_members 
  12. WHERE 
  13.     MEMBER_ID = @@GLOBAL .server_uuid 
  14.         OR MEMBER_ID = '' 

对应输出的监控指标为:mysql_mgr_node_status

  • 查询当前mysql节点的健康状态情况,如果online返回1,如果offline返回0。
  1. SELECT  
  2.     member_id, 
  3.     IF(MEMBER_STATE = 'ONLINE' 
  4.             AND ((SELECT  
  5.                 COUNT(*) 
  6.             FROM 
  7.                 performance_schema.replication_group_members 
  8.             WHERE 
  9.                 MEMBER_STATE != 'ONLINE') >= ((SELECT  
  10.                 COUNT(*) 
  11.             FROM 
  12.                 performance_schema.replication_group_members) / 2) = 0), 
  13.         '1'
  14.         '0'
  15. FROM 
  16.     performance_schema.replication_group_members 
  17.         JOIN 
  18.     performance_schema.replication_group_member_stats USING (member_id) 
  19. WHERE 
  20.     member_id = @@GLOBAL .server_uuid 

对应输出的监控指标为:mysql_mgr_node_health

  • 查询当前mysql节点角色情况,如果是主库返回1,如果是非主库返回0。
  1. SELECT  
  2.     @@server_uuid, 
  3.     IF(@@GLOBAL .group_replication_single_primary_mode, 
  4.         (SELECT  
  5.                 COUNT(1) 
  6.             FROM 
  7.                 performance_schema.global_status 
  8.             WHERE 
  9.                 variable_value = @@server_uuid), 
  10.         (SELECT  
  11.                 COUNT(1) 
  12.             FROM 
  13.                 performance_schema.replication_group_members 
  14.             WHERE 
  15.                 member_id = @@server_uuid 
  16.                     AND member_state = 'ONLINE')) AS isPrimary 

对应输出的监控指标为:mysql_mgr_role

  • 大事务查询
  1. SELECT  
  2.     @@GLOBAL .server_uuid, COUNT(trx_id) 
  3. FROM 
  4.     information_schema.INNODB_TRX, 
  5.     sys.session AS se 
  6. WHERE 
  7.     trx_mysql_thread_id = conn_id 

对应输出的监控指标为:mysql_big_trx,由于此查询需要调用MySQL系统sys相关视图,所以需要为exporter额外授权。

  1. grant usage on *.* to exporter@'%'
  2. grant select,execute on sys.* to exporter@'%'

七、grafana面板

grafana是一款带面板展示效果的开源应用。通过配置拉取prometheus服务器的指标数据,支持时序数据的查询及展示。拥有查询编译器功能,能够对时序数据运算后进行可视化展示。只需把prometheus提供的http服务配置即可。以下为MySQL监控指标图像化展示效果。

【编辑推荐】

  1. 一文解决所有MySQL分类排名问题
  2. MySQL使用规范手册,程序员必知必会
  3. 一文看懂MYSQL8 vs MYSQL5.7增加了哪些特性
  4. MySQL 8 新特性之降序索引底层实现
  5. MySQL如何实现Excel分组排序功能?
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

云计算从入门到上瘾

云计算从入门到上瘾

传统IT工程师的转型
共26章 | 51CTO阿森

92人订阅学习

从头解锁Python运维

从头解锁Python运维

多维度详解
共19章 | 叱诧少帅

332人订阅学习

Active Directory 架构规划实战

Active Directory 架构规划实战

4类AD架构规划
共15章 | wx5918591c0084b

322人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微