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

This article will explain in detail how to use systemtap debugging tools to analyze the performance of MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

First, analyze the consumption of SQL statements in each stage of execution.

For example, if we want to count the execution time of a SQL in the parsing phase, we can add a probe point to the corresponding parse_sql function of the mysqld process to calculate the execution time of the function.

The script is as follows:

For example, the current QPS is about 1.6W, which includes the consumption of parsing, redo, binlog, undo, network, IO reading and IO writing during SQL execution. Due to the large number of functions involved, this part needs to be sorted out and improved later.

It can also be used to analyze the execution performance of a SQL:

2. Statistical distribution of SQL execution time

For applications, it is mainly concerned with database throughput and response time. Let's measure the performance distribution of the current execution of SQL statements on the server from the perspective of application load.

The following figure shows the execution time distribution of SQL statements aggregated in 1 second, indicating that the execution time of most SQL is below 128us:

[performance overhead]

A simple stress test is done with sysbench. When running 8 threads, the stap script QPS is reduced from 3.5W to 3.3W, and the performance loss is about 6%. When running 32 threads, the stap script QPS is reduced from 9W to 7W, and the performance loss is about 22%.

This is the end of this article on "how to use systemtap debugging tools to analyze the performance of MySQL". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.