首页 > 数据库 > pt-query-digest的使用

pt-query-digest的使用

2016年5月7日 862 views 发表评论 阅读评论

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

 

  1. 分析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;

  1. 报告不记录到历史信息表,只记录简单的信息.

/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 …

 

  1. 使用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的使用》
 » 如果喜欢可以: 点此订阅本站
分类: 数据库 标签: ,
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.