mysql5.5慢日志设置和查询

mysql> showvariables like '%version%';

+-------------------------+---------------------+

| Variable_name           | Value               |

+-------------------------+---------------------+

|innodb_version          | 5.5.31              |

|protocol_version        | 10                  |

|slave_type_conversions  |                     |

| version                 | 5.5.31-log          |

|version_comment         | Sourcedistribution |

|version_compile_machine | x86_64             |

|version_compile_os      | Linux               |

+-------------------------+---------------------+

7 rows in set (0.00sec)

 

开启慢查询日志

在mysql的配置文件my.cnf 中的mysqld下方添加以下参数:

log_slow_queries=ON
long_query_time=1
slow_launch_time=1
slow_query_log=ON
slow_query_log_file=/log/mysql/slow_queries.log

 

注：

long_query_time=1 表示记录查询时间超过1秒的sql

slow_query_log_file 慢日志文件

slow_launch_time : 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加

 

 

重启mysql后检查结果

mysql> showvariables like '%slow%';

+---------------------+-----------------------------+

| Variable_name       | Value                       |

+---------------------+-----------------------------+

|log_slow_queries    | ON                          |

|slow_launch_time    | 1                           |

|slow_query_log      | ON                          |

|slow_query_log_file | /log/mysql/slow_queries.log |

+---------------------+-----------------------------+

4 rows in set (0.00sec)

 

mysql> showvariables like '%long%';

+---------------------------------------------------+----------+

| Variable_name                                     |Value    |

+---------------------------------------------------+----------+

|long_query_time                                   | 1.000000 |

|max_long_data_size                                | 1048576  |

|performance_schema_events_waits_history_long_size | 10000    |

+---------------------------------------------------+----------+

3 rows in set (0.00sec)

 

 

测试

mysql> selectsleep(2);

+----------+

| sleep(2) |

+----------+

|        0 |

+----------+

1 row in set (2.00sec)

 

查看日志是否记录

[root@HE1 /]# tail-f /log/mysql/slow_queries.log

# Time: 16021722:26:54

# User@Host:root[root] @ localhost []

# Query_time:2.000661  Lock_time: 0.000000 Rows_sent:1  Rows_examined: 0

SETtimestamp=1455776814;

select sleep(2);

 

 

设置好参数后，以后可通过

mysql>set GLOBAL slow_query_log=1;开启慢日志
mysql> set GLOBAL slow_query_log=0;关闭慢日志

 

是否记录没用到索引的SQL语句

mysql> showvariables like '%using%';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

|log_queries_not_using_indexes | OFF   |

+-------------------------------+-------+

1 row in set (0.00sec)

 

mysql> set globallog_queries_not_using_indexes=1;

Query OK, 0 rowsaffected (0.00 sec)

 

mysql> showvariables like '%using%';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

|log_queries_not_using_indexes | ON    |

+-------------------------------+-------+

1 row in set (0.00sec)

 

通过mysqldumpslow工具查询慢日志

 

[root@HE1 bin]#mysqldumpslow  --help

Usage: mysqldumpslow[ OPTS... ] [ LOGS... ]

 

Parse and summarizethe 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

 

 

 

 

[root@HE1 bin]#mysqldumpslow -s at -n 3 /log/mysql/slow_queries.log
 
Reading mysql slowquery log from /log/mysql/slow_queries.log
Count: 3  Time=2.33s (7s)  Lock=0.00s (0s)  Rows=1.0 (3), root[root]@localhost
  select sleep(N)
 
Count: 1  Time=0.37s (0s)  Lock=0.44s (0s)  Rows=0.0 (0), root[root]@localhost
  create table helei as select * frommysql.help_keyword
 
Count: 2  Time=0.11s (0s)  Lock=0.03s (0s)  Rows=0.0 (0), root[root]@localhost
  insert into helei  select * from mysql.help_keyword
 
Count: 2  Time=0.04s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  select count(*) from helei