如何开启MySQL慢查询日志

本文涉及的产品
对象存储 OSS,20GB 3个月
阿里云盘企业版 CDE,企业版用户数5人 500GB空间
日志服务 SLS,月写入数据量 50GB 1个月
简介:

前言

数据库日志记录了用户对数据库的各种操作及数据库发生的各种事件。能帮助数据库管理员追踪、分析问题。MySQL提供了错误日志、二进制日志、查询日志、慢查询日志。

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值(long_query_time,单位:秒)的SQL语句。默认情况下,MySQL不启动慢查询日志。本文简单介绍如何开启慢查询日志,如何用mysqldumpslow分析慢查询。

开启慢查询日志

修改my.cnf

在配置文件my.cnf(一般为/etc/my.cnf)中的[mysqld] section增加如下参数。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow-query.log   # 若没有指定,默认名字为hostname_slow.log
long_query_time = 1

log_queries_not_using_indexes = 1

其中,

  • slow_query_log = 1

    • 表示开启慢查询,0表示关闭
  • slow_query_log_file

    • 指定慢查询日志路径
    • 需要MySQL对该路径有写权限
  • long_query_time = 1

    • 表示查询时间>=1秒才记录日志
    • 默认10s
  • log_queries_not_using_indexes = 1

    • 表明记录没有使用索引的 SQL 语句

重启MySQL服务

# 重启
$ sudo service mysqld restart

# 重启后进程如下

root     22373  0.0  0.0  66064  1424 pts/3    S    16:59   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql
mysql    22721  0.3  0.5 890996 467040 pts/3   Sl   16:59   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

重启MySQL后会看到/var/lib/mysql/slow-query.log文件。

检查参数

通过如下命令可以检查上述参数配置情况。

mysql> show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/lib/mysql/slow-query.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

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

测试

慢查询

制造慢查询并执行。如下。

mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

慢查询日志

打开慢查询日志文件。可以看到上述慢查询的SQL语句被记录到日志中。

# Time: 180620 17:13:06
# User@Host: apsara[apsara] @ dc1487859883577.et2sqa [11.239.51.96]  Id:     3
# Query_time: 1.000246  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1529485986;
select sleep(1);

慢查询分析工具

mysqldumpslow

mysqldumpslow是MySQL自带的分析慢查询的工具。该工具是Perl脚本。

常用参数如下。

-s:排序方式,值如下
    c:查询次数
    t:查询时间
    l:锁定时间
    r:返回记录
    ac:平均查询次数
    al:平均锁定时间
    ar:平均返回记录书
    at:平均查询时间
-t:top N查询
-g:正则表达式

例子

  • 我们执行了多次类似如下的查询。
select * from db_user where name like 'zb%';
select * from db_user where name like 'aaa%';
select * from db_user where name like 'bc%';
...
  • 获取访问次数最多的5个SQL语句
$ mysqldumpslow -s c -t 5 /var/lib/mysql/slow-query.log

Reading mysql slow query log from /var/lib/mysql/slow-query.log
Count: 15  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), apsara[apsara]@dc1487859883577.et2sqa
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select * from db_user where name like 'S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), apsara[apsara]@dc1487859883577.et2sqa
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use test;
  SET timestamp=N;
  select * from db_user where name like 'S'
  • 按照时间排的top 5个SQL语句
$ mysqldumpslow -s t -t 5 /var/lib/mysql/slow-query.log
  • 按照时间排序且含有'like'的top 5个SQL语句
$ mysqldumpslow -s t -t 3 -g "like" /var/lib/mysql/slow-query.log

小结

默认情况下,MySQL不启动慢查询日志。若要检查慢查询,需要我们手动设置这个参数。一般情况下,若非调优需要,不建议启动该参数,因为开启慢查询日志或多或少会带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

Reference

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
17天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
125 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
12天前
|
关系型数据库 MySQL 数据库
【赵渝强老师】MySQL的binlog日志文件
MySQL的binlog日志记录了所有对数据库的更改操作(不包括SELECT和SHOW),主要用于主从复制和数据恢复。binlog有三种模式,可通过设置binlog_format参数选择。示例展示了如何启用binlog、设置格式、查看日志文件及记录的信息。
|
11天前
|
SQL 关系型数据库 MySQL
【赵渝强老师】MySQL的慢查询日志
MySQL的慢查询日志用于记录执行时间超过设定阈值的SQL语句,帮助数据库管理员识别并优化性能问题。通过`mysqldumpslow`工具可查看日志。本文介绍了如何检查、启用及配置慢查询日志,并通过实例演示了慢查询的记录与分析过程。
|
23天前
|
监控 关系型数据库 MySQL
分析慢查询日志
【10月更文挑战第29天】分析慢查询日志
37 3
|
23天前
|
监控 关系型数据库 数据库
怎样分析慢查询日志?
【10月更文挑战第29天】怎样分析慢查询日志?
34 2
|
13天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL的撤销日志文件和错误日志文件
本文介绍了MySQL的物理存储结构,重点讲解了InnoDB存储引擎中的撤销日志文件(undo log)和错误日志文件。从MySQL 8.0开始,默认生成两个10MB的undo表空间文件,并支持动态扩容和收缩。错误日志文件记录了MySQL启动、运行、关闭过程中的问题,通过示例展示了如何查看和使用这些日志。
|
2月前
|
存储 关系型数据库 MySQL
MySQL中的Redo Log、Undo Log和Binlog:深入解析
【10月更文挑战第21天】在数据库管理系统中,日志是保障数据一致性和完整性的关键机制。MySQL作为一种广泛使用的关系型数据库管理系统,提供了多种日志类型来满足不同的需求。本文将详细介绍MySQL中的Redo Log、Undo Log和Binlog,从背景、业务场景、功能、底层实现原理、使用措施等方面进行详细分析,并通过Java代码示例展示如何与这些日志进行交互。
92 0
|
存储 SQL 关系型数据库
慢查询与MySQL语句优化(下)
如果我们了解了Mysql中的索引原理之后,(详见探秘数据库 —— 事务 + InnoDB存储引擎),如何利用索引并对一些执行较慢的sql进行优化也是必要的,所以我们可以结合索引的原理来探究一下慢查询与优化的知识。
慢查询与MySQL语句优化(下)
|
存储 SQL 关系型数据库
慢查询与MySQL语句优化(上)
如果我们了解了Mysql中的索引原理之后,(详见探秘数据库 —— 事务 + InnoDB存储引擎),如何利用索引并对一些执行较慢的sql进行优化也是必要的,所以我们可以结合索引的原理来探究一下慢查询与优化的知识。
慢查询与MySQL语句优化(上)
|
16天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
29 1

相关产品

  • 日志服务
  • 下一篇
    无影云桌面