mysql慢查询

mysql慢查询日志默认是关闭的

 

mysql> show variables like '%slow_query_log%';

 

+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------+-------------------------------+

 

开启慢查询日志

mysql> set global slow_query_log=1;

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------+-------------------------------+

 

默认的慢查询日志名为: $HOSTNAME-slow.log

上面的设置只对当前库生效.若想永久生效. 则需要修改my.cnf配置文件

 

在[mysqld] 下面添加 

slow_query_log=1
slow_query_log_file=/var/lib/mysql/mysql-slow.log

 

 

慢查询sql的时间阈值:

mysql> show variables like '%long_query_time%';

 

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

默认大于10秒.

 

设置慢查询阈值 

mysql> set global long_query_time =1;

 

查询修改后的阈值,可以使用global查询或新开的一个会话

mysql> show global variables like '%long_query_time%';

mysql> show global variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

 

查询系统慢查询sql记录

mysql> show global status like '%Slow_queries%';

mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+

 

使用mysqldumpslow工具分析慢查询日志

[root@mysql ~]# 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