SQL Performance Analyzer for MySQL

The main purpose of MySQL's SQL Performance Analyzer is to show how resources are used throughout the process of SQL execution. The profiler can better show
Performance problems with poor SQL.

How to use MySQL SQL profile

1. Turn on MySQL SQL profile

Check if 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 to indicate that MySQL SQL Profiler is off, and if SQL Performance Analyzer is turned on, profiling will have a value of 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)


The above can see that profiling has become 1, but this is session level, the system is not supported. Test as follows

Quit MySQL
Mysql> quit
Bye
[Email protected] ~]# 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 the value of profiling
Mysql> SELECT @ @profiling;
+-------------+
| @ @profiling |
+-------------+
| 0 |
+-------------+
1 row in Set (0.01 sec)

The discovery has changed to the default value of 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 being used with SET GLOBAL
Mysql>

See here the error. So MySQL SQL profile is session-level.

2. For example, see 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
Can turn off this feature to get a quicker startup with-a

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 |
| 3 | 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 | 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 |
+--------------------+----------+
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 | 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 |
+--------------------+----------+
Rows in Set (0.00 sec)

mysql> Select SUM (Format (duration,6)) as duration from information_schema.profiling where query_id=7;
+----------+
| Duration |
+----------+
| 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 |
+----------+
| 0.003222 |
+----------+
1 row in Set (0.00 sec)

Mysql>

From the above information can be seen in the two SQL profile statistics, the first 4 differences are relatively large, this is the main difference of two SQL, the second query has a lot

Cached. SQL Performance Analyzer can help us diagnose some of the problems that are more difficult to identify and identify the source of the problem.

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

SQL Performance Analyzer for MySQL