mysq-dba应掌握的l管理工具-pt-tool

浏览数：28 / 时间：2015年06月12日

pt-query-digest

pt-query-digest可以从普通MySQL日志，慢查询日志以及二进制日志中分析查询，甚至可以从SHOW PROCESSLIST和MySQL协议的tcpdump中进行分析，如果没有指定文件，它从标准输入流（STDIN）中读取数据。

最简单的用法如下：

pt-query-digest slow.logs

输出信息大致如下:

技术分享

整个输出分为三大部分：

1、整体概要（Overall）

技术分享

这个部分是一个大致的概要信息(类似loadrunner给出的概要信息)，通过它可以对当前MySQL的查询性能做一个初步的评估，比如各个指标的最大值(max)，平均值(min)，95%分布值，中位数(median)，标准偏差(stddev)。这些指标有查询的执行时间（Exec time），锁占用的时间（Lock time），MySQL执行器需要检查的行数（Rows examine），最后返回给客户端的行数（Rows sent），查询的大小。

2、查询的汇总信息（Profile）

这个部分对所有”重要”的查询(通常是比较慢的查询)做了个一览表:

技术分享

每个查询都有一个Query ID，这个ID通过Hash计算出来的。pt-query-digest是根据这个所谓的Fingerprint来group by的。举例下面两个查询的Fingerprint是一样的都是select * from table1 where column1 = ?，工具箱中也有一个与之相关的工具pt-fingerprint。

select * from table1 where column1 = 2
select * from table1 where column1 = 3

Rank整个分析中该“语句”的排名，一般也就是性能最常的。
Response time “语句”的响应时间以及整体占比情况。
Calls 该“语句”的执行次数。
R/Call 每次执行的平均响应时间。
V/M 响应时间的差异平均对比率。

在尾部有一行输出，显示了其他2个占比较低而不值得单独显示的查询的统计数据。

3、详细信息

这个部分会列出Profile表中每个查询的详细信息：

技术分享

包括Overall中有的信息、查询响应时间的分布情况以及该查询”入榜”的理由。

pt-query-digest还有很多复杂的操作，这里就不一一介绍了。比如：从PROCESSLIST中查询某个MySQL中最慢的查询：

pt-query-digest –processlist h=host1

从tcpdump中分析：

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 log保存到另外一台机器上待稍后详细分析：

pt-query-digest --review h=host2 --no-report slow.log

还可以跟一些过滤条件。详见官方文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

另外结合一些第三方工具还能生成相应的报表，可以参考这里：http://biancheng.dnbcw.info/mysql/433514.html

建议：当slow log很大的时候最好还是将日志文件移到其他机器上进行分析。

pt-index-usage

这个工具主要是用来分析查询的索引使用情况。

pt-index-usage slow_query.log --h localhost --password 123456

详细的用法 –help查看再对照官网就差不再赘述。

注意使用这个工具需要MySQL必须要有密码，另外运行时可能报找不到/var/lib/mysql/mysql.sock的错，简单的从/tmp/mysql.sock链接一个就行了。

重点要说明的是pt-index-usage只能分析慢查询日志，所以如果想全面分析所有查询的索引使用情况就得将slow_launch_time设置为0，因此请谨慎使用该工具，线上使用的话最好在凌晨进行分析，尤其分析大量日志的时候是很耗CPU的。

整体来说这个工具是不推荐使用的，要想实现类似的分析可以考虑一些其他第三方的工具，比如：mysqlidxchx, userstat和check-unused-keys。网上比较推荐的是userstat，一个Google贡献的patch。

Oracle是可以将执行计划保存到性能视图中的，这样分析起来可能更灵活，但是目前我还没找到MySQL中类似的做法。

pt-upgrade

这个工具用来检查在新版本中运行的SQL是否与老版本一样，返回相同的结果，最好的应用场景就是数据迁移的时候。

pt-upgrade h=host1 h=host2 slow.log

pt-query-advisor

静态查询分析工具。能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议。这个工具好像2.2的版本给去掉了，有可能是因为对性能影响比较大新版本直接去掉了。

总结:上面这些工具最好不要直接在线上使用，应该作为上线辅助或故障后离线分析的工具，也可以做性能测试的时候配合着使用。

SHOW PROFILE

SHOW PROFILE是Google高级架构师Jeremy Cole贡献给MySQL社区的，它可以用来MySQL执行语句时候所使用的资源。默认是关闭的，需要打开执行下面的语句：

set profiling = 1;#这个命令只在本会话内起作用。

执行简单的SHOW PROFILES可以看到打开profiling之后所有查询的执行时间。

技术分享

执行SHOW PROFILE [TYPE] FOR QUERY Query_ID可以看到MySQL执行某个查询各个步骤的各项性能指标的详细信息：

技术分享

如果没有指定FOR QUERY则显示最近一条查询的详细信息。TYPE是可选的，有以下几个选项：

ALL 显示所有性能信息
BLOCK IO 显示块IO操作的次数
CONTEXT SWITCHES 显示上下文切换次数，不管是主动还是被动
CPU 显示用户CPU时间、系统CPU时间
IPC 显示发送和接收的消息数量
MEMORY [暂未实现]
PAGE FAULTS 显示页错误数量
SOURCE 显示源码中的函数名称与位置
SWAPS 显示SWAP的次数

MySQL在执行查询语句的时候会有很多步骤，这里就不一一赘述了，用到的时候网上搜下就行。需要特别说明的是Sending data这个步骤，给人感觉是MySQL把数据发送给客户端的耗时，其实不然，这个步骤包括了MySQL内部各个存储之间复制数据的过程，比如硬盘的寻道。

总结：前面的慢查询日志分析更像是对整个MySQL查询情况做一个全面的检查，而SHOW PROFILE则是对单个查询语句的剖析，通常当现网出现问题时应该结合二者。使用慢查询分析定位到具体的查询，使用SHOW PROFILE定位到具体的问题，是Sending data比较耗时还是System lock比较耗时...

performance_schema

这两个库保存了MySQL的一些性能和元数据相关的信息，其中performance_schema是MySQL5.5才新增的，上面提到的很多工具其实利用了这个库的信息。比如SHOW PROFILE ALL FOR QUERY 2:的信息还可以通过下面的查询获取：

SELECT * FROM information_schema.profiling WHERE query_id = 2 ORDER BY seq;

利用performance_schema还可以做一些更灵活的统计：

SET @query_id = 1;
SELECT STATE,SUM(DURATION) AS Total_R,
    ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING
    WHERE QUERY_ID = @query_id),2) AS Pct_R,
    COUNT(*) AS Calls,
    SUM(DURATION)/COUNT(*) AS "R/Call"
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = @query_id
GROUP BY STATE
ORDER BY Total_R DESC;

技术分享