MySQL并发监控_构建高大上的MySQL监控平台

左手的ㄟ右手 2023-01-11 01:25 136阅读 0赞

概述

对于MySQL的监控平台，相信大家实现起来有很多了：基于天兔的监控，还有基于zabbix相关的二次开发。相信很多同行都应该已经开始玩起来了。我这边的选型是prometheus + granafa的实现方式。简而言之就是我现在的生产环境使用的是prometheus，还有就是granafa满足的我的日常工作需要。在入门的简介和安装，大家可以参考这里：https://blog.51cto.com/cloumn/detail/77

1、首先看下我们的监控效果、mysql主从

![e0d4dc416113072fb72566d208019edf.png][]

2、mysql状态：

![72862a62dfe58535b4b5131675d5055e.png][]

![d5e00ae9e151851699fbcf3eabf429a7.png][]

3、缓冲池状态：

![1eb3b7e2317c4d62ae2d76937cf344b1.png][]

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、测试看有没有返回数值：

正常我们通过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快速建立多个监控项。可以查看两分钟内读取磁盘的增长率的增长率：