The profiling of MySQL optimization tool

The profiling of MySQL optimization tool

After using the slow query log to parse out the slow query statement, analyze the performance of the statement with profiling after optimization.

View slow query settings

Mysql> Show variables like "%slow%";

+---------------------+---------------------------------+

| variable_name | Value |

+---------------------+---------------------------------+

| log_slow_queries | On |

| Slow_launch_time | 2 |

| Slow_query_log | On |

| Slow_query_log_file | /var/lib/mysql/slow-queries.log |

+---------------------+---------------------------------+

4 rows in Set (0.00 sec)

Change the file location of the slow query log:

Mkdir/data/slow

Chown-r Mysql:mysql/data/slow

mysql> Set Global slow_query_log_file = "/data/slow/slow.log";

Mysql> Show variables like "%long_query_time%";

+-----------------+-----------+

| variable_name | Value |

+-----------------+-----------+

| Long_query_time | 10.000000 |

+-----------------+-----------+

1 row in Set (0.00 sec)

Set the slow query record in seconds

mysql> set long_query_time = 1;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like "%long_query_time%";

+-----------------+----------+

| variable_name | Value |

+-----------------+----------+

| Long_query_time | 1.000000 |

+-----------------+----------+

1 row in Set (0.00 sec)

Mysql> Show variables like "%pro%";

+---------------------------+-------+

| variable_name | Value |

+---------------------------+-------+

| have_profiling | YES |

| Profiling | OFF |

| Profiling_history_size | 15 |

| protocol_version | 10 |

|       Proxy_user | |

| Slave_compressed_protocol | OFF |

| Stored_program_cache | 256 |

+---------------------------+-------+

7 Rows in Set (0.00 sec)

Open profiling

mysql> Set profiling = 1;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like "%pro%";

+---------------------------+-------+

| variable_name | Value |

+---------------------------+-------+

| have_profiling | YES |

| Profiling | On |

| Profiling_history_size | 15 |

| protocol_version | 10 |

|       Proxy_user | |

| Slave_compressed_protocol | OFF |

| Stored_program_cache | 256 |

+---------------------------+-------+

7 Rows in Set (0.00 sec)

View SQL statement execution

Mysql> Show Profiles;

+----------+------------+--------------------------------------------------------------------+

| query_id | Duration | Query |

+----------+------------+--------------------------------------------------------------------+

| 10 | 0.00034150 | Show variables like "%slow%" |

| 11 | 0.00009450 | Set global Slow_query_log_file =/data/slow.log |

| 12 | 0.00008825 | Set global Slow_query_log_file = "/data/slow.log" |

| 13 | 0.00016525 | Set global Slow_query_log_file = "/data/slow/slow.log" |

| 14 | 0.00004975 | Set global Slow_query_log_file =/data/slow/slow.log |

| 15 | 0.00031400 | Show variables like "%slow%" |

| 16 | 0.00005325 | Set global Slow_query_log_file =/var/lib/mysql/slow-queries.log |

| 17 | 0.00009750 | Set global Slow_query_log_file = "/var/lib/mysql/slow-queries.log" |

| 18 | 0.00029575 | Show variables like "%slow%" |

| 19 | 0.00028575 | Show variables like "%pro%" |

| 20 | 0.00032200 | Show variables like "%pro%" |

| 21 | 0.00028725 | Show variables like "%pro%" |

| 22 | 0.00003675 | Show profile for Qurey 21 |

| 23 | 0.00004700 | Show profile for Qurey 1 |

| 24 | 0.00003450 | Reset Query Cache |

+----------+------------+--------------------------------------------------------------------+

Rows in Set (0.00 sec)

To view the details of a single SQL statement

Mysql> Show profile for query 15;

+--------------------+----------+

| Status | Duration |

+--------------------+----------+

| Starting | 0.000049 |

| Opening Tables | 0.000029 |

| System Lock | 0.000006 |

| init | 0.000006 |

| Optimizing | 0.000003 |

| Statistics | 0.000006 |

| Preparing | 0.000006 |

| Executing | 0.000174 |

| Sending Data | 0.000012 |

| End | 0.000003 |

| Query End | 0.000002 |

| Closing Tables | 0.000002 |

| removing TMP table | 0.000004 |

| Closing Tables | 0.000002 |

| Freeing items | 0.000008 |

| Logging Slow Query | 0.000002 |

| Cleaning Up | 0.000002 |

+--------------------+----------+

$ rows in Set (0.00 sec)

Clear the SQL cache

mysql> reset query Cache;

Query OK, 0 rows Affected (0.00 sec)

This article is from the "12213582" blog, please be sure to keep this source http://12223582.blog.51cto.com/12213582/1876071

The profiling of MySQL optimization tool