A colleague at CleverCode recently recommended a tool for analyzing sql statements in MySQL, profiling, and found that this tool is very good, which can accurately analyze where the specific time spent on sql statements during the query process. CleverCode summarizes it here and shares it with you.
1 Introduction
MySQL's Query Profiler is a very convenient Query diagnostic analysis tool, through which you can obtain the consumption of multiple resources in the entire execution process of a query, such as CPU, IO, IPC, SWAP, etc., as well as the occurrence of PAGE FAULTS, CONTEXT SWITCHE, etc., and also get the location of each function called by MySQL in the source file during the execution of the query.
MySQL version 5.0.37 or later supports the PROFILING debugging function, which allows you to understand the details of the resources consumed by SQL statements. Because it requires calling the system's getrusage() function, it can only be used on Linux/Unix class platforms, not Windows. Moreover, PROFILING is for processing processes rather than threads, and other applications on the server may affect your debugging results, so this tool is suitable for debugging during development, and if you want to debug in a production environment, you should pay attention to its limitations.
2 Operation
2.1 Check if profile is enabled, it is turned off by default.
2.2 Enable profiling. Variable profiling is a user variable that needs to be re-enabled each time.
2.3 Execute the following statements: To avoid having previously stored SQL in QCACHE, it is recommended to force the SELECT statement not to perform QCACHE detection when executing SQL. This allows you to submit the accuracy of the analysis.
2.4 Useshow profile queryThe most recent oneExecution information of the statement。 (Analysis: select sql_no_cache count(*) from system_user where age > 20)
2.5 Using show profiles. View a list of statements that are executed on the server. (query id, time spent, statement).
2.6 Use show profile to query the execution information of the formulation ID. Here we analyze the statement with ID 6. (Analysis: select sql_no_cache count(*) from system_user where age > 20).
2.7 Get CPU and Block IO consumption.
2.8 Obtaining Additional Information. can be obtained by performing "SHOW PROFILE *** FOR QUERY n". Reference address: http://dev.mysql.com/doc/refman/5.6/en/show-profile.html.
|
Posted on 7/6/2017 11:12:17 AM
|
|
|
|
