Myqsl慢查询日志和pt-query-design分析工具

一、慢日志查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

#查看是否开启慢查询日志记录 
SHOW VARIABLES like 'slow_query_log';
#开启
set global slow_query_log = on;

#未使用索引的查询也被记录到慢查询日志中，on表示开启，off表示关闭(默认值)。
show VARIABLES like 'log_queries_not_using_indexes';
#开启
set global log_queries_not_using_indexes = on;

#慢查询阈值（秒级），当查询时间大于设定的阈值时，记录日志。
show VARIABLES like 'long_query_time';
#设置未0.全部查看
set global long_query_time = 0;
#慢查询日志存储路径
show variables like 'slow_query_log_file';

慢查询默认是关闭的，这个时候mysql 服务器就会开始搜集日志记录，开启慢查询日志，也不需要考虑因为这个造成的性能影响，推荐优化后关闭就可以了

二、安装pt-query-design

pt-query-design对windows的环境支持性不是很好，推荐使用linux。以安装centos 为例：
#快速安装
wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y percona-toolkit-2.2.16-1.noarch.rpm

三、分析慢查询日志

pt-query-digest /data/mysql/data/db-3-12-slow.log
第一部分 ：总体分析结果
Exec time：执行时间
Lock time：锁定时间
Rows sent：发送行数
Rows examine：扫描行数
Query size：查询字符数

第二部分：查询分组统计结果
Rank：所有语句的排名，默认按查询时间降序排列，通过–order-by指定
Query ID：语句的ID，（去掉空格和查询条件中的文本值，计算hash值）
Response：总的响应时间
time：该查询在本次分析中总的时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：方差均值比（Variance-to-mean），也就是常说的离差指数。
Item：查询对象（简易方便分析大概是那条sql）

第三部分：每一种查询的详细统计结果
查询各项数据的百分比、总数、最小、最大、平均、95%等各项目的统计，包括SQL执行次数、执行时间、锁占用时间、发送行数、扫描行数、查询字符数，表格中也统计了查询涉及的数据库、查询时间直方图等信息。

四、常见的优化点

1.查询次数多，且每次查询占用时间长的SQL：通常为pt-query-digest分析的前几个查询
2.IO大的SQL：注意pt-query-digest分析中的Rows examine
3.未使用索引的SQL：通过pt-query-digest分析中的Rows examine与Rows Send对比

五、工具使用简介

1.查看服务器信息

pt-summary

详细文档 ：http://www.percona.com/doc/percona-toolkit/2.2/pt-summary.html

2.查看磁盘开销使用信息

pt-diskstats

详细文档 ：http://www.percona.com/doc/percona-toolkit/2.2/pt-diskstats.html

3.查看mysql数据库信息

pt-mysql-summary --user=root --password=123456

详细文档 ：http://www.percona.com/doc/percona-toolkit/2.2/pt-mysql-summary.html

4.分析慢查询日志

pt-query-digest /data/mysql/data/db-3-12-slow.log

详细文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

5.查找mysql的从库和同步状态

pt-slave-find --host=localhost --user=root --password=123456

详细文档 ：http://www.percona.com/doc/percona-toolkit/2.2/pt-slave-find.html

6.查看mysql的死锁信息

pt-deadlock-logger --user=root --password=123456 localhost

详细文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-deadlock-logger.html

7.从慢查询日志中分析索引使用情况

pt-index-usage slow_20131009.log

详细文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-index-usage.html

8.查找数据库表中重复的索引

pt-duplicate-key-checker --host=localhost --user=root --password=123456

详细文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-duplicate-key-checker.html

9.查看mysql表和文件的当前活动IO开销

pt-ioprofile

详细文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-ioprofile.html

10.查看不同mysql配置文件的差异

pt-config-diff /etc/my.cnf /etc/my_master.cnf

详细文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-config-diff.html

11. pt-find查找mysql表和执行命令，示例如下

查找数据库里大于2G的表：

pt-find --user=root --password=123456 --tablesize +2G

查找10天前创建，MyISAM引擎的表：

pt-find --user=root --password=123456 --ctime +10 --engine MyISAM

查看表和索引大小并排序

pt-find --user=root --password=123456 --printf “%T\t%D.%N\n” | sort -rn

详细文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-find.html

12. pt-kill 杀掉符合标准的mysql进程

显示查询时间大于60秒的查询

pt-kill --user=root --password=123456 --busy-time 60 --print

kill掉大于60秒的查询

pt-kill --user=root --password=123456 --busy-time 60 --kill

详细文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-kill.html

13. 查看mysql授权

pt-show-grants --user=root --password=123456
pt-show-grants --user=root --password=123456 --separate --revoke
详细文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-show-grants.html

14）验证数据库复制的完整性

pt-table-checksum --user=root --password=123456