MySQL的原始监控方式

最新推荐文章于 2024-03-31 23:28:22 发布

Kim同学

最新推荐文章于 2024-03-31 23:28:22 发布

阅读量264

点赞数 1

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_32262243/article/details/126960660

版权

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

ON为开启状态，如果为OFF

查看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 |
+-----------------+----------+

这个long_query_time是用来定义慢于多少秒的才算 “慢查询”，注意单位是秒，我通过执行sql指令set long_query_time=1来设置了long_query_time的值为1, 也就是执行时间超过1秒的都算慢查询.

查看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：单次返回的结果数；[/collapse]

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

如何避免SQL慢查询

使用子查询
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 子查询无效，生产环境尽量应避免使用子查询。
避免函数索引
SELECT FROM t WHERE YEAR(d) >= 2016;由于 MySQL 不像 Oracle 那样⽀持函数索引，即使 d 字段有索引，也会直接全表扫描。应改为 > SELECT FROM t WHERE d >= ‘2016-01-01’;
用 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);
LIKE 双百分号无法使用到索引
SELECT FROM t WHERE name LIKE ‘%de%’;使用 SELECT FROM t WHERE name LIKE ‘de%’;
分组统计可以禁止排序
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;
禁止不必要的 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

监控线配置图
采集结果

Kim同学

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL的原始监控方式

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

扫一扫