[MySQL Optimizer]--How to use the SQL Profiler Performance Analyzer

Source: Internet
Author: User

The main purpose of MySQL's SQL Performance Analyzer is to show how resources are used throughout the process of SQL execution. The parser can better demonstrate the performance problems of poor SQL.
Here are some examples of how MySQL SQL Profiler is used:

    • First, turn on MySQL SQL Profiler

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)
By default, the value of profiling is 0 to indicate that MySQL SQL Profiler is OFF, and the profiling value is 1 after you turn on SQL Performance Analyzer.

    • With the SQL Performance Analyzer, we can compare the differences between the 2 executions of the following statements, which is very helpful for us to understand the detailed execution of SQL.

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 profile 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 profile 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)
Mysql>
From the above example we can clearly see the difference between the 2 executions 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 profile for QUERY 28 shows the execution of the second count statistic, the second count returns the count results directly from the query cache, and by comparing the total execution time of 2 statistics, the cache reads nearly 10 times times as fast as the physical reads. By using SQL Performance Analyzer, you can help us diagnose some SQL that is more difficult to determine performance problems and find the source of the problem.


This article comes from Chinaunix blog, if you look at the original point:Http://blog.chinaunix.net/u3/93470/showart_2002151.html
Published

[MySQL Optimizer]--How to use the SQL Profiler Performance Analyzer

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.