mysql5.5慢日志设置和查询

3254rf

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下方添加以下参数:

1 2 3 4 5

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)

查看日志是否记录

[iyunv@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);

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

1 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工具查询慢日志

[iyunv@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

1 2 3 4 5 6 7 8 9 10 11 12 13 14

[iyunv@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