MySQL performance profiling tool (pt-query-digest)

MySQL performance profiling tool (pt-query-digest)

This tool is also from percona-toolkit
Other tools in this tool set
Troubleshooting of abnormal shutdown of MySQL Slave (pt-slave-restart)
 
Verify MySQL master-slave consistency (pt-table-checksum & pt-table-sync)
 
It can analyze various logs to analyze performance issues
Including
1. Slow log (default)
2. tcpdump
3. general log
4. binlog
5. show processlist
 
The experiment simulates a certain amount of pressure through tpcc, and enables binlog, general log, slow log, and tcpdump to collect logs.
Tpcc reference:
 
1. slow log method
Set slow_query_log to on
Long_query_time is set to 0.
Reconnect to the server (this generally means that the middleware server is restarted)
In this way, the slow log records all operations.
 
Analyze slow log files using pt-query-digest to generate analysis reports
The first is the summary information, including a total of 54.17k operations, 46 Statements, an average of 1.55k queries per second, and the time covered by the analysis report.

 
It also includes analysis similar to Top SQL.
You can view the SQL statements that have the greatest impact on server performance from the response time. R/Call indicates the average response time of each execution.

 
Then there is a detailed analysis report for each SQL statement,
Pct indicates the percentage of this item in the summary report.

 
2. tcpdump Mode
Because the slow LOG method requires reconnection, It is very expensive to restart the middleware in the production environment.
Therefore, packet capture analysis on the database server is also a good choice.
Pt-query-digest has a certain format for packet capture. (-X-nn-q-tttt)
-S: Source Port
-C: Number of captured packets
Tcpdump-s 65535-x-nn-q-tttt-I any-c 100000 port 3306> test. dmp
Analysis after packet capture
Pt-query-digest -- limit 10 -- type tcpdump test. dmp
Limit 10 limit to the top 10 operations
 
Information generated by other methods may be missing, which may fail to achieve the expected results.
However, if the processlist experiment fails, it is unclear how to use this method to analyze the performance.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: