"MySQL optimized" MySQL Profiling use

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.

0. You can clear the query cache before executing

Reset query Cache;

1. Open Profiling parameter

Root@localhost::>set profiling=1 0 rows affected (0.00 sec)  

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

2. Execute Query

Root@localhost: Test -: +: -> SelectStatusCount(*) -  fromTest_profilingGroup  bystatus;+----------------+----------+|Status| Count(*)|+----------------+----------+|St_xxx1|  - ||St_xxx2| 6666 ||St_xxx3| 292887 ||St_xxx4|  the |+----------------+----------+5Rowsinch Set(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

Root@localhost: Test -: -: *>show Profiles;+----------+------------+------------------------------------------------------------+|query_id|Duration|Query|+----------+------------+------------------------------------------------------------+| 1 | 0.00183100 |Show Databases|| 2 | 0.00007000 | SELECT DATABASE()|| 3 | 0.00099300 | descTest|| 4 | 0.00048800 |Show Tables|| 5 | 0.00430400 | desctest_profiling|| 6 | 1.90115800 | SelectStatusCount(*) fromTest_profilingGroup  byStatus|+----------+------------+------------------------------------------------------------+3Rowsinch Set(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 optimized" MySQL Profiling use