Mysql uses profiling to analyze the causes of slow SQL statements.

Mysql uses profiling to analyze the causes of slow SQL statements.

CleverCode colleagues recently recommended a mysql SQL statement analysis tool profiling, and found that this tool is very good, it can accurately analyze the specific time spent on SQL statements during the Query Process. CleverCode will be summarized here and shared with you.

[Do not repost the original works of CleverCode in the csdn blog. Original address: http://blog.csdn.net/clevercode/article/details/46336635]

1 Introduction MySQL's Query Profiler is a very convenient Query diagnostic and analysis tool that can be used to obtain the consumption of various resources in a Query throughout the execution process, such as CPU, IO, IPC, SWAP, page faults, context switche, and so on can also obtain the position of each function called by MySQL in the source file during the Query execution.

MySQL 5.0.37 and later versions support PROFILING debugging, allowing you to learn more about resource consumption of SQL statements. Because it needs to call the system's getrusage () function, it can only be used on Linux/Unix platforms, rather than Windows platforms. In addition, PROFILING is for processing processes rather than threads. Other applications on the server may affect your debugging results. Therefore, this tool is suitable for debugging during development. If you want to debug and use it in the production environment, pay attention to its limitations.


2. In operation 2.1, check whether the profile is enabled. By default, it is disabled.

mysql> select @@profiling;+-------------+| @@profiling |+-------------+|           0 | +-------------+1 row in set (0.00 sec)

2.2 enable profiling. The variable profiling is a user variable and must be re-enabled each time.

mysql> set profiling = 1;  Query OK, 0 rows affected (0.00 sec)mysql> select @@profiling;  +-------------+| @@profiling |+-------------+|           1 | +-------------+1 row in set (0.00 sec)

2.3 run the following statements. To avoid having to store SQL statements in QCACHE, we recommend that you do not perform QCACHE check when executing SQL statements. In this way, the analysis accuracy can be submitted.

mysql> use db_user;mysql> select sql_no_cache count(*) from system_user;mysql> update system_user set username = 'CleverCode' where id = 10;mysql> select sql_no_cache count(*) from system_user where age > 20;...... 

2.4 use show profile to query the execution information of the last statement. (Analysis: select SQL _no_cache count (*) from system_user where age> 20)

mysql> show profile;

2.5 use show profiles. View the list of statements executed on the server. (Query id, time spent, Statement ).

mysql> show profiles; 

2.6 Use show profile to query the execution information of the specified ID. The statement with ID 6 is analyzed here. (Analysis: select SQL _no_cache count (*) from system_user where age> 20 ).

mysql> show profile for query 6;

2.7 obtain the CPU and Block IO consumption.

mysql>  show profile block io,cpu for query 6;

2.8 obtain other information. You can obtain them by executing "show profile *** for query n. For more information, see http://dev.mysql.com/doc/refman/5.6/en/show-profile.html.

mysql> show profile all for query 6;mysql> show profile cpu,block io,memory,swaps,context switches,source for query 6;

ALL displays all information

Block io displays counts for block input and output operations

Context switches displays counts for voluntary and involuntary context switches

CPU displays user and system CPU usage times

IPC displays counts for messages sent and received ed

MEMORY is not currently implemented

Page faults displays counts for major and minor page faults

SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs


SWAPS displays swap counts

Copyright:

1) original works are from the "CleverCode blog". Do not reprint them. Otherwise, the copyright is held legally liable.

2) original address: http://blog.csdn.net/clevercode/article/details/46310435.

3) classification address (Mysql database summary): http://blog.csdn.net/clevercode/article/category/3262205 (blog continues to increase, follow the favorite)

4) Welcome to my blog for more highlights: http://blog.csdn.net/clevercode.