pt-query-digest的使用
pt-query-digest是最应该掌握的一个工具。它可以分析MySQL的各种日志，如慢查询日志、generel日志，也可以分析show processlist的输出。配合tcpdump我们还可以对线上数据库流量进行采样，实时监控数据库流量，及时发现性能问题。
基本用法如下，
pt-query-digest /path/to/slow.log > /path/to/keep/report_file
如果你有大量的数据库节点，可以考虑把pt-query-digest的分析报告写入数据库，方便检索和绘图。
输出的报表类似如下，以下截取了报告的部分内容，
# Overall: 565 total, 22 unique, 0.00 QPS, 0.00x concurrency _____________
# Time range: 2012-09-22 18:33:43 to 2012-10-16 10:45:31
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 1233s 503ms 15s 2s 7s 2s 1s
# Lock time 53ms 31us 145us 94us 119us 17us 93us
# Rows sent 1.67k 0 20 3.02 9.83 4.12 0.99
# Rows examine 616.77M 72.90k 12.03M 1.09M 6.61M 2.02M 245.21k
# Query size 139.49k 25 381 252.81 346.17 70.94 234.30
# Profile
# Rank Query ID Response time Calls R/Call Apdx V/M Item
# ==== ================== ============== ===== ======= ==== ===== ========
# 1 0xBE5D289C750F172A 308.6929 25.0% 40 7.7173 0.00 0.08 SELECT ccc tbl_eee tbl_ddd bbb
# 2 0x5C898C5E065DD204 149.4144 12.1% 105 1.4230 0.50 0.00 SELECT tbl_ddd_info tbl_eee tbl_ddd
# 3 0x6F05415421300718 136.7381 11.1% 97 1.4097 0.50 0.00 SELECT tbl_ddd_info tbl_eee tbl_ddd
# 4 0x2E9AE41A4D2149A1 123.0681 10.0% 22 5.5940 0.00 0.02 SELECT ccc tbl_eee tbl_ddd bbb
# 5 0xAFF556BC27138443 121.9603 9.9% 73 1.6707 0.50 0.00 SELECT tbl_ddd_info tbl_eee tbl_ddd
# 6 0xD07F224EF598BD9A 105.0456 8.5% 16 6.5653 0.00 0.23 SELECT ccc tbl_eee tbl_ddd bbb
# 7 0xC22F9709F846BB4E 99.1936 8.0% 73 1.3588 0.50 0.00 SELECT tbl_ddd_info tbl_eee tbl_ddd
# 8 0x4CAD792BF4A54CE9 53.7477 4.4% 4 13.4369 0.00 0.17 SELECT tbl_fff tbl_eee tbl_ddd
# 9 0x347319A37AC29893 39.1390 3.2% 69 0.5672 1.00 0.00 SELECT tbl_fff pt_game_base_score
# 10 0x7EF77B274F1C37D3 27.2826 2.2% 4 6.8207 0.00 0.00 SELECT ccc tbl_eee tbl_ddd bbb
# 11 0x8383B2CB219358F3 16.7553 1.4% 18 0.9308 0.97 0.00 SELECT tbl_iii tbl_hhh tbl_eee
# MISC 0xMISC 51.5793 4.2% 44 1.1723 NS 0.0 <11 ITEMS>
# Query 1: 0.00 QPS, 0.00x concurrency, ID 0xBE5D289C750F172A at byte 120071
# This item is included in the report because it matches –limit.
# Scores: Apdex = 0.00 [1.0]*, V/M = 0.08
# Query_time sparkline: | ^_|
# Time range: 2012-09-25 11:01:09 to 2012-10-16 10:14:31
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 40
# Exec time 25 309s 7s 10s 8s 9s 802ms 7s
# Lock time 6 4ms 59us 110us 89us 103us 12us 91us
# Rows sent 2 40 1 1 1 1 0 1
# Rows examine 45 281.88M 6.73M 7.29M 7.05M 6.94M 183.33k 6.94M
# Query size 5 7.19k 184 184 184 184 0 184
# String:
# Hosts
# Users db_user
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ #
# Tables
# SHOW TABLE STATUS LIKE ‘ccc’\G
# SHOW CREATE TABLE `tbl_eee`\G
# SHOW TABLE STATUS LIKE ‘tbl_ddd’\G
# SHOW CREATE TABLE `bbb`\G
# SHOW TABLE STATUS LIKE ‘ggg’\G
# EXPLAIN /*!50100 PARTITIONS*/
select … from ….
具体输出格式的解释：
Rank 所有查询日志分析后，此查询的排序,
Query ID 查询的标识字符串。可以搜索这个字符串快速定位到慢查询语句。
Response time 总的响应时间,以及总占比，应优化占比高的查询，比例比较小的查询一般可以忽略，不进行优化。
Calls 查询被调用执行的次数.
R/Call 每次执行的平均响应时间.
Apdx 应用程序的性能指数得分.（Apdex）.响应时间越长,得分越低.
V/M 响应时间的方差均值比 (变异数对平均数比,变异系数).可说明样本的分散程度. 这个值大,往往是值得考虑优化的对象.
Item 查询的简单显示，包含了查询涉及的表。
对于报告中的如下输出，
# Query 1: 0.00 QPS, 0.00x concurrency, ID 0xBE5D289C750F172A at byte 120071
我们可以利用偏移量到慢查询日志里定位具体的sql语句，定位方法如下，
tail -c +120071 /path/to/slow.log. | head
查询响应时间的分布，使用了很形象的表示方式，如下，
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ #
可以看到，有许多查询响应在1秒到10秒之间。
对于一些TOP SQL，我们可以使用explain工具分析执行计划，进行调优。
对于更新语句，此工具会帮你改写成可以使用explain工具的select语句。
.
一些使用的例子如下。
1、分析show processlist的输出。
./pt-query-digest –processlist S=/path/3307/mysql.sock,u=root,p=dxwd\* –interval 5 –run-time 5m
pt-query-digest –processlist h=host1 –print –no-report
- 分析tcpdump的输出。
分析执行SQL的频率，一般在高峰期取样，一定要记得关闭tcpdum，因为生成的文件可能很大。
首先运行命令。
nohup tcpdump -i eth1 port 3306 -s 65535 -x -nn -q -tttt > dbxx_sql_new.log &
过一段时间后，如1分钟，终止tcpdump任务
然后使用pt-query-digest进行分析
./pt-query-digest –type=tcpdump –watch-server 12.12.12.12:3306 dbxx_sql_new.log > report_to_develper.rtf
对生产环境的采样可以采取如上的方法，比如每分钟抓取5秒的网络包，然后把分析结果入库。利用监控系统及时发现问题，通知DBA或者研发人员线上的性能问题。
3. 把分析过的Sql记录到历史信息表中。可以配置一个数据库专门存放,可以了解分析的sql最后出现的时间,如果已经解决掉了,就不用再优化了.
如果我用工具生成了新的报表里面的bad sql ,我可以查下原来保存在数据库表中的sql,如果以前就有(已经分析了),那么就不用再做重复性的工作了.
步骤如下
1) 在存放优化信息的数据库中创建一个用户用来存放信息: grant create,select,insert,update,delete on ptool.* to ptool@’%’ identified by ‘ptool’;
2) /home/mysql/scripts/pt-query-digest –create-review-table –reviewh=13.13.13.13,P=3305,u=ptool,p=ptool,D=ptool,t=query_review \
–create-review-history-table –review-history h=13.13.13.13,P=3305,u=ptool,p=ptool,D=ptool,t=query_review_history –report-all\
/usr/local/mysql/log/3307/slowquery.log
以上第一次运行,会存储信息到指定的表中.
以后再次运行同样选项的sql, 如果表的reviewed_by列有设定值,那么此工具不会显示你已经review过了的sql的. 如果要显示所有sql,那么需要使用选项–report-all
如果有–report-all和review表(表里有sql以及你添加的意见等信息) ,那么生成的报告里带有你review过的sql的意见 ,非常有用。
3) 可以用sql查询top sql
SELECT * FROM `query_review_history` WHERE ts_max > ‘2012-09-20 00:00:00’
ORDER BY ts_cnt DESC , query_time_sum DESC LIMIT 3;
可以按照checksum（数据表里的是10进制的显示,report里是16进制的显示方式）去数据表查询对应sql,记录自己的优化意见 ,如
SELECT * FROM `query_review` WHERE CHECKSUM=0xB76366269B6B4973;
- 报告不记录到历史信息表,只记录简单的信息.
/home/mysql/scripts/pt-query-digest –create-review-table –review h=13.13.13.13,P=3305,u=ptool,p=ptool,D=test,t=query_review /path/to/log3307/slowquery.log
存放信息的表需要我们手动建立,或者添加选项 –create-review-table ,–create-review-table 会自动创建如下的表
Create the “–review” table if it does not exist.
CREATE TABLE query_review (
checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,
fingerprint TEXT NOT NULL,
sample TEXT NOT NULL,
first_seen DATETIME,
last_seen DATETIME,
reviewed_by VARCHAR(20),
reviewed_on DATETIME,
comments TEXT
)
COLUMN MEANING
=========== ===============
checksum A 64-bit checksum of the query fingerprint
fingerprint The abstracted version of the query; its primary key
sample The query text of a sample of the class of queries
first_seen The smallest timestamp of this class of queries
last_seen The largest timestamp of this class of queries
reviewed_by Initially NULL; if set, query is skipped thereafter
reviewed_on Initially NULL; not assigned any special meaning
comments Initially NULL; not assigned any special meaning
每次重新运行以上命令,就会重新更新表内的值.比如最早,最近出现的时间。
— INSERT INTO `test`.`query_review` …. ON DUPLICATE KEY UPDATE …
- 使用pt-query-digest分析通用日志general日志和二进制日志
分析通用日志
pt-query-digest –type genlog general.log > /tmp/xxx.log 即可.
分析二进制日志
pt-query-digest –type binlog \
–group-by fingerprint \
–limit “100%” \
–order-by “Query_time:cnt” \
–output report \
–report-format profile \
/tmp/xxx.log
注意/tmp/xxx.log日志是文本形式的二进制日志
可以分析的日志类型
binlog
genlog
slowlog
tcpdump
6. 按不同的指标排序
目前默认的是报告哪些Sql是最慢的，最值得去优化的。也可以按照其他的指标进行排序，要使用到选项 –group-by, –filter, and –embedded-attributes
–group-by 选项
加上了group-by选项，默认也选择了相应的order-by选项了，很方便。
由于general日志里没有执行时间，那么默认的order by就是 Query_time:cnt 了。
如果想按照新的属性进行排序，那么就需要创建新的属性，比如
–filter ‘($event->{Row_ratio} = $event->{Rows_sent} / ($event->{Rows_examined})) && 1’
pt-query-digest \
–group-by fingerprint \
–order-by Query_time:sum \
–limit 10 \
slow.log
#以examined rows进行排序
./pt-query-digest –since=’2013-12-06 00:00:00′ –until=’2013-12-16 00:00:00′ /path/to/log3306/slowquery.log –order-by=’Rows_examined:max’ > /tmp/aa.log
#按执行过程遍历的行记录数和返回的结果集行记录数的比例排序， ratio of Rows_sent to Rows_examined,
Rows_sent / Rows_examined
./pt-query-digest –since=’2013-12-14 00:00:00′ –until=’2013-12-15 00:00:00′ /path/to/log3306/slowquery.log –filter ‘($event->{Row_ratio} = $event->{Rows_sent} / ($event->{Rows_examined})) && 1′ –order-by=’Row_ratio:min’ > /tmp/aa.log
#仅显示row_ration。不排序
./pt-query-digest –since ‘2013-12-14 00:00:00’ –until ‘2013-12-14 10:00:00’ –filter ‘($event->{Row_ratio} = $event->{Rows_sent} / ($event->{Rows_examined})) && 1’/path/to/log3306/slowquery.log > /tmp/aa.log
#只report select查询
An example filter that discards everything but SELECT statements:
./pt-query-digest –since ‘2013-12-13 00:00:00’ –until ‘2013-12-14 10:00:00’ –filter ‘$event->{arg} =~ m/^select/i’ /path/to/log3306/slowquery.log> /tmp/bb.log
其他参数
–limit 默认是95%:20 ,输出95%的bad sql.
If the argument is an integer, report only the top N worst queries.
If the argument is an integer followed by the “%” sign, report that per-centage of the worst queries.
If the percentage is followed by a colon and another integer, report the top percentage or the number specified by that integer, whichever comes first.
–limit 100%:10000 。显示所有的查询。
» 转载保留版权：老陈 » 《pt-query-digest的使用》» 如果喜欢可以： 点此订阅本站