TheRiver | blog

You have reached the world's edge, none but devils play past here

0%

mysql slow log

Posted on Edited on
Symbols count in article: 6.2k Reading time ≈ 6 mins.

参考

MySQL实战45讲

mysql技术内幕

查看配置

1
2
3
4
5
6
7
8
MySQL [mysql]> show variables like '%slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /usr/local/mysql/log/slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
[root@xxxx ]# cat /etc/my.cnf
[mysqld]
...
slow_query_log=1
slow_query_log_file=/usr/local/mysql/log/slow.log
long_query_time=1

运行时间>long_query_time的语句会被记录下来，和postgresql的日志文件有点像。

log_queries_not_using_indexes

该参数打开的话，没有使用索引的语句也会被打印出来。

1
2
3
4
5
6
7
MySQL [mysql]> show variables like '%using_in%';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | OFF   |
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+

log_throttle_queries_not_using_indexes

该参数表示每分钟允许记录到slow log的未使用索引的语句次数，默认是0表示没限制，这个参数是mysql5.6.5版本引入的。

mysqldumpslow

该工具有助于分析slow log,参数如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@xxxx ]# /usr/local/mysql/mysql/bin/mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

table slow_Log

打开存放满日志到table slow_log的开关：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MySQL [mysql]> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

MySQL [mysql]> set log_output = 'TABLE';
ERROR 1229 (HY000): Variable 'log_output' is a GLOBAL variable and should be set with SET GLOBAL
MySQL [mysql]> set GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.02 sec)
1
2
3
4
5
6
7
8
9
MySQL [mysql]> select * from slow_log;
+----------------------------+------------------------------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+
| start_time                 | user_host                                      | query_time      | lock_time       | rows_sent | rows_examined | db    | last_insert_id | insert_id | server_id | sql_text        | thread_id |
+----------------------------+------------------------------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+
| 2020-07-31 16:28:22.604167 | skip-grants user[root] @ localhost [127.0.0.1] | 00:00:02.001130 | 00:00:00.000000 |         1 |             0 | mysql |              0 |         0 |         0 | select sleep(2) |         2 |
| 2020-08-20 15:30:33.876872 | skip-grants user[root] @ localhost [127.0.0.1] | 00:00:03.000253 | 00:00:00.000000 |         1 |             0 | mysql |              0 |         0 |         1 | select sleep(3) |        12 |
| 2020-08-20 15:30:55.647108 | skip-grants user[root] @ localhost [127.0.0.1] | 00:00:04.000252 | 00:00:00.000000 |         1 |             0 | mysql |              0 |         0 |         1 | select sleep(4) |        12 |
+----------------------------+------------------------------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+
3 rows in set (0.00 sec)

表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MySQL [mysql]> show create table slow_log;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| slow_log | CREATE TABLE `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

跟pgsql的统计表很像。不过没找到命中率之类的统计信息。

ending

----------- ending -----------