前言
简单学习了下慢日志分析,简单做下笔记,以便后期方便查看。通常情况下,我们应该优化查询次数多且耗时多的语句,或者扫描行数多的语句优化,或者缓存命中率(查询结果记录数/扫描记录数)低的语句。
帮助:(/usr/local/mysql/bin/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 取排序的前NUM个-a don't abstract all numbers to N and strings to 'S' 不要将所有数字归为N,字符串归为S-n NUM abstract numbers with at least n digits within names-g PATTERN grep: only consider stmts that include this string 过滤模式 相当于grep作用-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard)-i NAME name of server instance (if using mysql.server startup script)-l don't subtract lock time from total time 不要减除锁时间
日志的结构
# User@Host: root[root] @ localhost [] # Query_time: 1.098060 Lock_time: 0.059297 Rows_sent: 0 Rows_examined: 5416 use db_msg; SET timestamp=1489657202; //查询时间 DELETE FROM t_msg_recv_5 WHERE expire_time < UNIX_TIMESTAMP() AND channel!='ShangSou' LIMIT 500;
简单例子及说明
命令及输出:/usr/local/mysql/bin/mysqldumpslow -s t -t 1 /data/mysql-slow.log
Reading mysql slow query log from /data/mysql-data/mysql-slow.log Count: 389594 Time=4.52s (1762523s) Lock=0.07s (25931s) Rows=1.0 (388500), x[x]@[ip]SELECT seq_num FROM t_msg_max_num WHERE user_id = "S" AND channel = "S"
说明:
1. Count: 该类型查询执行总次数 2. Time=4.52s (1722323s):4.52s指该类型查询执行最长时间,1722323s该类型查询总执行时间 3. Lock=0.07s (25931s): 0.07s平均锁时间 ,25931s总时间 4. Rows=1.0 (388500):类似的,1.0为平均发送给客户端行数记录,388500发送给客户端总行数
系统变量log-queries-not-using-indexes:
未使用索引的查询也被记录到慢查询日志中(可选项)。如果调优的话,建议开启这个选项。另外,开启了这个参数,其实使用full index scan的sql也会被记录到慢查询日志。
系统变量log_slow_admin_statements:
是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志