使用 MySQL自带命令 mysqldumpslow 查看
OPTIONS
- -s ORDER ORDER, 主要有 c, t, l, r 和 ac, at, al, ar, 分别是按照 query次数, 时间, lock的时间和返回的记录数来排序, 前面加了a时倒序.
- -t NUM top NUM, 即为返回前面多少条的数据.
- -g PATTERN grep: 后边可以写一个正则匹配模式, 大小写不敏感
#查看访问次数最多的 20 个 sql 语句 [root@localhost ~]# mysqldumpslow -s c -t 20 /usr/local/mysql/var/mysql_slow_query.log #查看返回记录集最多的 20 个 sql [root@localhost ~]# mysqldumpslow -s r -t 20 /usr/local/mysql/var/mysql_slow_query.log #按照时间返回前 10 条里面含有左连接的 sql 语句 [root@localhost ~]# mysqldumpslow -t 10 -s t -g "LEFT JOIN" /usr/local/mysql/var/mysql_slow_query.log
mysqlsla 分析 MySQL 慢查询日志
- mysqlsla -lt slow /usr/local/mysql/var/mysql_slow_query.log slow log
- mysqlsla -lt general /usr/local/mysql/var/mysql_query.log general log
- mysqlbinlog /usr/local/mysql/var/mysql-bin.000001 | mysqlsla -lt binary – binary log
mysqlsla(现已不在维护)是第三方提供的perl脚本, 不过它功能强悍, 可以分析包括慢查询在内的多种格式的日志.
[root@localhost tmp]# wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz [root@localhost tmp]# tar zxf mysqlsla-2.03.tar.gz [root@localhost tmp]# cd mysqlsla-2.03 [root@localhost mysqlsla-2.03]# perl Makefile.PL [root@localhost mysqlsla-2.03]# make [root@localhost mysqlsla-2.03]# make install
安装完成后, mysqlsla 会加入到 /usr/bin 目录.
mysqlsla 会自动判断日志类型, 缺省会打印出前十条结果, 可以通过类似–top 100的参数来修改, 如果觉得每次输入麻烦, 还可以建立一个配置文件 ~/.mysqlsla, 在文件里写上:
top=100
这样就不用每次都手动输入参数了.
[root@localhost mysqlsla-2.03]# mysqlsla -lt slow /usr/local/mysql/var/mysql_slow_query.log
Report for slow logs: /usr/local/mysql/var/mysql_slow_query.log
4 queries total, 2 unique
Sorted by \'t_sum\'
Grand Totals: Time 11 s, Lock 0 s, Rows sent 712.40k, Rows Examined 712.40k
______________________________________________________________________ 001 ___
Count : 3 (75.00%)
Time : 8 s total, 2.666667 s avg, 2 s to 4 s max (72.73%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
Rows sent : 178.10k avg, 178.10k to 178.10k max (75.00%)
Rows examined : 178.10k avg, 178.10k to 178.10k max (75.00%)
Database : test
Users :
root@localhost : 66.67% (2) of query, 75.00% (3) of all users
root1@localhost : 33.33% (1) of query, 25.00% (1) of all users
Query abstract:
SELECT * FROM test_1;
Query sample:
select * from test_1;
______________________________________________________________________ 002 ___
Count : 1 (25.00%)
Time : 3 s total, 3 s avg, 3 s to 3 s max (27.27%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
Rows sent : 178.10k avg, 178.10k to 178.10k max (25.00%)
Rows examined : 178.10k avg, 178.10k to 178.10k max (25.00%)
Database :
Users :
root@localhost : 100.00% (1) of query, 75.00% (3) of all users
Query abstract:
SELECT * FROM test_2;
Query sample:
select * from test_2;
转载请注明：爱开源 » 查看 MySQL 慢日志