MySQL SQL Performance Analyzer

Author: skate
Time: 2012/02/17

MySQL SQL Performance Analyzer

The SQL Performance Analyzer of MySQL is mainly used to display the usage of various resources during SQL Execution. Analyzer can better display
Poor SQL Performance problems.

How to use MySQL SQL Profile

1. Enable MySQL SQL Profile

Check whether MySQL SQL profile is enabled
Mysql> select @ profiling;
+ ------------- +
| @ Profiling |
+ ------------- +
| 0 |
+ ------------- +
1 row in SET (0.01 Sec)

By default, the value of profiling is 0, indicating that MySQL SQL profiler is off. If SQL Performance Analyzer is enabled, the value of profiling is 1.

Mysql> set profiling = 1;
Query OK, 0 rows affected (0.03 Sec)

Mysql> select @ profiling;
+ ------------- +
| @ Profiling |
+ ------------- +
| 1 |
+ ------------- +
1 row in SET (0.01 Sec)

As shown above, profiling has changed to 1, but this is session-level and is not supported by the system. Test

Exit MySQL
Mysql> quit
Bye
[Root @ localhost ~] # Mysql-uroot-P
Enter password:
Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 3
Server version: 5.0.45-log Source Distribution

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

View profiling Value
Mysql> select @ profiling;
+ ------------- +
| @ Profiling |
+ ------------- +
| 0 |
+ ------------- +
1 row in SET (0.01 Sec)

If the default value is 0, what if the system level is set?

Mysql> set global profiling = 1;
Error 1228 (hy000): Variable 'profiling' is a session variable and can't be used with set global
Mysql>

An error is reported here. Therefore, MySQL SQL profile is session-level.

2. For example, how to use

Mysql> Create Table T5 as select * from T1;
Error 1046 (3d000): No Database Selected
Mysql> Use Backup;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> Create Table T5 as select * from T1;
Query OK, 2 rows affected (0.06 Sec)
Records: 2 duplicates: 0 Warnings: 0

Mysql> select count (*) from T5;
+ ---------- +
| Count (*) |
+ ---------- +
| 2 |
+ ---------- +
1 row in SET (0.00 Sec)

Mysql> select count (*) from T5;
+ ---------- +
| Count (*) |
+ ---------- +
| 2 |
+ ---------- +
1 row in SET (0.00 Sec)

Mysql> show profiles;
+ ---------- + ------------ + --------------------------------------- +
| Query_id | duration | query |
+ ---------- + ------------ + --------------------------------------- +
| 1 | 0.00382400 | select @ profiling |
| 2 | 0.00268500 | create table T5 as select * from T1 |
| 2 | 0.00017200 | select database () |
| 4 | 0.01985400 | show databases |
| 5 | 0.00018900 | show tables |
| 6 | 0.06225200 | create table T5 as select * from T1 |
| 7 | 0.00368800 | select count (*) from T5 |
| 8 | 0.00322200 | select count (*) from T5 |
+ ---------- + ------------ + --------------------------------------- +
8 rows in SET (0.01 Sec)

Mysql>
Mysql> show profile for query 7;
+ -------------------- + ---------- +
| Status | duration |
+ -------------------- + ---------- +
| (Initialization) | 0.000414 |
| Opening tables | 0.000599 |
| System lock | 0.000254 |
| Table lock | 0.000175 |
| Init | 1, 0.000052 |
| Optimizing | 0.00001 |
| Executing | 0.002107 |
| End| 0.000042 |
| Query end | 0.000005 |
| Freeing items | 0.000014 |
| Closing tables | 0.000011 |
| Logging slow query | 0.000005 |
+ -------------------- + ---------- +
12 rows in SET (0.03 Sec)

Mysql> show profile for query 8;
+ -------------------- + ---------- +
| Status | duration |
+ -------------------- + ---------- +
| (Initialization) | 0.000064 |
| Opening tables | 0.000018 |
| System lock | 0.00001 |
| Table lock | 0.000013 |
| Init | 1, 0.00002 |
| Optimizing | 0.00001 |
| Executing | 0.002589 |
| End| 0.000459 |
| Query end | 0.000007 |
| Freeing items | 0.000015 |
| Closing tables | 0.000012 |
| Logging slow query | 0.000005 |
+ -------------------- + ---------- +
12 rows in SET (0.00 Sec)

Mysql> select sum (format (duration, 6) as duration from information_schema.profiling where query_id = 7;
+ ---------- +
| Duration |
+ ---------- +
| 1, 0.003688 |
+ ---------- +
1 row in SET (0.02 Sec)

Mysql> select sum (format (duration, 6) as duration from information_schema.profiling where query_id = 8;
+ ---------- +
| Duration |
+ ---------- +
| 1, 0.003222 |
+ ---------- +
1 row in SET (0.00 Sec)

Mysql>

From the above information, we can see that the profile statistics of the two sqls differ greatly from the first four. This is the main difference between the two sqls, and there are many

Cached. The SQL Performance Analyzer can help us diagnose SQL statements that are difficult to identify performance problems and find out the root cause of the problem.

------ End -----