Profiling is a very useful MySQL performance analysis tool, today to test the profiling function. Thanks for the blog with Love Rose:
The main purpose of the MySQL SQL Performance Analyzer is to show the usage of resources throughout the process of SQL execution. The parser can better demonstrate the performance problems of bad SQL.
Here are some examples of how to use MySQL SQL Profiler:
First, open MySQL SQL Profiler
Copy Code code as follows:
mysql> SELECT @ @profiling;
+-------------+
| @ @profiling |
+-------------+
| 0 |
+-------------+
1 row in Set (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows Affected (0.00 sec)
mysql> SELECT @ @profiling;
+-------------+
| @ @profiling |
+-------------+
| 1 |
+-------------+
1 row in Set (0.00 sec) The default profiling value of 0 indicates that the MySQL SQL Profiler is off, and the profiling value is 1 after the SQL Performance Analyzer is opened.
By using the SQL Performance Analyzer, we compare the differences between the 2 execution procedures of the following statements, which are very helpful for us to understand the detailed execution process of SQL.
Copy Code code as follows:
Mysql> CREATE TABLE T_engines select * from T_engines1;
Query OK, 57344 rows affected (0.10 sec)
records:57344 duplicates:0 warnings:0
Mysql> Select COUNT (*) from T_engines;
+----------+
| COUNT (*) |
+----------+
| 57344 |
+----------+
1 row in Set (0.00 sec)
Mysql> Select COUNT (*) from T_engines;
+----------+
| COUNT (*) |
+----------+
| 57344 |
+----------+
1 row in Set (0.00 sec)
Mysql> show PROFILES;
+----------+------------+-------------------------------------------------+
| query_id | Duration | Query |
+----------+------------+-------------------------------------------------+
| 26 | 0.10213775 | CREATE TABLE T_engines SELECT * FROM T_engines1 |
| 27 | 0.00032775 | Select COUNT (*) from T_engines |
| 28 | 0.00003850 | Select COUNT (*) from T_engines |
+----------+------------+-------------------------------------------------+
Rows in Set (0.01 sec)
Mysql> Show profiles for QUERY 27;
+--------------------------------+------------+
| Status | Duration |
+--------------------------------+------------+
| (initialization) | 0.00000425 |
| Checking query cache for Query | 0.00004050 |
| Checking Permissions | 0.00001050 |
| Opening Tables | 0.00018250 |
| System Lock | 0.00000450 |
| Table Lock | 0.00001775 |
| init | 0.00001075 |
| Optimizing | 0.00000550 |
| Executing | 0.00002775 |
| End | 0.00000450 |
| Query End | 0.00000325 |
| Storing result in query cache | 0.00000400 |
| Freeing items | 0.00000400 |
| Closing Tables | 0.00000500 |
| Logging Slow Query | 0.00000300 |
+--------------------------------+------------+
Rows in Set (0.00 sec)
Mysql> Show profiles for QUERY 28;
+-------------------------------------+------------+
| Status | Duration |
+-------------------------------------+------------+
| (initialization) | 0.00000350 |
| Checking query cache for Query | 0.00000750 |
| Checking privileges on cached query | 0.00000500 |
| Checking Permissions | 0.00000525 |
| Sending cached result to client | 0.00001275 |
| Logging Slow Query | 0.00000450 |
+-------------------------------------+------------+
6 rows in Set (0.00 sec) mysql> SELECT sum (FORMAT (DURATION, 6)) as DURATION from INFORMATION_SCHEMA. PROFILING WHERE query_id =27 ORDER by SEQ;
+----------+
| DURATION |
+----------+
| 0.000326 |
+----------+
1 row in Set (0.00 sec) mysql> SELECT sum (FORMAT (DURATION, 6)) as DURATION from INFORMATION_SCHEMA. PROFILING WHERE query_id =28 ORDER by SEQ;
+----------+
| DURATION |
+----------+
| 0.000039 |
+----------+
1 row in Set (0.00 sec)
From the above example, we can clearly see the difference between the 2 execution of the Count statement, show profile for QUERY 27 shows the first count of the execution process, including Opening tables, Table Lock and other operations. While show profiles for QUERY 28 shows the execution of the second count statistic, the second count returns the count statistics directly from the query cache, and by comparing the total execution time of the 2 statistics, the cache reads are nearly 10 times times the speed of physical reading. By using the SQL Performance Analyzer, you can help us diagnose the problems that are more difficult to identify, and identify the root cause of the problem.
