mysqldump 备份产生大量慢查询,有办法过滤么?

原创
04/08 15:17
阅读数 236

MySQL 8.0.30 新功能,再也不用担心大量无效日志了!

作者:李富强,爱可生 DBA 团队成员,熟悉 MySQL,TiDB,OceanBase 等数据库。相信持续把对的事情做好一点,会有不一样的收获。

爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。

本文约 1600 字,预计阅读需要 5 分钟。

新特性介绍

MySQL 8.0.30 版本中,mysqldump 逻辑备份工具引入了 mysqld-long-query-time 选项,用于设置 mysqldump 备份的会话级别慢查询阈值 long_query_time

生产环境 long_query_time 一般设置的比较小,由于 mysqldump 备份时执行的是全表扫描,SQL 执行时间很容易超过 long_query_time,导致慢查询日志中记录大量备份产生的慢查询。通过指定 mysqld-long-query-time 选项,给 mysqldump 单独设定合适的会话级别慢查询阈值,可以减少慢查询日志中大量无效慢查询。

使用方法

mysqldump 指定备份选项 mysqld-long-query-time 为具体的值,该选项取值范围为 0 到 31536000(365天),单位是秒。如果不指定此选项,mysqldump 产生的查询是否属于慢查询根据全局变量 long_query_time 阈值来确定。

# 添加 mysqldump 备份选项为 --mysqld-long-query-time=100
# 即小于 100s 的 mysqldump 备份语句不会记录到慢查询日志中
mysqldump -ubackup_user -h10.186.58.39  -P3000 -p123456 --all-databases \
--mysqld-long-query-time=100  >/opt/all_databases.sql

对比实验

mysqldump 不添加备份选项 mysqld-long-query-time 和添加备份选项 mysqld-long-query-time 对比测试,分别观察慢查询日志内容。

环境信息

#数据库服务器配置
8c16g150g(ssd)
  
#数据库版本
 MySQL  localhost:3000 ssl  SQL > select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.30    |
+-----------+
  
#mysqldump版本
  
[root@node1 ~]# mysqldump --version
mysqldump  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
 
 
#全局慢查询阈值
 MySQL  localhost:3000 ssl  SQL >  show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
  
#慢查询已开启
 MySQL  localhost:3000 ssl  SQL >   show variables like '%slow_query_log%';
+---------------------+-------------------------------------------------------+
| Variable_name       | Value                                                 |
+---------------------+-------------------------------------------------------+
| slow_query_log      | ON                                                    |
| slow_query_log_file | /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log |
+---------------------+-------------------------------------------------------+
  
#使用sysbench工具,分别造了数据量为10w,100w,1000w,50000w共4张表
 MySQL  localhost:3000 ssl  SQL > select count(*) from fq_10w.sbtest1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.1108 sec)
 MySQL  localhost:3000 ssl  SQL > select count(*) from fq_100w.sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.3367 sec)
 MySQL  localhost:3000 ssl  SQL > select count(*) from fq_1000w.sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.3046 sec)
 MySQL  localhost:3000 ssl  SQL > select count(*) from fq_5000w.sbtest1;
+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (8.7858 sec)

场景一

mysqldump 不添加备份选项 mysqld-long-query-time 进行数据库备份。

#执行备份
[root@node1 ~]# mysqldump -ubackup_user -h10.186.58.39  -P3000 -p123456 --all-databases    >/opt/all_databases.sql
  
#备份完成后,查看慢日志,可以看到备份产生的查询的Query_time只要超过全局变量long_query_time(0.1s)的值就会记录到慢查询日志中
[root@node1 ~]# cat   /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log
# Time: 2024-03-20T06:25:51.689799Z
# User@Host: backup_user[backup_user] @ node1 [10.186.58.39]  Id:    31
# Query_time: 77.574346  Lock_time: 0.000000 Rows_sent: 50000000  Rows_examined: 50000000
use fq_5000w;
SET timestamp=1710915874;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`;
# Time: 2024-03-20T06:37:00.974869Z
# User@Host: backup_user[backup_user] @ node1 [10.186.58.39]  Id:    32
# Query_time: 18.428685  Lock_time: 0.000000 Rows_sent: 10000000  Rows_examined: 10000000
use fq_1000w;
SET timestamp=1710916602;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`;
# Time: 2024-03-20T06:37:02.571939Z
# User@Host: backup_user[backup_user] @ node1 [10.186.58.39]  Id:    32
# Query_time: 1.527482  Lock_time: 0.000000 Rows_sent: 1000000  Rows_examined: 1000000
use fq_100w;
SET timestamp=1710916621;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`;
# Time: 2024-03-20T06:37:02.754243Z
# User@Host: backup_user[backup_user] @ node1 [10.186.58.39]  Id:    32
# Query_time: 0.125747  Lock_time: 0.000000 Rows_sent: 100000  Rows_examined: 100000
use fq_10w;
SET timestamp=1710916622;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`;

场景二

mysqldump 添加备份选项 mysqld-long-query-time 进行数据库备份。

#清空慢日志文件
[root@node1 ~]# cat /dev/null > /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log
[root@node1 ~]# cat /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log
[root@node1 ~]#
 
#执行备份,根据场景1得知,备份5000w的表耗时77.57s,这里我们指定备份选项mysqld-long-query-time为100,尝试把备份本身产生的慢查询过滤掉
[root@node1 ~]# mysqldump -ubackup_user -h10.186.58.39  -P3000 -p123456 --all-databases  --mysqld-long-query-time=100   >/opt/all_databases.sql
  
#备份完成后,查看慢日志,可以看到慢日志文件为空,备份本身产生的慢查询被过滤掉了
[root@node1 ~]# cat /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log
[root@node1 ~]#

结论

MySQL 8.0.30 版本,通过给 mysqldump 指定 mysqld-long-query-time 选项,设定合适的会话级别慢查询阈值,可以过滤掉备份产生的慢查询。

知识补充

  • MySQL 8.0.30 版本的 mysqldump 备份工具中的 mysqld-long-query-time 选项,也适用于 MySQL 5.7 版本的备份。
  • mysqldumpmysqld-long-query-time 选项的值不支持小数(eg:0.1),否则执行备份会报错。

更多技术文章,请访问:https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台,覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库,为开发和运维提供流程自动化能力,提升上线效率,提高数据质量。

SQLE 获取

类型 地址
版本库 https://github.com/actiontech/sqle
文档 https://actiontech.github.io/sqle-docs/
发布信息 https://github.com/actiontech/sqle/releases
数据审核插件开发文档 https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse
展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部