MySQL Performance Profiling Tool (pt-query-digest) _ MySQL

MySQL Performance Profiling Tool (pt-query-digest) is also from percona-toolkit

Other tools in this tool set

Troubleshooting of abnormal shutdown of MySQL Slave (pt-slave-restart)

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.

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.