如何合理配置MySQL Slow Query Log - 文章 - 开发者社区

问题描述

如何正确配置 MySQL 的 slow query log？

常见参数说明

1.min_examined_row_limit

测试配置文件如下

###slow_log
long_query_time=1      //条件1：时间需要大于1s
slow_query_log=1
slow_query_log_file=/data/3306/data/slow.log
min_examined_row_limit=2000             //条件2：检查扫描的行数>2000
log_queries_not_using_indexes=1

需要同时满足条件1和条件2才会被记录到slow.log中，利用benchmark函数来验证

mysql> select benchmark(100000000,1000000*1000000);
+--------------------------------------+
| benchmark(100000000,1000000*1000000) |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (1.44 sec)

执行时间为1.44秒但是没有被记录到slow.log日志中

注释掉min_examined_row_limit参数后，slow.log中会有日志记录如下

# Time: 2021-10-29T02:10:58.593293Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
# Query_time: 1.439743  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1635473458;
select benchmark(100000000,1000000*1000000);

2.log_queries_not_using_indexes

即使不满足条件1（slow_query_time）和条件2（min_examined_row_limit)的条件没有使用索引也会记录到慢日志中示例查询语句：

mysql> select * from mysql.db\G;
*************************** 1. row ***************************
                 Host: %
                   Db: p1_01
                 User: p1_role_readonly
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: localhost
                   Db: performance_schema
                 User: mysql.session
                 ……
                 ……

slow.log日志记录（即使时间只是0.000215，检查的行数只有2行），但是因为进行了全表扫面所以也会记录到slow.log日志中。

# Time: 2021-10-29T02:14:24.200366Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000215  Lock_time: 0.000118 Rows_sent: 2  Rows_examined: 2
use mysql;
SET timestamp=1635473664;
select * from db;

2.1 log_throttle_queries_not_using_indexes

抑制未使用索引的慢查询记录（因为开了log_queries_not_using_indexes参数后日志量记录会比较多）

配置文件如下：
###slow_log
long_query_time=1
slow_query_log=1
slow_query_log_file=/data/3306/data/slow.log
#min_examined_row_limit=2000
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=5   //1分钟内超过5条未时候索引的慢查询，后续的慢日志记录会抑制

测试语句，连续执行mysql> select * from db;的全表扫描语句

慢日志记录如下：

# Time: 2021-10-29T02:30:04.440644Z      //02:30分钟的时候发生开始产生日志
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000174  Lock_time: 0.000091 Rows_sent: 2  Rows_examined: 2
use mysql;
SET timestamp=1635474604;
select * from db;
# Time: 2021-10-29T02:30:10.605822Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2


中间大约也执行了4条，但是一直没有日志记录
//产生5条日志后，后面不在有日志输出，知道接近一分钟02：32的时候，报错throttle
# Time: 2021-10-29T02:32:08.332210Z
# User@Host: [] @  []  Id:     2
# Query_time: 0.001029  Lock_time: 0.000472 Rows_sent: 2  Rows_examined: 2
SET timestamp=1635474728;
//这里会有如下的告警，表是slowlog的日志记录已经被压缩
rottle:          6 'index not used' warning(s) suppressed.;

2.2 log-short-format

服务器将用较少的信息写入慢查询日志

//修改前
# Query_time: 0.000236  Lock_time: 0.000081 Rows_sent: 2  Rows_examined: 2
SET timestamp=1635474728;
select * from db;
/usr/local/mysql/bin/mysqld, Version: 5.7.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /data/3306/data/tmp/mysql.sock
Time                 Id Command    Argument

//修改后
# Query_time: 0.000133  Lock_time: 0.000072 Rows_sent: 2  Rows_examined: 2
use mysql;
SET timestamp=1635487528;
select * from db;

3.log_slow_admin_statements

ALTER TABLE， ANALYZE TABLE， CHECK TABLE， CREATE INDEX， DROP INDEX， OPTIMIZE TABLE，和 REPAIR TABLE这些语句也会被记录。

参考文档

[1] https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

如果您有其他问题，欢迎您联系火山引擎技术支持服务