MySQL profiling usage, mysqlprofiling

MySQL profiling usage, mysqlprofiling

The two most common bottlenecks of MySQL databases are the CPU and I/O bottlenecks. When the CPU is saturated, it usually occurs when the data is loaded into the memory or the data is read from the disk. The disk I/O bottleneck occurs when the loading data is much larger than the memory capacity. If the application is distributed on the network, when the query volume is large, the ping bottleneck will appear on the network, we can use mpstat, iostat, sar, and vmstat to view the system performance status. Today, we will not discuss the performance bottleneck of server hardware. We just talk about the MySQL system itself. We usually need to analyze the database. Common analysis methods include slow query logs and EXPLAIN analysis queries, profiling Analysis and show command query system status and system variables can better optimize the performance of database systems by locating performance bottlenecks.

This is a series of articles. Today we only talk about show profile Syntax. Through this, we can clearly know where the performance bottleneck of a Query is. Is it because it consumes too much CPU computing or does it require too many IO operations? The procedure is as follows:

1. Enable the profiling Parameter
Set profiling = 1;
2. Execute Query
Select * from;
3. Get the profile information of all queries saved in the system
Show profiles;
4. obtain detailed profile information for a single Query.
Show profile all for query 1;

Syntax:

Show profile [type [, type]...]
[For query n]
[LIMIT row_count [OFFSET offset]

Type:
ALL: displays ALL overhead information.
| Block io: displays block io overhead.
| Context switches: CONTEXT switching overhead
| CPU: displays the user's CPU time and system CPU time
| IPC: displays sending and receiving expenses.
| MEMORY: not implemented currently
| Page faults: displays overhead information related to PAGE errors.
| SOURCE: displays overhead information related to Source_function, Source_file, and Source_line.
| SWAPS: displays the overhead related to the number of exchanges.

You can also view the information in the INFORMATION_SCHEMA.PROFILING table.

References:
Https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
Https://dev.mysql.com/doc/refman/5.7/en/profiling-table.html

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.