对于当前数据库的监控方式有很多,分为数据库自带、商用、开源三大类,每一种都有各自的特色;而对于MySQL数据库,由于其有很高的社区活跃度,监控方式更是多种多样,不管哪种监控方式最核心的就是监控数据,获取得到全面的监控数据后就是灵活的展示部分。
那我们今天就介绍一下完全采用MySQL自有方式采集获取监控数据,在单体下达到最快速、方便、损耗最小。
本次文章完全使用MySQL自带的show命令实现获取,从connectsbuffercachelockSQL SlowstatementDatabase throughputsserverconfig这7大方面全面获取监控数据。

Connects(连接数)

最大使用连接数:show status like 'Max_used_connections';

mysql> show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 38    |
+----------------------+-------+

当前打开的连接数:show status like 'Threads_connected';

mysql> show status like 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 20    |
+-------------------+-------+

bufferCache(缓存)

未从缓冲池读取的次数:show status like 'Innodb_buffer_pool_reads';

mysql> show status like 'Innodb_buffer_pool_reads';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Innodb_buffer_pool_reads | 152992 |
+--------------------------+--------+

从缓冲池读取的次数:show status like 'Innodb_buffer_pool_read_requests';

mysql> show status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| Innodb_buffer_pool_read_requests | 8004086499 |
+----------------------------------+------------+

缓冲池的总页数:show status like 'Innodb_buffer_pool_pages_total';

mysql> show status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 49152 |
+--------------------------------+-------+

缓冲池空闲的页数:show status like 'Innodb_buffer_pool_pages_free';

mysql> show status like 'Innodb_buffer_pool_pages_free';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_free | 1024  |
+-------------------------------+-------+

缓存命中率计算公式:
(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%

缓存池使用率公式:
((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free)/Innodb_buffer_pool_pages_total)*100%

lock(锁)

锁等待个数:show status like 'Innodb_row_lock_waits';

mysql> show status like 'Innodb_row_lock_waits';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Innodb_row_lock_waits | 187890 |
+-----------------------+--------+

平均每次锁等待时间:show status like 'Innodb_row_lock_time_avg';

mysql> show status like 'Innodb_row_lock_time_avg';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_row_lock_time_avg | 5     |
+--------------------------+-------+

查看是否存在表锁:show open TABLES where in_use>0;

mysql> show open TABLES where in_use>0;
Empty set (0.00 sec)

有数据代表存在锁表,空为无表锁!

备注:锁等待统计的数量为累加数据,每次获取得时候可以跟之前得数据进行相减,得到当前统计得数据

SQL Slow

慢 SQL 指的是MySQL慢查询,具体指运行时间超过long_query_time值的SQL。我们常听MySQL中有二进制日志-binlog、中继日志-relaylog、重做回滚日志-redolog、undolog 等。针对慢查询,还有一种慢查询日志slowlog,用来记录在MySQL中响应时间超过阀值的语句。慢SQL对实际生产业务影响是致命的,所以测试人员在性能测试过程中,对数据库SQL语句执行情况实施监控,给开发提供准确的性能优化意见显得尤为重要。那怎么使用Mysql数据库提供的慢查询日志来监控SQL语句执行情况,找到消耗较高的SQL语句,以下详细说明一下慢查询日志的使用步骤:

查看sql-slow状态:show variables like 'slow_query_log';
修改sql-slow状态:set global slow_query_log=1;

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+

查看mysql-slow阈值:show variables like 'long_query_time';
修改mysql-slow阈值:set global long_query_time=0.1;

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

查看mysql慢sql目录:'show variables like 'slow_query_log_file';'

mysql> show variables like 'slow_query_log_file';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log_file | /home/mysql/log/mysql/slow_query.log |
+---------------------+--------------------------------------+

[root@localhost ~]# cat /home/mysql/log/mysql/slow_query.log
# Time: 220920 16:16:40
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 45  Schema: nova  QC_hit: No
# Query_time: 2.000211  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 65
SET timestamp=1663661800;
select sleep(2);

格式化慢sql日志:mysqldumpslow -s at -t 1 /home/mysql/log/mysql/slow_query.log

[root@localhost ~]# mysqldumpslow -s at -t 1 /home/mysql/log/mysql/slow_query.log

Reading mysql slow query log from slow.log
Count: 4  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  Time: N N:N:N
  # User@Host: root[root] @ localhost [N.N.N.N]
  # Thread_id: N  Schema:   QC_hit: No
  # Query_time: N.N  Lock_time: N.N  Rows_sent: N  Rows_examined: N
  # Rows_affected: N  Bytes_sent: N
  SET timestamp=N;
  select sleep(N)

结果详解

Count:表示该类型的语句执行次数;
Time:表示该类型的语句执行的平均时间(总计时间);
Lock:锁时间0s;
Rows:单次返回的结果数;

注:此语句通过 jdbc 执行不了,属于命令行执行。意思为:显示出耗时最长的 10 个 SQL 语句执行信息,10 可以修改为 TOP 个数。显示的信息为:执行次数、平均执行时间、SQL语句
备注:当mysqldumpslow命令执行失败时,将慢日志同步到本地进行格式化处理。

如何避免SQL慢查询

  1. 使用子查询

SELECT FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);子查询在 MySQL5.5 版本里,内部执行计划器是这样执行的:先查外表再匹配内表,而不是先查内表 t2,当外表的数据很大时,查询速度会非常慢。在 MariaDB10/MySQL5.6 版本里,采用 join 关联方式对其进行了优化,这条 SQL 会自动转换为 SELECT t1. FROM t1 JOIN t2 ON t1.id = t2.id;但请注意的是:优化只针对 SELECT 有效,对 UPDATE/DELETE 子 查询无效, 生产环境尽量应避免使用子查询。

  1. 避免函数索引

SELECT FROM t WHERE YEAR(d) >= 2016;由于 MySQL 不像 Oracle 那样⽀持函数索引,即使 d 字段有索引,也会直接全表扫描。应改为 > SELECT FROM t WHERE d >= ‘2016-01-01’;

  1. 用 IN 来替换 OR 低效查询

慢 SELECT FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;高效查询 > SELECT FROM t WHERE LOC_IN IN (10,20,30);

  1. LIKE 双百分号无法使用到索引

SELECT FROM t WHERE name LIKE ‘%de%’;使用 SELECT FROM t WHERE name LIKE ‘de%’;

  1. 分组统计可以禁止排序

SELECT goods_id,count() FROM t GROUP BY goods_id;默认情况下,MySQL 对所有 GROUP BY col1,col2… 的字段进⾏排序。如果查询包括 GROUP BY,想要避免排序结果的消耗,则可以指定 ORDER BY NULL 禁止排序。使用 SELECT goods_id,count () FROM t GROUP BY goods_id ORDER BY NULL;

  1. 禁止不必要的 ORDER BY 排序

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;使用 SELECT count (1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

statement

insert数量:show status like 'Com_insert';

mysql> show status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 0     |
+---------------+-------+

delete数量:show status like 'Com_delete';

mysql> show status like 'Com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 0     |
+---------------+-------+

update数量:show status like 'Com_update';

mysql> show status like 'Com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 0     |
+---------------+-------+

select数量:show status like 'Com_select';

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 2     |
+---------------+-------+

Database throughputs(吞吐)

发送吞吐量:show status like 'Bytes_sent';

mysql> show status like 'Bytes_sent';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Bytes_sent    | 106824 |
+---------------+--------+

接收吞吐量:show status like 'Bytes_received';

mysql> show status like 'Bytes_received';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Bytes_received | 4054  |
+----------------+-------+

总吞吐量计算公式:Bytes_sent+Bytes_received

serverconfig(数据库参数)

查询数据库全局配置:show variables;
数据库参数

总结

任何东西不应过重关注其外表,要注重内在的东西,往往绚丽的外表下会有对应的负担和损耗。mysql 数据库的监控支持通过 SQL 方式从 performance_schema 库中访问对应的表数据,前提是初始化此库并开启监控数据写入。对于监控而言,不在于手段的多样性,而需要明白监控的本质,以及需要的监控项内容,找到符合自身项目特色的监控方式。在选择监控工具对 mysql 监控时,需要关注监控工具本身对于数据库服务器的消耗,不要影响到其自身的使用。

附:zabbix监控脚本

[root@localhost ~]# vim /usr/local/zabbix/etc/zabbix_agentd.conf.d/mysql.conf
# 最大使用连接数:
UserParameter=MySQL.Max_used_connections,/bin/mysql -h127.0.0.1 -e"show status like 'Max_used_connections';" | grep Max_used_connections | awk '{print $2}'
# 当前打开的连接数:
UserParameter=MySQL.Threads_connected,/bin/mysql -h127.0.0.1 -e"show status like 'Threads_connected';" | grep Threads_connected | awk '{print $2}'
# 缓存池命中次数:
UserParameter=MySQL.Innodb_buffer_pool_reads,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_reads';" | grep Innodb_buffer_pool_reads | awk '{print $2}'
# 缓存池未命中次数:
UserParameter=MySQL.Innodb_buffer_pool_read_requests,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_read_requests';" | grep Innodb_buffer_pool_read_requests | awk '{print $2}'
# 缓存池命中率:
UserParameter=MySQL.Innodb_buffer_pool_ead_requests_probability,/usr/local/zabbix/share/zabbix/alertscripts/Innodb_buffer_pool_ead_requests_probability.sh
# 缓存池总页数:
UserParameter=MySQL.Innodb_buffer_pool_pages_total,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_pages_total';" | grep Innodb_buffer_pool_pages_total | awk '{print $2}'
# 缓存池空闲页数:
UserParameter=MySQL.Innodb_buffer_pool_pages_free,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_pages_free';" | grep Innodb_buffer_pool_pages_free | awk '{print $2}'
# 缓存池使用率:
UserParameter=MySQL.Innodb_buffer_pool_used,/usr/local/zabbix/share/zabbix/alertscripts/Innodb_buffer_pool_used.sh
# 锁等待个数:
UserParameter=MySQL.Innodb_row_lock_waits,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_row_lock_waits';" | grep Innodb_row_lock_waits | awk '{print $2}'
# 锁等待平均时间:
UserParameter=MySQL.Innodb_row_lock_time_avg,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_row_lock_time_avg';" | grep Innodb_row_lock_time_avg | awk '{print $2}'
# 表锁情况:
UserParameter=MySQL.tables_lock_num,/bin/mysql -h127.0.0.1 -e"show open TABLES where in_use>0;" | wc -l
# 发送吞吐量:
UserParameter=MySQL.Bytes_sent,/bin/mysql -h127.0.0.1 -e"show status like 'Bytes_sent';" | grep Bytes_sent | awk '{print $2}'
# 接收吞吐量:
UserParameter=MySQL.Bytes_received,/bin/mysql -h127.0.0.1 -e"show status like 'Bytes_received';" | grep Bytes_received | awk '{print $2}'
    

[root@localhost ~]# cat /usr/local/zabbix/share/zabbix/alertscripts/Innodb_buffer_pool_used.sh
#!/bin/bash
Innodb_buffer_pool_pages_total=`/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_pages_total';" | grep Innodb_buffer_pool_pages_total | awk '{print $2}'`
Innodb_buffer_pool_pages_free=`/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_pages_free';" | grep Innodb_buffer_pool_pages_free | awk '{print $2}'`
Innodb_buffer_pool_used=`echo scale=2';(('$Innodb_buffer_pool_pages_total-$Innodb_buffer_pool_pages_free')/'$Innodb_buffer_pool_pages_total')' | bc`
echo 0$Innodb_buffer_pool_used


[root@localhost ~]# cat /usr/local/zabbix/share/zabbix/alertscripts/Innodb_buffer_pool_ead_requests_probability.sh 
#!/bin/bash
Innodb_buffer_pool_reads=`/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_reads';" | grep Innodb_buffer_pool_reads | awk '{print $2}'`
Innodb_buffer_pool_read_requests=`/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_read_requests';" | grep Innodb_buffer_pool_read_requests | awk '{print $2}'`
Innodb_buffer_pool_ead_requests_probability=`echo scale=2';(1-'$Innodb_buffer_pool_reads'/'$Innodb_buffer_pool_read_requests')' | bc`
echo 0$Innodb_buffer_pool_ead_requests_probability

如果不想直接输入用户名密码可以摘/etc/my.ini文件中修改添加以下两个参数:
[mysql]
user=root
password=p@ssw0rd

监控线配置图
采集结果

最后修改:2022 年 09 月 20 日
-