View the MySQL show profile (profiling Report)

Source: Internet
Author: User

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.