重庆分公司,新征程启航
为企业提供网站建设、域名注册、服务器等服务
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)
创建测试表
mysql> use zxydb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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选项
[root@three57 percona-toolkit-3.1.0]# pt-query-digest --help pt-query-digest analyzes MySQL queries from slow, general, and binary log files. It can also analyze queries from Cand MySQL protocol data from tcpdump. By default, queries are grouped by fingerprint and reported in descending order of query time (i.e. the slowest queries first). If no C are given, the tool reads C . The optional C is used for certain options like L<"--since"> and L<"--until">. For more details, please use the --help option, or try 'perldoc /usr/local/bin/pt-query-digest' for complete documentation. 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
[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.log QPS及并发 # 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 127us SQL产生结果个数 # Rows sent 1 1 1 1 1 0 1 SQL扫描表记录 # 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的概述,极重要,排版一个字,棒 # Profile RANK为排名编号 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 1 SQL执行时间为7s # Exec time 100 7s 7s 7s 7s 7s 0 7s SQL锁定时间为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 root SQL不同时间范围分布,极好极好 可见如上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]#
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注创新互联行业资讯频道,感谢您对创新互联的支持。