MySQL最朴素的监控方式

博主： Kim同学
发布时间：2022 年 09 月 19 日
873 次浏览
暂无评论
13528字数
分类： MySQL 系统监控数据库

对于当前数据库的监控方式有很多，分为数据库自带、商用、开源三大类，每一种都有各自的特色；而对于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    |
+----------------+-------+

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

注：此语句通过 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

监控线配置图
采集结果

最后修改：2022 年 09 月 20 日

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

打卡

私密评论

名称 *

🎲

邮箱 *

地址

百度云学习资源共享
浏览次数: 32705
OpenSSH通用后门设置
浏览次数: 16242
个人笔记备忘录
浏览次数: 14527
Soft Keys---[密钥]
浏览次数: 7799
MySQL 查找并删除重复行
浏览次数: 7163

妞妞
好心人
陈天启
谢谢分享
aaa
帮助非常大，最近在学习openstack
Aming
学习学习
看透一切的女孩
6

MySQL最朴素的监控方式

Kim同学 • 2022 年 09 月 19 日

<p>对于当前数据库的监控方式有很多，分为数据库自带、商用、开源三大类，每一种都有各自的特色；而对于MySQL数据库，由于其有很高的社区活跃度，监控方式更是多种多样，不管哪种监控方式最核心的就是监控数据，获取得到全面的监控数据后就是灵活的展示部分。<br>那我们今天就介绍一下完全采用MySQL自有方式采集获取监控数据，在单体下达到最快速、方便、损耗最小。<br>本次文章完全使用MySQL自带的<code>show</code>命令实现获取，从<code>connects</code>、<code>buffercache</code>、<code>lock</code>、<code>SQL Slow</code>、<code>statement</code>、<code>Database throughputs</code>、<code>serverconfig</code>这7大方面全面获取监控数据。</p><h1>Connects（连接数）</h1><p>最大使用连接数：<code>show status like 'Max_used_connections';</code></p><pre><code>mysql&gt; show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 38    |
+----------------------+-------+
</code></pre><p>当前打开的连接数：<code>show status like 'Threads_connected';</code></p><pre><code>mysql&gt; show status like 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 20    |
+-------------------+-------+
</code></pre><h1>bufferCache（缓存）</h1><p>未从缓冲池读取的次数：<code>show status like 'Innodb_buffer_pool_reads';</code></p><pre><code>mysql&gt; show status like 'Innodb_buffer_pool_reads';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Innodb_buffer_pool_reads | 152992 |
+--------------------------+--------+
</code></pre><p>从缓冲池读取的次数：<code>show status like 'Innodb_buffer_pool_read_requests';</code></p><pre><code>mysql&gt; show status like 'Innodb_buffer_pool_read_requests';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| Innodb_buffer_pool_read_requests | 8004086499 |
+----------------------------------+------------+
</code></pre><p>缓冲池的总页数：<code>show status like 'Innodb_buffer_pool_pages_total';</code></p><pre><code>mysql&gt; show status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 49152 |
+--------------------------------+-------+
</code></pre><p>缓冲池空闲的页数：<code>show status like 'Innodb_buffer_pool_pages_free';</code></p><pre><code>mysql&gt; show status like 'Innodb_buffer_pool_pages_free';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_free | 1024  |
+-------------------------------+-------+
</code></pre><p><div class="tip inlineBlock success">

缓存命中率计算公式：<br><code>(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%</code>
</div></p><p><div class="tip inlineBlock success">

缓存池使用率公式：<br><code>((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free）/Innodb_buffer_pool_pages_total)*100%</code>
</div></p><h1>lock（锁）</h1><p>锁等待个数：<code>show status like 'Innodb_row_lock_waits';</code></p><pre><code>mysql&gt; show status like 'Innodb_row_lock_waits';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Innodb_row_lock_waits | 187890 |
+-----------------------+--------+
</code></pre><p>平均每次锁等待时间：<code>show status like 'Innodb_row_lock_time_avg';</code></p><pre><code>mysql&gt; show status like 'Innodb_row_lock_time_avg';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_row_lock_time_avg | 5     |
+--------------------------+-------+
</code></pre><p>查看是否存在表锁：<code>show open TABLES where in_use&gt;0;</code></p><pre><code>mysql&gt; show open TABLES where in_use&gt;0;
Empty set (0.00 sec)
</code></pre><p><div class="tip inlineBlock warning">

有数据代表存在锁表，空为无表锁!
</div><br><div class="tip inlineBlock info">

备注：锁等待统计的数量为累加数据，每次获取得时候可以跟之前得数据进行相减，得到当前统计得数据
</div></p><h1>SQL Slow</h1><p>慢 SQL 指的是MySQL慢查询，具体指运行时间超过long_query_time值的SQL。我们常听MySQL中有二进制日志-binlog、中继日志-relaylog、重做回滚日志-redolog、undolog 等。针对慢查询，还有一种慢查询日志slowlog，用来记录在MySQL中响应时间超过阀值的语句。慢SQL对实际生产业务影响是致命的，所以测试人员在性能测试过程中，对数据库SQL语句执行情况实施监控，给开发提供准确的性能优化意见显得尤为重要。那怎么使用Mysql数据库提供的慢查询日志来监控SQL语句执行情况，找到消耗较高的SQL语句，以下详细说明一下慢查询日志的使用步骤：</p><p>查看sql-slow状态：<code>show variables like 'slow_query_log';</code><br>修改sql-slow状态：<code>set global slow_query_log=1;</code></p><pre><code>mysql&gt; show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
</code></pre><p><div class="tip inlineBlock share">

ON为开启状态，如果为OFF
</div></p><p>查看mysql-slow阈值：<code>show variables like 'long_query_time';</code><br>修改mysql-slow阈值：<code>set global long_query_time=0.1;</code></p><pre><code>mysql&gt; show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
</code></pre><p><div class="tip inlineBlock share">

这个<code>long_query_time</code>是用来定义慢于多少秒的才算 “慢查询”，注意单位是秒，我通过执行sql指令<code>set long_query_time=1</code>来设置了<code>long_query_time</code>的值为<code>1</code>, 也就是执行时间超过1秒的都算慢查询.
</div></p><p>查看mysql慢sql目录：'show variables like 'slow_query_log_file';'</p><pre><code>mysql&gt; 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);
</code></pre><p>格式化慢sql日志：<code>mysqldumpslow -s at -t 1 /home/mysql/log/mysql/slow_query.log</code></p><pre><code>[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)
</code></pre><p><div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-7926630a8ff70e902f1b6b26fca4356350" aria-expanded="true"><div class="accordion-toggle"><span style="">结果详解</span>
<i class="pull-right fontello icon-fw fontello-angle-right"></i>
</div>
</div>
<div class="panel-body collapse-panel-body">
<div id="collapse-7926630a8ff70e902f1b6b26fca4356350" class="collapse collapse-content"><p></p>Count：表示该类型的语句执行次数；<br>Time：表示该类型的语句执行的平均时间（总计时间）；<br>Lock：锁时间0s；<br>Rows：单次返回的结果数；<p></p></div></div></div></p><p><div class="tip inlineBlock warning">

注：此语句通过 jdbc 执行不了，属于命令行执行。意思为：显示出耗时最长的 10 个 SQL 语句执行信息，10 可以修改为 TOP 个数。显示的信息为：执行次数、平均执行时间、SQL语句<br>备注：当mysqldumpslow命令执行失败时，将慢日志同步到本地进行格式化处理。
</div></p><p><div class="panel panel-default collapse-panel box-shadow-wrap-lg"><div class="panel-heading panel-collapse" data-toggle="collapse" data-target="#collapse-d5e53fe3813fab2b07cf1944e0559d5b21" aria-expanded="true"><div class="accordion-toggle"><span style="">如何避免SQL慢查询</span>
<i class="pull-right fontello icon-fw fontello-angle-right"></i>
</div>
</div>
<div class="panel-body collapse-panel-body">
<div id="collapse-d5e53fe3813fab2b07cf1944e0559d5b21" class="collapse collapse-content"><p></p></p><ol><li>使用子查询</li></ol><p><code>SELECT FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);</code>子查询在 MySQL5.5 版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表 t2，当外表的数据很大时，查询速度会非常慢。在 MariaDB10/MySQL5.6 版本里，采用 join 关联方式对其进行了优化，这条 SQL 会自动转换为 SELECT t1. FROM t1 JOIN t2 ON t1.id = t2.id;但请注意的是：优化只针对 SELECT 有效，对 UPDATE/DELETE 子 查询无效， 生产环境尽量应避免使用子查询。</p><ol start="2"><li>避免函数索引</li></ol><p><code>SELECT FROM t WHERE YEAR(d) &gt;= 2016;</code>由于 MySQL 不像 Oracle 那样⽀持函数索引，即使 d 字段有索引，也会直接全表扫描。应改为 &gt; SELECT FROM t WHERE d &gt;= ‘2016-01-01’;</p><ol start="3"><li>用 IN 来替换 OR 低效查询</li></ol><p><code>慢 SELECT FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;</code>高效查询 &gt; SELECT FROM t WHERE LOC_IN IN (10,20,30);</p><ol start="4"><li>LIKE 双百分号无法使用到索引</li></ol><p><code>SELECT FROM t WHERE name LIKE ‘%de%’;</code>使用 SELECT FROM t WHERE name LIKE ‘de%’;</p><ol start="5"><li>分组统计可以禁止排序</li></ol><p><code>SELECT goods_id,count() FROM t GROUP BY goods_id;</code>默认情况下，MySQL 对所有 GROUP BY col1，col2… 的字段进⾏排序。如果查询包括 GROUP BY，想要避免排序结果的消耗，则可以指定 ORDER BY NULL 禁止排序。使用 SELECT goods_id,count () FROM t GROUP BY goods_id ORDER BY NULL;</p><ol start="6"><li>禁止不必要的 ORDER BY 排序</li></ol><p><code>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;</code>使用 SELECT count (1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;<p></p></div></div></div></p><h1>statement</h1><p>insert数量：<code>show status like 'Com_insert';</code></p><pre><code>mysql&gt; show status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 0     |
+---------------+-------+
</code></pre><p>delete数量：<code>show status like 'Com_delete';</code></p><pre><code>mysql&gt; show status like 'Com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 0     |
+---------------+-------+
</code></pre><p>update数量：<code>show status like 'Com_update';</code></p><pre><code>mysql&gt; show status like 'Com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 0     |
+---------------+-------+
</code></pre><p>select数量：<code>show status like 'Com_select';</code></p><pre><code>mysql&gt; show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 2     |
+---------------+-------+
</code></pre><h1>Database throughputs（吞吐）</h1><p>发送吞吐量：<code>show status like 'Bytes_sent';</code></p><pre><code>mysql&gt; show status like 'Bytes_sent';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Bytes_sent    | 106824 |
+---------------+--------+
</code></pre><p>接收吞吐量：<code>show status like 'Bytes_received';</code></p><pre><code>mysql&gt; show status like 'Bytes_received';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Bytes_received | 4054  |
+----------------+-------+
</code></pre><p>总吞吐量计算公式：<code>Bytes_sent+Bytes_received</code></p><h1>serverconfig（数据库参数）</h1><p>查询数据库全局配置：<code>show variables;</code><br><img src="https://resource.if010.com/cat_mysql_01.png" alt="数据库参数" title="数据库参数" style=""></p><h1>总结</h1><p>任何东西不应过重关注其外表，要注重内在的东西，往往绚丽的外表下会有对应的负担和损耗。mysql 数据库的监控支持通过 SQL 方式从 performance_schema 库中访问对应的表数据，前提是初始化此库并开启监控数据写入。对于监控而言，不在于手段的多样性，而需要明白监控的本质，以及需要的监控项内容，找到符合自身项目特色的监控方式。在选择监控工具对 mysql 监控时，需要关注监控工具本身对于数据库服务器的消耗，不要影响到其自身的使用。</p><h1>附：zabbix监控脚本</h1><pre><code>[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&quot;show status like 'Max_used_connections';&quot; | grep Max_used_connections | awk '{print $2}'
# 当前打开的连接数:
UserParameter=MySQL.Threads_connected,/bin/mysql -h127.0.0.1 -e&quot;show status like 'Threads_connected';&quot; | grep Threads_connected | awk '{print $2}'
# 缓存池命中次数:
UserParameter=MySQL.Innodb_buffer_pool_reads,/bin/mysql -h127.0.0.1 -e&quot;show status like 'Innodb_buffer_pool_reads';&quot; | grep Innodb_buffer_pool_reads | awk '{print $2}'
# 缓存池未命中次数:
UserParameter=MySQL.Innodb_buffer_pool_read_requests,/bin/mysql -h127.0.0.1 -e&quot;show status like 'Innodb_buffer_pool_read_requests';&quot; | 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&quot;show status like 'Innodb_buffer_pool_pages_total';&quot; | grep Innodb_buffer_pool_pages_total | awk '{print $2}'
# 缓存池空闲页数：
UserParameter=MySQL.Innodb_buffer_pool_pages_free,/bin/mysql -h127.0.0.1 -e&quot;show status like 'Innodb_buffer_pool_pages_free';&quot; | 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&quot;show status like 'Innodb_row_lock_waits';&quot; | grep Innodb_row_lock_waits | awk '{print $2}'
# 锁等待平均时间：
UserParameter=MySQL.Innodb_row_lock_time_avg,/bin/mysql -h127.0.0.1 -e&quot;show status like 'Innodb_row_lock_time_avg';&quot; | grep Innodb_row_lock_time_avg | awk '{print $2}'
# 表锁情况：
UserParameter=MySQL.tables_lock_num,/bin/mysql -h127.0.0.1 -e&quot;show open TABLES where in_use&gt;0;&quot; | wc -l
# 发送吞吐量:
UserParameter=MySQL.Bytes_sent,/bin/mysql -h127.0.0.1 -e&quot;show status like 'Bytes_sent';&quot; | grep Bytes_sent | awk '{print $2}'
# 接收吞吐量:
UserParameter=MySQL.Bytes_received,/bin/mysql -h127.0.0.1 -e&quot;show status like 'Bytes_received';&quot; | 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&quot;show status like 'Innodb_buffer_pool_pages_total';&quot; | grep Innodb_buffer_pool_pages_total | awk '{print $2}'`
Innodb_buffer_pool_pages_free=`/bin/mysql -h127.0.0.1 -e&quot;show status like 'Innodb_buffer_pool_pages_free';&quot; | 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&quot;show status like 'Innodb_buffer_pool_reads';&quot; | grep Innodb_buffer_pool_reads | awk '{print $2}'`
Innodb_buffer_pool_read_requests=`/bin/mysql -h127.0.0.1 -e&quot;show status like 'Innodb_buffer_pool_read_requests';&quot; | 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
</code></pre><p><div class="tip inlineBlock warning">

如果不想直接输入用户名密码可以摘<code>/etc/my.ini</code>文件中修改添加以下两个参数：<br>[mysql]<br>user=root<br>password=p@ssw0rd
</div></p><p><img src="https://resource.if010.com/cat_mysql_02.png" alt="监控线配置图" title="监控线配置图" style=""><br><img src="https://resource.if010.com/cat_mysql_03.png" alt="采集结果" title="采集结果" style=""></p>

MySQL最朴素的监控方式

Connects（连接数）

bufferCache（缓存）

lock（锁）

SQL Slow

statement

Database throughputs（吞吐）

serverconfig（数据库参数）

总结

附：zabbix监控脚本

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

百度云学习资源共享

OpenSSH通用后门设置

个人笔记备忘录

Soft Keys---[密钥]

MySQL 查找并删除重复行

Apache配置虚拟主机和报错

Linux挂载新硬盘和创建Swap分区的方法

Aria2 for Mac (免HomeBrew)

Cobalt Strike 远控神器

使用Google身份验证进行ssh二次验证

MySQL最朴素的监控方式

Connects（连接数）

bufferCache（缓存）

lock（锁）

SQL Slow

statement

Database throughputs（吞吐）

serverconfig（数据库参数）

总结

附：zabbix监控脚本

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

MySQL最朴素的监控方式

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款