mysql运维利器percona-toolkit工具的pt-query-digest语法以及用法

mysql运维利器percona-toolkit工具的pt-query-digest语法以及用法，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

前提条件

• mysql版本5.7.21

• redhat 6.8

开启慢查询

启用慢查询

mysql> set global slow_query_log=on;Query OK, 0 rows affected (0.01 sec)

启用未使用索引慢查询

mysql> set global log_queries_not_using_indexes=on;Query OK, 0 rows affected (0.00 sec)

运行sql

创建测试表

mysql> use zxydb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql>  insert into t_slow select * from t_slow;Query OK, 8388608 rows affected (35.04 sec)Records: 8388608  Duplicates: 0  Warnings: 0

执行sql查询

mysql> select count(*) from zxydb.t_slow;+----------+| count(*) |+----------+| 16777216 |+----------+1 row in set (6.48 sec)

pt-query-digest语法

查询pt-query-digest选项

[root@three57 percona-toolkit-3.1.0]# pt-query-digest --helppt-query-digest analyzes MySQL queries from slow, general, and binary log files.It can also analyze queries from C and MySQL protocol datafrom tcpdump.  By default, queries are grouped by fingerprint and reported indescending order of query time (i.e. the slowest queries first).  If no Care given, the tool reads C.  The optional C is used for certainoptions like L<"--since"> and L<"--until">.  For more details, please use the--help option, or try 'perldoc /usr/local/bin/pt-query-digest' for completedocumentation.Usage: pt-query-digest [OPTIONS] [FILES] [DSN]Options:  --ask-pass                   Prompt for a password when connecting to MySQL  --attribute-aliases=a        List of attribute|alias,etc (default db|Schema)  --attribute-value-limit=i    A sanity limit for attribute values (default 0)  --charset=s              -A  Default character set  --config=A                   Read this comma-separated list of config files;                               if specified, this must be the first option on                               the command line

获取完整pt-query-digest全部语义信息

[root@three57 percona-toolkit-3.1.0]# man pt-query-digest>/pt-query.log

使用pt-query-digest分析慢查询日志

[root@three57 percona-toolkit-3.1.0]# pt-query-digest  /var/lib/mysql/three57-slow.log 用户时间分布及进程内程内存# 100ms user time, 10ms system time, 21.88M rss, 173.04M vsz当前系统时间# Current date: Wed Nov 13 15:29:44 2019主机名称# Hostname: three57慢查询日志# Files: /var/lib/mysql/three57-slow.logQPS及并发# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________# Time range: all events occurred at 2019-11-13T07:28:59属性：全部，最小，最大，平均，95%，平均利差，中差# Attribute          total     min     max     avg     95%  stddev  median# ============     ======= ======= ======= ======= ======= ======= =======SQL执行时间# Exec time             7s      7s      7s      7s      7s       0      7s锁定时间# Lock time          127us   127us   127us   127us   127us       0   127usSQL产生结果个数# Rows sent              1       1       1       1       1       0       1SQL扫描表记录# Rows examine      16.00M  16.00M  16.00M  16.00M  16.00M       0  16.00M查询大小# Query size            33      33      33      33      33       0      33符合慢查询SQL的概述，极重要，排版一个字，棒# ProfileRANK为排名编号 query id为具体SQL response time为sql响应时间 calls为SQL执行次数r/call为每次SQL执行的响应时间，具体的SQL语句# Rank Query ID                           Response time Calls R/Call V/M  # ==== ================================== ============= ===== ====== =====#    1 0xAC104A376C0A55B1F56FDA6E706E555F 6.7433 100.0%     1 6.7433  0.00 SELECT zxydb.t_slow具体慢查询SQL语句# Query 1: 0 QPS, 0x concurrency, ID 0xAC104A376C0A55B1F56FDA6E706E555F at byte 0# This item is included in the report because it matches --limit.# Scores: V/M = 0.00# Time range: all events occurred at 2019-11-13T07:28:59# Attribute    pct   total     min     max     avg     95%  stddev  median# ============ === ======= ======= ======= ======= ======= ======= =======# Count        100       1SQL执行时间为7s# Exec time    100      7s      7s      7s      7s      7s       0      7sSQL锁定时间为127us,此值如极高，需要针对性分析# Lock time    100   127us   127us   127us   127us   127us       0   127us# Rows sent    100       1       1       1       1       1       0       1全表扫描相关# Rows examine 100  16.00M  16.00M  16.00M  16.00M  16.00M       0  16.00M# Query size   100      33      33      33      33      33       0      33# String:产生SQL的主机及用户# Hosts        localhost# Users        rootSQL不同时间范围分布，极好极好可见如上SQL执行时间在1S左右# Query_time distribution#   1us#  10us# 100us#   1ms#  10ms# 100ms#    1s  #################################################################  10s+# Tables#    SHOW TABLE STATUS FROM `zxydb` LIKE 't_slow'\G#    SHOW CREATE TABLE `zxydb`.`t_slow`\G# EXPLAIN /*!50100 PARTITIONS*/select count(*) from zxydb.t_slow\G[root@three57 percona-toolkit-3.1.0]#

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。