Use of MySQL profiling

In the first section of this chapter, we also mentioned using query profiler to locate the performance bottleneck of a query. Here we will detail the usage and usage of profiling.

To optimize a query, we need to know exactly where the performance bottleneck of this query is. Is there too much CPU computing consumption or too many Io operations required? To get a clear understanding of this information, it can be easily done in the MySQL 5.0 and MySQL 5.1 official versions through the query profiler function.

MySQL query profiler is a very convenient query diagnostic and analysis tool that can be used to obtain the consumption of multiple resources in a query throughout the execution process, such as CPU, Io, IPC, swap, page faults, context switche, and so on. The location of the functions called by MySQL in the source file during query execution can also be obtained.

Let's take a look at the specific usage of query profiler.

1. Enable the profiling Parameter

 
Root @ localhost: (none)10:53:11> Set profiling =1; Query OK,0Rows affected (0.00Sec)
 

You can enable or disable the query profiler function by running the "set profiling" command.

2. Execute Query

 
...... Root @ localhost: Test07:43:18>SelectStatus, count (*)->From test_profiling group by status;+ ---------------- + ---------- + | Status | count (*) | + ---------------- + ---------- + | st_xxx1 |27| St_xxx2 |6666| St_xxx3 |292887| St_xxx4 |15| + ---------------- + ---------- +5RowsInSet (1.11Sec )......
 

After the query profiler function is enabled, MySQL automatically records the profile information of all executed queries.

3. Obtain the profile information of all the queries saved in the system.

Root @ localhost: Test 07 : 47 : 35 > 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 | Select Status, count (*) from test_profiling group by status | + ---------- + ------------ + ------------------------------------------------------------ + 3 Rows In Set ( 0.00 Sec)

Run the "show profile" command to obtain the profile information of multiple queries saved in the current system.

4. obtain detailed profile information for a single query.

After obtaining the summary information, we can obtain a query according to the query_id in the summary information.

The detailed profile information is as follows:

In the above example, the CPU and block Io consumption are obtained, which is very clear and suitable for locating performance bottlenecks. If you want to obtain other information, you can obtain it by executing "show profile *** for query N". You can test it yourself.

Transferred from MySQL performance optimization and Architecture Design