Tutorial on how to use profile in MySQL

What is a profile?

We can use it when we want to analyze the performance of a certain SQL.

Profiling is only available after MySQL 5.0.3.

After starting the profile, all queries including incorrect statements will be recorded.

Close the session or set profiling=0 to close it. (If you set the profiling_history_size parameter to 0, this also has the effect of turning off MySQL profiling.)

This tool can be used to query the SQL execution status, how much time is spent on System lock and Table lock, etc.

It is very important to locate the I/O consumption and CPU consumption of a statement. (The two largest resources consumed by SQL statement execution are IO and CPU)

--After MySQL 5.7, profile information will be gradually abandoned. MySQL recommends using performance schema

MySQL official website definition

The SHOW PROFILE and SHOW PROFILES statements display profiling information that indicates resource usage for statements executed during the course of the current session.

Simply put, the current session resource consumption.

Note: show profile and show Profiles are not recommended and may be deleted in later versions of MySQL; the official website recommends using Performance Schema

How to use

The profile is disabled by default and is recommended for production environments.

View the profile settings of the current environment

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+

profiling off means profiling is turned off, and profiling_history_size 15 means saving the resource consumption of the most recent 15 SQL statements.

To enable the profile function, you can use the command

set global profiling = 1;

Then you can use the following command

show profiles;

View the latest 15 SQL statements;

If you want to view the specific situation of a certain item, the SQL format is:

SHOW PROFILE [type [, type] ... ]
 [FOR QUERY n]
 [LIMIT row_count [OFFSET offset]]

type: {
 ALL
 | BLOCK IO
 | CONTEXT SWITCHES
 | CPU
 |IPC
 | MEMORY
 | PAGE FAULTS
 | SOURCE
 | SWAPS
}

The official website explains each field in type as follows:

ALL displays all information

BLOCK IO displays counts for block input and output operations

CONTEXT SWITCHES displays counts for voluntary and involuntary context switches

CPU displays user and system CPU usage times

IPC displays counts for messages sent and received

MEMORY is not currently implemented

PAGE FAULTS displays counts for major and minor page faults

SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs

SWAPS displays swap counts

Profiling is valid for each session. When the session ends, the current profiling information will be lost.

Use Cases

mysql> show profiles;
+----------+------------+----------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------+
| 1 | 0.00060275 | select * from customers |
| 2 | 0.00222450 | show tables |
| 3 | 0.00567425 | select * from offices |
| 4 | 0.00052050 | show tables |
| 5 | 0.01123300 | select * from payments |
| 6 | 0.00111675 | show tables |
| 7 | 0.02049625 | select * from productlines |
+----------+------------+----------------------------+

When troubleshooting SQL execution, or which SQL is executing very slowly and where it is slow, profiles are very useful tools.

Shows where a SQL statement is spent

mysql> show profile for query 7;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000043 |
| checking permissions | 0.000005 |
| Opening tables | 0.014552 |
| init | 0.000025 |
| System lock | 0.000009 |
| optimizing | 0.000004 |
| statistics | 0.000011 |
| preparing | 0.000010 |
| executing | 0.000003 |
| Sending data | 0.005653 |
| end | 0.000010 |
| query end | 0.000009 |
| closing tables | 0.000020 |
| freeing items | 0.000121 |
| cleaning up | 0.000023 |
+----------------------+----------+

The information is clear at a glance, so I can get a general understanding of the SQL execution.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.