Prometheus + Granafa 构建MySQL监控平台( 四 ) _MySQL监控

# HELP mysql_global_status_innodb_buffer_pool_read_requests Generic metric from SHOW GLOBAL STATUS.# TYPE mysql_global_status_innodb_buffer_pool_read_requests untypedmysql_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 untypedmysql_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-rulesrules:- alert: MySQL Statusexpr: up == 0for: 5slabels:severity: warningannotations:summary: "{{$labels.instance}}: MySQL has stop !!!"description: "检测MySQL数据库运行状态"- alert: MySQL Slave IO Thread Statusexpr: mysql_slave_status_slave_io_running == 0for: 5slabels:severity: warningannotations:summary: "{{$labels.instance}}: MySQL Slave IO Thread has stop !!!"description: "检测MySQL主从IO线程运行状态"- alert: MySQL Slave SQL Thread Statusexpr: mysql_slave_status_slave_sql_running == 0for: 5slabels:severity: warningannotations:summary: "{{$labels.instance}}: MySQL Slave SQL Thread has stop !!!"description: "检测MySQL主从SQL线程运行状态"- alert: MySQL Slave Delay Statusexpr: mysql_slave_status_sql_delay == 30for: 5slabels:severity: warningannotations:summary: "{{$labels.instance}}: MySQL Slave Delay has more than 30s !!!"description: "检测MySQL主从延时状态"- alert: Mysql_Too_Many_Connectionsexpr: rate(mysql_global_status_threads_connected[5m]) > 200for: 2mlabels:severity: warningannotations:summary: "{{$labels.instance}}: 连接数过多"description: "{{$labels.instance}}: 连接数过多，请处理 ,(current value is: {{ $value }})"- alert: Mysql_Too_Many_slow_queriesexpr: rate(mysql_global_status_slow_queries[5m]) > 3for: 2mlabels:severity: warningannotations: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我们可以看到规则生效了：