[转帖]图文结合带你搞懂 MySQL 日志之 Slow Query Log(慢查询日志)

图文,结合,搞懂,mysql,日志,slow,query,log,查询 · 浏览次数 : 0

小编点评

** GreatSQL 概述** GreatSQL 是由万里数据库维护的 MySQL 分支,专注于提升 MGR 可靠性及性能,支持 InnoDB 并行查询特性,是适用于金融级应用的 MySQL 分支版本。 ** 主要功能** * ** MGR 可靠性提升:**GreatSQL 使用 InnoDB 作为核心引擎,支持多种性能优化技术,以提升 MGR 可靠性及性能。 * ** InnoDB 并行查询:**GreatSQL 支持 InnoDB 引擎,提供多线程并行查询,以提升数据库性能。 * ** InnoDB 并行查询特性:**GreatSQL 支持 InnoDB 引擎的并行查询特性,以提升数据库性能。 * **金融级应用:**GreatSQL 是适用于金融级应用的 MySQL 分支版本,提供金融级安全及性能。 ** 主要特性** * ** MGR 可靠性提升:**GreatSQL 使用 InnoDB 作为核心引擎,提供多种性能优化技术,以提升 MGR 可靠性及性能。 * ** InnoDB 并行查询:**GreatSQL 支持 InnoDB 引擎,提供多线程并行查询,以提升数据库性能。 * ** InnoDB 并行查询特性:**GreatSQL 支持 InnoDB 引擎的并行查询特性,以提升数据库性能。 * **金融级应用:**GreatSQL 是适用于金融级应用的 MySQL 分支版本,提供金融级安全及性能。 ** 使用教程** * **官方文档:**GreatSQL 官方文档包含大量使用教程、示例和调试指南。 * **Gitee 版本:**Gitee 版本包含一些使用教程和示例。 * **GitHub 版本:**GitHub 版本包含一些使用教程和示例。 ** 注意** * GreatSQL 是由万里数据库维护的 MySQL 分支,并非官方 MySQL 版本。 * GreatSQL 使用 InnoDB 引擎,需要安装 InnoDB 才能使用。 * GreatSQL 是一个基于 InnoDB 的 MySQL 分支版本,不支持所有 MySQL 功能。

正文

https://my.oschina.net/GreatSQL/blog/5719211
  • GreatSQL 社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。
  • GreatSQL 是 MySQL 的国产分支版本,使用上与 MySQL 一致。
  • 作者:KAiTO
  • 文章来源:GreatSQL 社区原创

什么是慢查询日志

MySQL 的慢查询日志,用来记录在 MySQL 中响应时间超过阀值的语句,具体指运行时间超过 long_query_time 值的 SQL,则会被记录到慢查询日志中。long_query_time 的默认值为 10,意思是运行 10 秒以上 (不含 10 秒) 的语句,认为是超出了我们的最大忍耐时间值。

它的主要作用是,帮助我们发现那些执行时间特别长的 SQL 查询,并且有针对性地进行优化,从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候,检查一下慢查询日志,找到那些慢查询,对解决问题很有帮助。比如一条 sq | 执行超过 5 秒钟,我们就算慢 SQL,希望能收集超过 5 秒的 sql,结合 explain 进行全面分析。

默认情况下,MySQL 数据库没有开启慢查询日志,需要我们手动来设置这个参数。如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响

慢查询日志支持将日志记录写入文件。

如何开启慢查询日志

开启 slow_query_log

mysql> show variables like '%slow_query_log%';
+-----------------------------------+--------------------------------+
| Variable_name                     | Value                          |
+-----------------------------------+--------------------------------+
| slow_query_log                    | OFF                            |
| slow_query_log_always_write_time  | 10.000000                      |
| slow_query_log_file               | /var/lib/mysql/KAiTO-slow.log  |
| slow_query_log_use_global_control |                                |
+-----------------------------------+--------------------------------+
4 rows in set (0.00 sec)

# 开启慢查询
mysql > set global slow_query_log='ON';
Query OK, 0 rows affected (0.12 sec)

然后我们再来查看下慢查询日志是否开启,以及慢查询日志文件的位置:

mysql> show variables like '%slow_query_log%';
+-----------------------------------+--------------------------------+
| Variable_name                     | Value                          |
+-----------------------------------+--------------------------------+
| slow_query_log                    | ON                             |
| slow_query_log_always_write_time  | 10.000000                      |
| slow_query_log_file               | /var/lib/mysql/KAiTO-slow.log  |
| slow_query_log_use_global_control |                                |
+-----------------------------------+--------------------------------+
4 rows in set (0.00 sec)

你能看到这时慢查询分析已经开启,同时文件保存在 /var/lib/mysql/KAiTO-slow.log 文件中。

修改 long_query_time 阈值

接下来我们来看下慢查询的时间阈值设置,使用如下命令:

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

意思就是超过 10 秒的 SQL 语句就会被记录慢查询日志中,那要如何修改这个阈值呢?

mysql> set global long_query_time = 1;
mysql> show global variables like '%long-query_time%';

或修改 my.cnf 文件,[mysqld] 下增加或修改参数 long_query_timeslow_query_log 和 slow_query_log_file 后,然后重启 MySQL 服务器。

[mysqld]
slow_query_log=ON #开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/my-slow.log #慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒,超出此设定值的SQL即被记录到慢查询日志
log_output=FILE # 一般有两种形式,一种是输出到文件FILE中,一种是写入数据表格table中,会保存到mysql库的slow_log表中

如果不指定存储路径,慢查询日志将默认存储到 MySQL 数据库的数据文件夹下。如果不指定文件名,默认文件名为 hostname-slow.log

补充

  • min_examined_row_limit

除了上述变量,控制慢查询日志的还有一个系统变量: min_examined_row_limit。 这个变量的意思是,查询扫描过的最少记录数。这个变量和查询执行时间,共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值,并且查询执行时间超过 long_query_time 的值,那么,这个查询就被记录到慢查询日志中;反之,则不被记录到慢查询日志中。

mysql> show variables like 'min%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

你也可以根据需要,通过修改 my.cnf 文件,来修改 min_examined_row_limit 的值。


除了记录普通的慢查询之外,MySQL 还提供了两个参数来让我们记录未使用索引的查询,它们分别是:log-queries-not-using-indexes 和 log_throttle_queries_not_using_indexes

  • log-queries-not-using-indexes

系统变量 log-queries-not-using-indexes 作用是未使用索引的查询也被记录到慢查询日志中。

 

  • log_throttle_queries_not_using_indexes

可通过设置 log_throttle_queries_not_using_indexes 来限制每分钟写入慢日志中的不走索引的 SQL 语句个数,该参数默认为 0,表示不开启,也就是说不对写入 SQL 语句条数进行控制。

在生产环境下,如果没有使用索引,那么此类 SQL 语句会频繁地被记录到 slow log,从而导致 slow log 文件大小不断增加,我们可以通过调整此参数进行配置。


  • log_slow_extra

如果启用 log_slow_extra 系统变量(从 MySQL 8.0.14 开始提供),服务器会在日志写入几个额外字段。若要记录 bytes_received 与 bytes_sent 这两个字段则需要开启


  • percona slow log

GreatSQL 是源于 Percona Server 的分支版本,除了 Percona Server 已有的稳定可靠、高效、管理更方便等优势外,特别是进一步提升了 MGR (MySQL Group Replication) 的性能及可靠性,以及众多 bug 修复。这就是为什么在使用 GreatSQL 查看慢查询日志时,会有 Query_timeLock_time 等信息,这些都是我们 GreatSQL 源于 Percona Server 的原因,使查询内容更加丰富,更多的数据可以使得我们更好的排查错误。

通过一个简单的案例来展示: 我们先把慢查询日志打开且设置时间阈值大于 1 秒就记录:

#开启慢查询日志
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
#时间阈值超过1秒就记录
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
#查看已经被记录的慢查询数量
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 3     |
+---------------+-------+
1 row in set (0.01 sec)

写一条 SQL 语句使得使用时间大于 1 秒

mysql> SELECT * FROM `student` WHERE id>1000 AND `name`='Yunxi';
+---------+-------+-------+------+---------+
| 9999715 |   707 | Yunxi |  863 |      71 |
.......省略
| 9999999 |   418 | Yunxi |  793 |     734 |
+---------+-------+-------+------+---------+
166949 rows in set (3.94 sec)
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 4     |
+---------------+-------+
1 row in set (0.00 sec)

可以看到此条 SQL 已经被记录,接下来我们去查看慢查询日志:

# Time: 2022-12-14T15:01:34.892085Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 3.985637  Lock_time: 0.000138 Rows_sent: 165346  Rows_examined: 9900000 Thread_id: 8 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 4848540 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 9900000 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-12-14T15:01:30.906448Z End: 2022-12-14T15:01:34.892085Z Schema: slow Rows_affected: 0
# Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 0
# Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 27606  InnoDB_IO_r_bytes: 452296704  InnoDB_IO_r_wait: 0.220474
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 8191
use slow;
SET timestamp=1671030090;
SELECT * FROM `student` WHERE id>100000 AND `name`='Yunxi';

可以看到慢查询日志记录的非常详细,从上述日志中能看到几个信息:

  • 1. 这个 SQL 的耗时 3.985637 秒。
  • 2. 返回结果有 165346 行,总共需要扫描 9900000 行数据。如果扫描行数很多,但返回行数很少,说明该 SQL 效率很低,可能索引不当。
  • 3.Read_* 等几个指标表示这个 SQL 读记录的方式,是否顺序读、随机读等。
  • 4.Sort_* 等几个指标表示该 SQL 是否产生了排序,及其代价。如果有且代价较大,需要想办法优化。
  • 5.tmp 等几个指标表示该 SQL 是否产生临时表,及其代价。如果有且代价较大,需要想办法优化。
  • 6.Full_scan/Full_join 表示是否产生了全表扫描或全表 JOIN,如果有且 SQL 耗时较大,需要想办法优化。
  • 7.InnoDB_IO_* 等几个指标表示 InnoDB 逻辑读相关数据。
  • 8.InnoDB_rec_lock_wait 表示是否有行锁等待。
  • 9.InnoDB_queue_wait 表示是否有排队等待。
  • 10.InnoDB_pages_distinct 表示该 SQL 总共读取了多少个 InnoDB page,是个非常重要的指标。

GreatSQL 可以作为 MySQL 或 Percona Server 的可选替代方案,用于线上生产环境。完全免费并兼容 MySQL 或 Percona Server。综上,如果在生产环境中已经用上 Percona Server 的话,那么也可以放心使用 GreatSQL。详情可见:(https://greatsql.cn/doc/#!&v=47_6_0)了解更多 GreatSQL 内容

查看慢查询数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

慢查询日志分析工具

在生产环境中,如果要手工分析日志,查找、分析 SQL,显然是个体力活,MySQL 提供了日志分析工具 mysqldumpslow ,或者是可以使用另一个工具 pt-query-digest。 它可以从 logsprocesslist、和 tcpdump 来分析 MySQL 的状况,logs 包括 slow loggeneral logbinlog。也可以把分析结果输出到文件中,或则把文件写到表中。分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。

关闭慢查询日志

作者建议除了调优需要开,正常还是不要开了

MySQL 服务器停止慢查询日志功能的方法:

  • 方式 1
[mysqld]
slow_query_log=OFF
  • 方式 2
SET GLOBAL slow_query_log=off;

删除慢查询日志

mysql> show variables like '%slow_query_log%';
+-----------------------------------+--------------------------------+
| Variable_name                     | Value                          |
+-----------------------------------+--------------------------------+
| slow_query_log                    | ON                             |
| slow_query_log_always_write_time  | 10.000000                      |
| slow_query_log_file               | /var/lib/mysql/zhyno1-slow.log |
| slow_query_log_use_global_control |                                |
+-----------------------------------+--------------------------------+
4 rows in set (0.00 sec)

通过以上查询可以看到慢查询日志的目录,在该目录下手动删除慢查询日志文件即可。或使用命令 mysqladmin 来删除,mysqladmin 命令的语法如下:mysqladmin -uroot -p flush-logs 执行该命令后,命令行会提示输入密码。输入正确密码后,将执行删除操作。新的慢查询日志会直接覆盖旧的查询日志,不需要再手动删除。

注意 慢查询日志都是使用 mysqladmin flush-logs 命令来删除重建的。使用时一定要注意,一旦执行了这个命令,慢查询日志都只存在新的日志文件中,如果需要旧的查询日志,就必须事先备份。

参考文章


Enjoy GreatSQL :)

关于 GreatSQL

GreatSQL 是由万里数据库维护的 MySQL 分支,专注于提升 MGR 可靠性及性能,支持 InnoDB 并行查询特性,是适用于金融级应用的 MySQL 分支版本。

相关链接: GreatSQL 社区 Gitee GitHub Bilibili

GreatSQL 社区:

捉虫活动详情:https://greatsql.cn/thread-97-1-1.html

社区博客有奖征稿详情:https://greatsql.cn/thread-100-1-1.html

与[转帖]图文结合带你搞懂 MySQL 日志之 Slow Query Log(慢查询日志)相似的内容:

[转帖]图文结合带你搞懂 MySQL 日志之 Slow Query Log(慢查询日志)

https://my.oschina.net/GreatSQL/blog/5719211 GreatSQL 社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。 GreatSQL 是 MySQL 的国产分支版本,使用上与 MySQL 一致。 作者:KAiTO 文章来源:GreatSQL 社区

[转帖]性能优化必备——火焰图

引言 本文主要介绍火焰图及使用技巧,学习如何使用火焰图快速定位软件的性能卡点。结合最佳实践实战案例,帮助读者加深刻的理解火焰图构造及原理,理解 CPU 耗时,定位性能瓶颈。 背景 当前现状 假设没有火焰图,你是怎么调优程序代码的呢?让我们来捋一下。 1. 功能开关法 想当年我刚工作,还是一个技术小白

[转帖]为华为欧拉OpenEuler系统装上图形界面,结果令人惊艳(图文)

https://zhuanlan.zhihu.com/p/432118726 阅读过上期华为欧拉系统安装图文教程的小伙伴都知道,默认情况下标准安装欧拉系统后是字符界面,对于想体验桌面版本或不会使用Linux的同学抬高了一个门槛。而本身在安装系统的过程中也没有提供图形的可选项目,可猜想欧拉目前还没有没

[转帖]使用火焰图(FlameGraph)分析程序性能

火焰图概念 火焰图(FlameGraph)是 svg 格式的矢量图,是先通过 perf 等工具分析得到结果,并将该结果生成的具有不同层次且支持互动的图片,看起来就像是火焰,这也是它的名字的由来。表现形式如下所示: 需要注意以下几点: 纵向(Y 轴)高低不平,表示的是函数调用栈的深度。每一层都是一个函

【转帖】Linux创建软连接出现的错误及解决方法

问题: 创建软连接以后,使用cd 软连接路径显示没有那个文件或目录。 创建过程及切换结果,如图: 原因: 路径一定要写成绝对路径!!! 解决办法: 了解创建软连接的命令: ln -s 源文件 目标文件或目录 而这里的源文件路径一定要写成绝对路径,造成上面结果的原因正是没有使用绝对路径的原因,正确的创

[转帖]jmeter之foreach循环控制器-03篇

上篇我们通过正则表达式获取到了一组数据,那么怎么来用呢?下面就用foreach控制器来使用结果,如下图所示 然后再foreach控制器里添加要循环的请求,我们模拟百度搜索,value填入${id} 然后运行测试,会发现请求循环执行了10次,每次的值都不一样

[转帖]解决jmeter请求响应结果乱码的问题

如下图所示,请求百度接口的时候,发现返回的信息里面中文是乱码 这个时候我们只需要改一下jmeter里的配置文件,设置响应结果的字符编码为UTF-8就行了。 进入jmeter安装目录/bin中,找到jmeter.properties这个文件,windows用文本编辑器打开,我是mac的,直接vim编辑

[转帖]zookeeper结构

目录 命名空间 作者:@dwtfukgv本文为作者原创,转载请注明出处:https://www.cnblogs.com/dwtfukgv/p/14644592.html 命名空间 zookeeper的命名空间和文件系统比较类似,它的每个节点被称作znode,每个znode可以存储1MB的数据,如下图

【转帖】《MySQL高级篇》四、索引的存储结构

1. 为什么使用索引 假如给数据使用 二叉树 这样的数据结构进行存储,如下图所示 2、索引及其优缺点 2.1 索引概述 2.2 优点 类似大学图书馆建书目索引,提高数据检索的效率,降低 数据库的 IO 成本 这也是创建索引的主要的原因。通过创建唯一索引,可以保证数据库表中每一行 数据的唯一性 (唯一

【转帖】3.JVM内存结构概述

目录 1.JVM内存结构 1.JVM内存结构 在JVM系列的第一篇文章中已经给出了JVM内存结构的简图,下面是JVM内存结构更加详细的图。 同样,JVM的内存结构可以分为上中下3层。 上层主要是类加载子系统,负责将字节码文件加载到内存中。 类加载又分为具体的三个环节,加载(loading)、链接(l