View the MySQL show profile (profiling Report)

View the MySQL show profile (profiling Report)

The show profil command is provided by MySQL to analyze the resource consumption of statement execution in the current session. It can be used for SQL optimization measurement.

1. Enabling and disabling Parameters

1.1 view parameters

By default, the parameter is disabled and the result of the last 15 running times is saved.

Mysql> show variables like 'profiling % ';

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

| Variable_name | Value |

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

| Profiling | OFF |

| Profiling_history_size | 15 |

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

2 rows in set

1.2 enable and disable parameters (the parameter is a session-level parameter and only valid for the current session)

The procedure is as follows:

Mysql> SET profiling = 1; or SET profiling = on;

Mysql> SET profiling = on;

Query OK, 0 rows affected

 

Mysql> show variables like 'profiling % ';

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

| Variable_name | Value |

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

| Profiling | ON |

| Profiling_history_size | 15 |

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

2 rows in set

Closed Operation:
Mysql> SET profiling = 0; or SET profiling = off;

Ii. Procedure

2.1 enable: SET profiling = on;

2.2 run the corresponding SQL statement;

2.3 view the overall results: show profiles;

2.4 view the detailed results: show profile for query n, where n corresponds to Query_ID in the show profiles output;

Mysql> show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 0.00077425 | select count (*) from tab_user_info |

| 2 | 0.0013575 | select count (*) from tab_tel_area |

| 3 | 9.7E-5 | select count (*) from tab_tel_area |

| 4 | 0.005193 | show variables like 'profiling % '|

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

4 rows in set

 

Mysql> show profile for query 2;

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

| Status | Duration |

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

| Starting | 2E-5 |

| Checking query cache for query | 4.7E-5 |

| Opening tables | 0.001163 |

| System lock | 4E-6 |

| Table lock | 4.1E-5 |

| Init | 1.6E-5 |

| Optimizing | 6E-6 |

| Executing | 1.4E-5 |

| End | 5E-6 |

| Query end | 3E-6 |

| Freeing items | 3.1E-5 |

| Storing result in query cache | 5E-6 |

| Logging slow query | 3E-6 |

| Cleaning up | 2E-6 |

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

14 rows in set

Note: The report shows each step of query execution and the time spent. When a statement is executed very easily, you can clearly see the time spent in each order of statements, however, when statements are nested loops and other operations, reading this report will become very painful. Therefore, the following statements are compiled to summarize the operations of the same type. The script is as follows:

Mysql> SET @ QUERY_ID = 1;

Mysql> select state, SUM (DURATION) AS TOTAL_R,

ROUND (100 * SUM (DURATION)/(select sum (DURATION)

FROM INFORMATION_SCHEMA.PROFILING

WHERE QUERY_ID = @ QUERY_ID), 2) AS PCT_R,

COUNT (*) as cils,

SUM (DURATION)/COUNT (*) AS "R/CALL"

FROM INFORMATION_SCHEMA.PROFILING

WHERE QUERY_ID = @ QUERY_ID

GROUP BY STATE

Order by TOTAL_R DESC;

Summary: this tool reminds me of Oracle's autotrace tool. If an execution plan is provided together, the statement optimization will be more helpful;

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: