# 查询是否开启慢查询 mysql> show variables like '%slow_query_log%'; +---------------------+-----------------------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log | +---------------------+-----------------------------------------------+ 2 rows in set (0.00 sec)
# 开启慢查询 mysql> set global slow_query_log=1; Query OK, 0 rows affected (0.09 sec)
mysql> show variables like 'slow_query_log%'; +---------------------+-----------------------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log | +---------------------+-----------------------------------------------+ 2 rows in set (0.00 sec)
# 查询慢查询日志记录阈值 mysql> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
# 设置新的阈值 mysql> set global long_query_time=5; Query OK, 0 rows affected (0.00 sec)
# 新的阈值在新的连接中才会生效，这里需要断开重连或者使用新的连接查询 mysql> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
# 查询是否将未使用索引的查询也被记录到慢查询日志中 mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-----------+ | Variable_name | Value | +-------------------------------+-----------+ | log_queries_not_using_indexes | ON | +-------------------------------+-----------+ 1 row in set (0.00 sec)
# 查询日志存储方式 mysql> show variables like 'log_output'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | log_output | FILE | +-----------------+-----------+ 1 row in set (0.00 sec)
# 查询有多少条慢查询记录 mysql> show global status like '%Slow_queries%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 2104 | +---------------+-------+ 1 row in set (0.00 sec)
慢查询日志分析
慢查询日志分析工具基本分为两种，一种是 Mysql 自带的 mysqldumpslow 一种是第三方的 pt-query-digest
[root@DB-Server ~]# 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
-s：是表示按照何种方式排序
c：查询次数
l：锁定时间
r：返回行数
t：查询时间
al：平均锁定时间
ar：平均返回行数
at：平均查询时间
-t：表示 top n，返回前 n 条数据
-g：匹配一个查询，可以匹配正则，忽略大小写
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
# 返回记录集最多的10个SQL。 > mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
# 访问次数最多的10个SQL > mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
