配置mysql slow和slow log 分析

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.log

  show 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

• 把查询结果保存到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协议数据，然后再分析
  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