title: 配置mysql slow和slow log 分析
date: 2016-01-08
comments: true
category: DB
tags: MySQL,slow log
配置mysql slow
Mysql 5.5
log-slow-queries = F:/MySQL/log/mysqlslowquery.log
long_query_time = 2-
Mysql 5.6
show variables like '%slow_query%';
slow-query-log=1/on
slow-query-log-file=mysql-slow.logshow global variables like '%long_query%'; long_query_time=2 show global variables like '%indexes%'; log-queries-not-using-indexes show variables like '%min_examined_row_limit%'; min_examined_row_limit=0
analyse MySQL slow log
直接查詢
pt-query-digest slow.log > /tmp/slow-report.log最近12小時
pt-query-digest --since=12h slow.log > /tmp/slow-report.log按時間時間范圍
pt-query-digest slow.log --since '2016-01-09 09:30:00' --until '2016-01-09 10:00:00' > /tmp/slow-report.log分析指定含有select語句的慢查詢
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log > /tmp/slow-report.log針對某個用戶的慢查詢
pt-query-digest --filter '($event->{user}|| "") =~ m/^root/i' slow.log > /tmp/slow-report.log查詢所有的全表掃描 或 full join的慢查詢
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log > /tmp/slow-report.log把查詢保存到query_review表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table slow.log > /tmp/slow-report.log把查詢結(jié)果保存到query_history表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ history--create-review-table slow.log_20140401
pt-query-digest --user=root –password=abc123--review h=localhost,D=test,t=query_history--create-review-table slow.log_20140402通過tcpdump抓取mysql的tcp協(xié)議數(shù)據(jù)感混，然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log分析general log
pt-query-digest --type=genlog localhost.log > slow_report11.log
