Shulou(Shulou.com)05/31 Report--

Mysql operation and maintenance tool percona-toolkit tool pt-query-digest syntax and usage, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

prerequisite

Mysql version 5.7.21

Redhat 6.8

Open slow query

Enable slow query

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

Enable slow query with unused index

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

Create a test table

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 tweezers * * query OK, 8388608 rows affected (35.04sec) Records: 8388608 Duplicates: 0 Warnings: 0

Execute sql query

Mysql > select count (*) from zxydb.t_slow;+-+ | count (*) | +-+ | 16777216 | +-+ 1 row in set (sec) pt-query-digest syntax

Query pt-query-digest options

[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 and L. 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

Get all the semantic information of the complete pt-query-digest

[root@three57 percona-toolkit-3.1.0] # man pt-query-digest > / pt-query.log uses pt-query-digest to analyze slow log [root@three57 percona-toolkit-3.1.0] # pt-query-digest / var/lib/mysql/three57-slow.log user time distribution and in-process memory # 100ms user time, 10ms system time, 21.88m rss 173.04M vsz current system time # Current date: Wed Nov 13 15:29:44 2019 hostname # Hostname: three57 slow query log # Files: / var/lib/mysql/three57-slow.logQPS and concurrent # Overall: 1 total, 1 unique, 0 QPS, 0x concurrency _ # Time range: all events occurred at 2019-11-13T07:28:59 attribute: all Minimum, maximum, average, 95%, average spread Difference # Attribute total min max avg 95% stddev median# = = SQL execution time # Exec time 7s 7s 7s 07s lock time # Lock time 127us 127us 127us 127us 127us 0 127usSQL results # Rows sent 1 1 1 1 1 0 1SQL scan table record # Rows examine 16.00M 16.00M 16.00M 0 16.00M query size # Query size 33 33 33 0 33 Overview of slow query SQL Extremely important, typesetting one word, ProfileRANK for ranking number query id for specific SQL response time for sql response time calls for number of SQL execution r/call for response time for each SQL execution Specific SQL statement # Rank Query ID Response time Calls R/Call Vamp M # = # 1 0xAC104A376C0A55B1F56FDA6E706E555F 6.7433 100.0% 1 6.7433 0.00 SELECT zxydb.t_slow specific slow query SQL statement # Query 1: 0 QPS, 0x concurrency ID 0xAC104A376C0A55B1F56FDA6E706E555F at byte ID 0xAC104A376C0A55B1F56FDA6E706E555F at byte This item is included in the report because it matches-- limit.# Scores: VUnip M = 0.01Time range: all events occurred at 2019-11-13T07:28:59# Attribute pct total min max avg 95% stddev median# = # Count 100 1SQL execution time is 7s# Exec time 100 7s 7s 7s 0 7sSQL lock time is 127us This value is extremely high. Need targeted analysis # Lock time 100 127us 127us 127us 127us 127us 0 127us# Rows sent 100 1 1 1 10 1 full table scan correlation # Rows examine 100 16.00M 16.00M 16.00M 0 16.00M# Query size 100 33 33 33 0 33 # String: distribution of hosts and users producing SQL in different time ranges of # Hosts localhost# Users rootSQL Excellent. As shown above, the SQL execution time is about 1s # Query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s # 10s+# Tables# SHOW TABLE STATUS FROM `zxydb` Is it helpful for you to read the above content after reading the above content LIKE 'tweets'\ G# SHOW CREATE TABLE `zxydb`.`t _ room`\ G# EXPLAIN / *! 50100 PARTITIONS*/select count (*) from zxydb.t_slow\ G [root @ three57 percona-toolkit-3.1.0] #? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.