MySQL worry-view tool profile

MySQL can use its own profile function to see what system resources are required to execute the statement, such as CPU, memory, disk IO, the default is the shutdown state to be manually opened, profile for the management or development of the DBA is a great help.

1. View the status of the profile feature

[Email protected] ~]#/usr/local/mysql-5.6.23/bin/mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 5499
Server Version:5.6.23-log MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> Show variables like '%pro% ';
+---------------------------+-------+
| variable_name | Value |
+---------------------------+-------+
| have_profiling | YES |
|Profiling | OFF| --Default is off state
| Profiling_history_size | 15 |
| protocol_version | 10 |
|       Proxy_user | |
| Slave_compressed_protocol | OFF |
| Stored_program_cache | 256 |
+---------------------------+-------+
7 Rows in Set (0.00 sec)

mysql> SET @ @profiling = 1;
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> Show variables like '%profiling% ';
+------------------------+-------+
| variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
|Profiling | on|
| Profiling_history_size | 15 |
+------------------------+-------+
3 Rows in Set (0.00 sec)

Mysql> select * from T;
+------+
| A |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)

Mysql> Show Profiles;
+----------+------------+-----------------------------------+
| query_id | Duration | Query |
+----------+------------+-----------------------------------+
| 1 | 0.00051675 | Show variables like '%profil% ' |
| 2 | 0.00052600 | Show variables like '%profiling% ' |
| 3 | 0.00010600 | SELECT DATABASE () |
| 4 | 0.00025225 | Show Databases |
| 5 | 0.00012550 | Show Tables |
| 6 | 0.00021525 | SELECT * FROM T |
+----------+------------+-----------------------------------+
6 rows in Set, 1 Warning (0.00 sec)

Mysql> Show profile for query 6; --View the resources consumed by the query statement that QUERY_ID is 6

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| Starting | 0.000058 |
| Checking Permissions | 0.000007 |
| Opening Tables | 0.000020 |
| init | 0.000015 |
| System Lock | 0.000010 |
| Optimizing | 0.000004 |
| Statistics | 0.000012 |
| Preparing | 0.000011 |
| Executing | 0.000002 |
| Sending Data | 0.000037 |
| End | 0.000004 |
| Query End | 0.000007 |
| Closing Tables | 0.000008 |
| Freeing items | 0.000010 |
| Cleaning Up | 0.000012 |
+----------------------+----------+
Rows in Set, 1 Warning (0.00 sec)

Mysql> Show profile BLOCK io,cpu,memory for query 6;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | Cpu_user | Cpu_system | block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| Starting | 0.000058 |   0.000000 |            0.000000 |             0 | 0 |
| Checking Permissions | 0.000007 |   0.000000 |            0.000000 |             0 | 0 |
| Opening Tables | 0.000020 |   0.000000 |            0.000000 |             0 | 0 |
| init | 0.000015 |   0.000000 |            0.000000 |             0 | 0 |
| System Lock | 0.000010 |   0.000000 |            0.000000 |             0 | 0 |
| Optimizing | 0.000004 |   0.000000 |            0.000000 |             0 | 0 |
| Statistics | 0.000012 |   0.000000 |            0.000000 |             0 | 0 |
| Preparing | 0.000011 |   0.000000 |            0.000000 |             0 | 0 |
| Executing | 0.000002 |   0.000000 |            0.000000 |             0 | 0 |
| Sending Data | 0.000037 |   0.000000 |            0.000000 |             0 | 0 |
| End | 0.000004 |   0.000000 |            0.000000 |             0 | 0 |
| Query End | 0.000007 |   0.000000 |            0.000000 |             0 | 0 |
| Closing Tables | 0.000008 |   0.000000 |            0.000000 |             0 | 0 |
| Freeing items | 0.000010 |   0.000000 |            0.000000 |             0 | 0 |
| Cleaning Up | 0.000012 |   0.000000 |            0.000000 |             0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
Rows in Set, 1 Warning (0.00 sec)

Mysql> CREATE TABLE t2 (a int);
Query OK, 0 rows affected (0.30 sec)

Mysql> Show Profiles;
+----------+------------+-----------------------------------+
| query_id | Duration | Query |
+----------+------------+-----------------------------------+
| 1 | 0.00051675 | Show variables like '%profil% ' |
| 2 | 0.00052600 | Show variables like '%profiling% ' |
| 3 | 0.00010600 | SELECT DATABASE () |
| 4 | 0.00025225 | Show Databases |
| 5 | 0.00012550 | Show Tables |
| 6 | 0.00021525 | SELECT * FROM T |
| 7 | 0.00023775 | Help ' show profile ' |
| 8 | 0.07420075 | INSERT into T VALUES (2) |
| 9 | 0.00017425 | CREATE TABLE T1 (a int) |
| 10 | 0.29320100 | CREATE TABLE t2 (a int) |
+----------+------------+-----------------------------------+
Rows in Set, 1 Warning (0.00 sec)

Mysql> Show profile BLOCK io,cpu,memory for query 10; --View the resources consumed by the query statement that QUERY_ID is 10

+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | Cpu_user | Cpu_system | block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| Starting | 0.000059 |   0.000000 |            0.000000 |             0 | 0 |
| Checking Permissions | 0.000007 |   0.000000 |            0.000000 |             0 | 0 |
| Opening Tables | 0.000059 |   0.000000 |            0.000000 |             0 | 0 |
| Creating Table | 0.259481 |   0.002000 |            0.000000 |           0 | 280 |
| After Create | 0.000026 |   0.000000 |            0.000000 |             0 | 0 |
| Query End | 0.033376 |   0.000000 |            0.000000 |             0 | 8 |
| Closing Tables | 0.000016 |   0.000000 |            0.000000 |             0 | 0 |
| Freeing items | 0.000160 |   0.000000 |            0.000000 |             0 | 0 |
| Cleaning Up | 0.000018 |   0.000000 |            0.000000 |             0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
9 rows in Set, 1 Warning (0.00 sec)

Mysql>

This article is from the "Days Together" blog, please be sure to keep this source http://tongcheng.blog.51cto.com/6214144/1650613

MySQL worry-view tool profile