The use of MySQL profiling

In the first section of this chapter we also mentioned the performance bottleneck of using the query Profiler to locate a query, and here we describe in detail the purpose of Profiling and how to use it.

To optimize a query, we need to know exactly where this query's performance bottleneck is, is it consuming too much CPU, or does it require too many IO operations? To be able to understand this information clearly, in MySQL 5.0 and the MySQL 5.1 official version can be very easy to do, that is, through the Query Profiler function.

MySQL's query Profiler is an easy-to-use Query diagnostic analysis tool that allows you to get a query that consumes a variety of resources throughout the execution, such as Cpu,io,ipc,swap, and the PAGE faults,c that occurs. Ontext Switche and so on, you can also get the position of each function in the source file called by MySQL during the Query execution.

Let's look at the specific usage of the Query Profiler.

1. Open Profiling parameter

Ten:: >set profiling=10 rows affected (0.00 sec )

You can turn off the Query Profiler function by executing the "set profiling" command.

2. Execute Query

... ... [Email protected]: Test -: +: ->SelectStatus,count (*) -From test_profiling Group by status;+----------------+----------+| Status | COUNT (*) |+----------------+----------+| st_xxx1 | -|| st_xxx2 |6666|| st_xxx3 |292887|| st_xxx4 | the|+----------------+----------+5RowsinchSet (1.11sec) ...

After you turn on the query Profiler feature, MySQL automatically logs all the profile information for the query that was executed.

3. Get profile profiles of all Query saved in the system

[Email protected]: test -: -: *>show Profiles;+----------+------------+------------------------------------------------------------+| query_id | Duration |Query|+----------+------------+------------------------------------------------------------+|1|0.00183100|Show Databases||2|0.00007000|SELECT DATABASE ()||3|0.00099300|desc Test||4|0.00048800|Show Tables||5|0.00430400|desc test_profiling||6|1.90115800|SelectStatus,count (*) from test_profiling GROUP by status |+----------+------------+-------------------------------------- ----------------------+3RowsinchSet (0.00Sec

Obtain profile information for multiple Query profiles that are saved in the current system by executing a "SHOW profile" command.

4. Get detailed profile information for a single Query.

After obtaining the summary information, we can get a Query based on the query_id in the profile

Detailed profile information, the specific operation is as follows:

In the example above, the consumption of CPU and Block IO is very clear and very useful for locating performance bottlenecks. If you want to get other information, you can get it by executing "SHOW profile * * * * for QUERY n", and you will be able to test and familiarize yourself with your readers.

MySQL performance optimization and architecture design

The use of MySQL profiling