How MySQL uses profile to analyze statement performance consumption 04/24 Update SLTechnology News&Howtos

How MySQL uses profile to analyze statement performance consumption

2025-04-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

Editor to share with you how MySQL uses profile to analyze sentence performance consumption, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

MySQL uses profile to analyze statement performance consumption

MySQL can use profile to analyze the performance consumption of SQL statements. For example, query how long SQL will execute, and see information such as CPU, memory usage, system lock and table lock time spent during execution.

Use the parameter have_profiling to check whether MySQL supports profile, and the parameter profiling to check whether the current system profile is enabled:

Check to see if profile is enabled:

Mysql > show variables like'% profil%'

+-+ +

| | Variable_name | Value |

+-+ +

| | profiling_history_size | 15 |-sets the number of reserved profiling. Default is 15, and the range is 0 to 100. profiling is disabled if it is 0 |

+-+ +

Here are some common commands about profile:

L set profiling = 1; # based on session level, set profiling = off is used for closing.

L show profile for query 1; # 1 is query_id

L show profile cpu for query 1; # View the consumption of CPU

L show profile memory for query 1; # View memory consumption

L show profile block io,cpu for query 1; # check the consumption of Ibank O and CPU

Among the results of the command "show profile for query" is Sending data, which indicates that the MySQL thread starts accessing the data row and returns the result to the client, not just to the client. Because in the Sending data state, the MySQL thread often needs to do a lot of disk read operations, it is often the longest state in the whole query.

You can query the overall percentage of SQL consumption using the following statement:

SELECT STATE

SUM (DURATION) AS TOTAL_R

ROUND (100 * SUM (DURATION) / (SELECT SUM (DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1), 2) AS PCT_R

COUNT (*) AS CALLS

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

FROM INFORMATION_SCHEMA.PROFILING

WHERE QUERY_ID = 1

GROUP BY STATE

ORDER BY TOTAL_R DESC

Profile is a very quantitative index, which can be used to compare the consumption of various resources according to these quantitative indicators, which is conducive to the overall control of SQL statements. After obtaining the most time-consuming thread state, MySQL supports further selecting all, cpu, block io, context switch, page faults and other detail types to see what resources MySQL is spending too much time on.

You can view the file, function name and specific number of lines of source file corresponding to each step of SQL parsing through show profile source for query:

Mysql > show profile source for query 1

+-- +

+-- +

+-- +

5 rows in set, 1 warning (0.01sec)

Show profile can help DBA understand where the time is going when doing SQL optimization, and starting with MySQL 5.6, you can further learn how to choose the execution plan for the optimizer through the trace file.

Examples of usage are as follows:

Mysql > SELECT @ @ profiling

+-+

| | @ @ profiling |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

Mysql > SET profiling = 1

Query OK, 0 rows affected (0.00 sec)

Mysql > DROP TABLE IF EXISTS T1

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > CREATE TABLE T1 (id INT)

Query OK, 0 rows affected (0.01 sec)

Mysql > SHOW PROFILES

+-+

+-+

| | 0 | 0.000088 | SET PROFILING = 1 |

| | 1 | 0.000136 | DROP TABLE IF EXISTS T1 |

| | 2 | 0.011947 | CREATE TABLE T1 (id INT) |

+-+

3 rows in set (0.00 sec)

Mysql > SHOW PROFILE

+-+ +

| | Status | Duration |

+-+ +

| | checking permissions | 0.000040 | |

| | creating table | 0.000056 | |

| | After create | 0.011363 | |

| | query end | 0.000375 | |

| | freeing items | 0.000089 | |

| | logging slow query | 0.000019 | |

| | cleaning up | 0.000005 | |

+-+ +

7 rows in set (0.00 sec)

Mysql > SHOW PROFILE FOR QUERY 1

+-+ +

| | Status | Duration |

+-+ +

| | query end | 0.000107 | |

| | freeing items | 0.000008 | |

| | logging slow query | 0.000015 | |

| | cleaning up | 0.000006 | |

+-+ +

4 rows in set (0.00 sec)

Mysql > SHOW PROFILE CPU FOR QUERY 2

+-+

+-+

| | checking permissions | 0.000040 | 0.000038 | 0.000002 | |

| | creating table | 0.000056 | 0.000028 | 0.000028 | |

| | After create | 0.011363 | 0.000217 | 0.001571 | |

| | query end | 0.000375 | 0.000013 | 0.000028 | |

| | freeing items | 0.000089 | 0.000010 | 0.000014 | |

| | logging slow query | 0.000019 | 0.000009 | 0.000010 | |

| | cleaning up | 0.000005 | 0.000003 | 0.000002 | |

+-+

It should be noted that INFORMATION_SCHEMA.PROFILING and SHOW PROFILES have been marked as repealed in MySQL 5.7.2 with the warning message:

'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead

'INFORMATION_SCHEMA.PROFILING' is deprecated and will be removed in a future release. Please use Performance Schema instead

Please refer to the official website: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html.

& description:

For more information about profile, please refer to my blog: http://blog.itpub.net/26736162/viewspace-2135700/.

Https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html

Mysql >? SHOW PROFILES

Name: 'SHOW PROFILES'

Description:

Syntax:

SHOW PROFILES

The SHOW PROFILES statement, together with SHOW PROFILE, displays

Profiling information that indicates resource usage for statements

Executed during the course of the current session. For more

Information, see [HELP SHOW PROFILE].

* Note*:

These statements are deprecated and will be removed in a future MySQL

Release. Use the Performance Schema instead; see

Http://dev.mysql.com/doc/refman/5.7/en/performance-schema.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/show-profiles.html

Mysql >? SHOW PROFILE

Name: 'SHOW PROFILE'

Description:

Syntax:

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 SHOW PROFILE and SHOW PROFILES statements display profiling

Information that indicates resource usage for statements executed

During the course of the current session.

* Note*:

These statements are deprecated and will be removed in a future MySQL

Release. Use the Performance Schema instead; see

Http://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profili

Ng.html.

Profiling is controlled by the profiling session variable, which has a

Default value of 0 (OFF). Profiling is enabled by setting profiling to

1 or ON:

Mysql > SET profiling = 1

SHOW PROFILES displays a list of the most recent statements sent to the

Server. The size of the list is controlled by the

Profiling_history_size session variable, which has a default value of

15. The maximum value is 100. Setting the value to 0 has the practical

Effect of disabling profiling.

All statements are profiled except SHOW PROFILE and SHOW PROFILES, so

You will find neither of those statements in the profile list.

Malformed statements are profiled. For example, SHOW PROFILING is an

Illegal statement, and a syntax error occurs if you try to execute it

But it will show up in the profiling list.

SHOW PROFILE displays detailed information about a single statement.

Without the FOR QUERY n clause, the output pertains to the most

Recently executed statement. If FOR QUERY n is included, SHOW PROFILE

Displays information for statement n. The values of n correspond to the

Query_ID values displayed by SHOW PROFILES.

The LIMIT row_count clause may be given to limit the output to

Row_count rows. If LIMIT is given, OFFSET offset may be added to begin

The output offset rows into the full set of rows.

By default, SHOW PROFILE displays Status and Duration columns. The

Status values are like the State values displayed by SHOW PROCESSLIST

Although there might be some minor differences in interpretion for the

Two statements for some status values (see

Http://dev.mysql.com/doc/refman/5.7/en/thread-information.html).

Optional type values may be specified to display specific additional

Types of information:

O ALL displays all information

O BLOCK IO displays counts for block input and output operations

O CONTEXT SWITCHES displays counts for voluntary and involuntary

Context switches

O CPU displays user and system CPU usage times

O IPC displays counts for messages sent and received

O MEMORY is not currently implemented

O PAGE FAULTS displays counts for major and minor page faults

O 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

O SWAPS displays swap counts

Profiling is enabled per session. When a session ends, its profiling

Information is lost.

URL: http://dev.mysql.com/doc/refman/5.7/en/show-profile.html

Examples:

Mysql > SELECT @ @ profiling

+-+

| | @ @ profiling |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

Mysql > SET profiling = 1

Query OK, 0 rows affected (0.00 sec)

Mysql > DROP TABLE IF EXISTS T1

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > CREATE TABLE T1 (id INT)

Query OK, 0 rows affected (0.01 sec)

Mysql > SHOW PROFILES

+-+

+-+

| | 0 | 0.000088 | SET PROFILING = 1 |

| | 1 | 0.000136 | DROP TABLE IF EXISTS T1 |

| | 2 | 0.011947 | CREATE TABLE T1 (id INT) |

+-+

3 rows in set (0.00 sec)

Mysql > SHOW PROFILE

+-+ +

| | Status | Duration |

+-+ +

| | checking permissions | 0.000040 | |

| | creating table | 0.000056 | |

| | After create | 0.011363 | |

| | query end | 0.000375 | |

| | freeing items | 0.000089 | |

| | logging slow query | 0.000019 | |

| | cleaning up | 0.000005 | |

+-+ +

7 rows in set (0.00 sec)

Mysql > SHOW PROFILE FOR QUERY 1

+-+ +

| | Status | Duration |

+-+ +

| | query end | 0.000107 | |

| | freeing items | 0.000008 | |

| | logging slow query | 0.000015 | |

| | cleaning up | 0.000006 | |

+-+ +

4 rows in set (0.00 sec)

Mysql > SHOW PROFILE CPU FOR QUERY 2

+-+

+-+

| | checking permissions | 0.000040 | 0.000038 | 0.000002 | |

| | creating table | 0.000056 | 0.000028 | 0.000028 | |

| | After create | 0.011363 | 0.000217 | 0.001571 | |

| | query end | 0.000375 | 0.000013 | 0.000028 | |

| | freeing items | 0.000089 | 0.000010 | 0.000014 | |

| | logging slow query | 0.000019 | 0.000009 | 0.000010 | |

| | cleaning up | 0.000005 | 0.000003 | 0.000002 | |

+-+

7 rows in set (0.00 sec)

Detailed explanation of mysql statement performance overhead Detection profiling

I have introduced the use of msyql query optimization explain check command before. Explain mainly checks the basic performance of sql statements and whether sql is excellent, but can not check the specific cost related to hardware resources. The profiling tool to be introduced today can check the cost of resources in more detail.

First of all, this performance check tool is effective for each session, and it is important to initiate query testing after the end of the session.

It is off by default and needs to be turned on manually:

SET profiling = 1

When enabled, statements sent to the mysql server can be displayed through SHOW PROFILES. 15 statements are displayed by default and the maximum is set to 100. this is achieved by setting the variable profiling_history_size. Setting to 0 will disable profiling.

Grammar

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 definition of type is also simple in English:

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

Use the example

Check to see if profiling is enabled

Mysql > SELECT @ @ profiling

+-+

| | @ @ profiling |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

Turn on profiling

Mysql > SET profiling = 1

Query OK, 0 rows affected (0.00 sec)

Run the SQL statement to be parsed

Mysql > DROP TABLE IF EXISTS T1

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > CREATE TABLE T1 (id INT)

Query OK, 0 rows affected (0.01 sec)

Check the performance metrics of all captured analysis statements

Mysql > SHOW PROFILES

+-+

+-+

| | 0 | 0.000088 | SET PROFILING = 1 |

| | 1 | 0.000136 | DROP TABLE IF EXISTS T1 |

| | 2 | 0.011947 | CREATE TABLE T1 (id INT) |

+-+

3 rows in set (0.00 sec)

Displays the performance indicator of a single analysis statement, which is the one that has been executed the most recently

Mysql > SHOW PROFILE

+-+ +

| | Status | Duration |

+-+ +

| | checking permissions | 0.000040 | |

| | creating table | 0.000056 | |

| | After create | 0.011363 | |

| | query end | 0.000375 | |

| | freeing items | 0.000089 | |

| | logging slow query | 0.000019 | |

| | cleaning up | 0.000005 | |

+-+ +

7 rows in set (0.00 sec)

Check the performance of an analysis statement

Mysql > SHOW PROFILE FOR QUERY 1

+-+ +

| | Status | Duration |

+-+ +

| | query end | 0.000107 | |

| | freeing items | 0.000008 | |

| | logging slow query | 0.000015 | |

| | cleaning up | 0.000006 | |

+-+ +

4 rows in set (0.00 sec)

You can also view CPU or other resource consumption information

Mysql > SHOW PROFILE CPU FOR QUERY 2

+-+

+-+

| | checking permissions | 0.000040 | 0.000038 | 0.000002 | |

| | creating table | 0.000056 | 0.000028 | 0.000028 | |

| | After create | 0.011363 | 0.000217 | 0.001571 | |

| | query end | 0.000375 | 0.000013 | 0.000028 | |

| | freeing items | 0.000089 | 0.000010 | 0.000014 | |

| | logging slow query | 0.000019 | 0.000009 | 0.000010 | |

| | cleaning up | 0.000005 | 0.000003 | 0.000002 | |

+-+

7 rows in set (0.00 sec)

Other ways of use

You can also view the performance of the parsing statement by looking up the table, and all that show can see is recorded in the INFORMATION_ schema table, such as:

SELECT STATE, FORMAT (DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ

Table of correspondence between SHOW and INFORMATION_SCHEMA:

INFORMATION_SCHEMA NameSHOW NameRemarksQUERY_IDQuery_ID

SEQ

STATEStatus

DURATIONDuration

CPU_USERCPU_user

CPU_SYSTEMCPU_system

CONTEXT_VOLUNTARYContext_voluntary

CONTEXT_INVOLUNTARYContext_involuntary

BLOCK_OPS_INBlock_ops_in

BLOCK_OPS_OUTBlock_ops_out

MESSAGES_SENTMessages_sent

MESSAGES_RECEIVEDMessages_received

PAGE_FAULTS_MAJORPage_faults_major

PAGE_FAULTS_MINORPage_faults_minor

SWAPSSwaps

SOURCE_FUNCTIONSource_function

SOURCE_FILESource_file

SOURCE_LINESource_line

Be careful

The use of INFORMATION_SCHEMA has been marked and abolished in mysql5.7.2, and will be completely deleted in future versions. The use of SHOW will also be replaced by MySQL Performance Schema in future versions. For more information, please refer to the official website: https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html.

All the translations of the above profiling introductions come from the official website. The original version can refer to: https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

Https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

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

Note

These statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see Section 25.17.1, "Query Profiling Using Performance Schema".

Profiling is controlled by the profiling session variable, which has a default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON:

Mysql > SET profiling = 1

SHOW PROFILES displays a list of the most recent statements sent to the server. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling.

All statements are profiled except SHOW PROFILE and SHOW PROFILES, so you will find neither of those statements in the profile list. Malformed statements are profiled. For example, SHOW PROFILING is an illegal statement, and a syntax error occurs if you try to execute it, but it will show up in the profiling list.

SHOW PROFILE displays detailed information about a single statement. Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES.

The LIMIT row_count clause may be given to limit the output to row_count rows. If LIMIT is given, OFFSET offset may be added to begin the output offset rows into the full set of rows.

By default, SHOW PROFILE displays Status and Duration columns. The Status values are like the State values displayed by SHOW PROCESSLIST, although there might be some minor differences in interpretion for the two statements for some status values (see Section 9.14, "Examining Thread Information").

Optional type values may be specified to display specific additional types of information:

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 enabled per session. When a session ends, its profiling information is lost.

Mysql > SELECT @ @ profiling; +-+ | @ profiling | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql > SET profiling = 1; Query OK, 0 rows affected (0.00 sec) mysql > DROP TABLE IF EXISTS T1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > CREATE TABLE T1 (id INT) Query OK, 0 rows affected (0.01 sec) mysql > SHOW PROFILES +-- + | Query_ID | Duration | Query | +-- + | 0 | | 0.000088 | SET PROFILING = 1 | | 1 | 0.000136 | DROP TABLE IF EXISTS T1 | | 2 | 0.011947 | CREATE TABLE T1 (id INT) | +-- + 3 rows in set (sec) mysql > SHOW PROFILE | +-- +-+ | Status | Duration | +-+-+ | checking permissions | 0.000040 | | creating table | 0.000056 | After create | 0.011363 | | query end | 0.000375 | Freeing items | 0.000089 | logging slow query | 0.000019 | | cleaning up | 0.000005 | +-- +-+ 7 rows in set (0.00 sec) mysql > SHOW PROFILE FOR QUERY 1 +-+-+ | Status | Duration | +-+-+ | query end | 0.000107 | freeing items | 0.000008 | logging slow query | 0.000015 | | cleaning up | 0.000006 | +- -+-+ 4 rows in set (0.00 sec) mysql > SHOW PROFILE CPU FOR QUERY 2 +-+ | Status | Duration | CPU_user | CPU_system | +-- +-- -- +-+ | checking permissions | 0.000040 | 0.000038 | 0.000002 | | creating table | 0.000056 | 0.000028 | 0.000028 | After create | 0.011363 | 0.000217 | 0.001571 | query end | 0.000375 | 0.000013 | 0.000028 | freeing items | 0.000089 | 0.000010 | 0.000014 | logging slow query | 0.000019 | 0.000009 | 0.000010 | | cleaning up | 0.000005 | 0.000003 | 0.000002 | +-+ 7 rows in set (0.000005 sec)

Note

Profiling is only partially functional on some architectures. For values that depend on the getrusage () system call, NULL is returned on systems such as Windows that do not support the call. In addition, profiling is per process and not per thread. This means that activity on threads within the server other than your own may affect the timing information that you see.

You can also get profiling information from the PROFILING table in INFORMATION_SCHEMA. See Section 24.18, "The INFORMATION_SCHEMA PROFILING Table". For example, the following queries produce the same result:

SHOW PROFILE FOR QUERY 2 select STATE, FORMAT (DURATION, 6) AS DURATIONFROM INFORMATION_SCHEMA.PROFILINGWHERE QUERY_ID = 2 ORDER BY SEQ;24.18 The INFORMATION_SCHEMA PROFILING Table

The PROFILING table provides statement profiling information. Its contents correspond to the information produced by the SHOW PROFILES and SHOW PROFILE statements (see Section 14.7.5.31, "SHOW PROFILES Syntax"). The table is empty unless theprofiling session variable is set to 1.

Note

This table is deprecated as of MySQL 5.7.2 and will be removed in a future MySQL release. Use the Performance Schema instead; see Chapter 25, MySQL Performance Schema.

INFORMATION_SCHEMA NameSHOW NameRemarksQUERY_IDQuery_IDSEQ

STATEStatusDURATIONDurationCPU_USERCPU_userCPU_SYSTEMCPU_systemCONTEXT_VOLUNTARYContext_voluntaryCONTEXT_INVOLUNTARYContext_involuntaryBLOCK_OPS_INBlock_ops_inBLOCK_OPS_OUTBlock_ops_outMESSAGES_SENTMessages_sentMESSAGES_RECEIVEDMessages_receivedPAGE_FAULTS_MAJORPage_faults_majorPAGE_FAULTS_MINORPage_faults_minorSWAPSSwapsSOURCE_FUNCTIONSource_functionSOURCE_FILESource_fileSOURCE_LINESource_line

Notes:

QUERY_ID is a numeric statement identifier.

SEQ is a sequence number indicating the display order for rows with the same QUERY_ID value.

STATE is the profiling state to which the row measurements apply.

DURATION indicates how long statement execution remained in the given state, in seconds.

CPU_USER and CPU_SYSTEM indicate user and system CPU use, in seconds.

CONTEXT_VOLUNTARY and CONTEXT_INVOLUNTARY indicate how many voluntary and involuntary context switches occurred.

BLOCK_OPS_IN and BLOCK_OPS_OUT indicate the number of block input and output operations.

MESSAGES_SENT and MESSAGES_RECEIVED indicate the number of communication messages sent and received.

PAGE_FAULTS_MAJOR and PAGE_FAULTS_MINOR indicate the number of major and minor page faults.

SWAPS indicates how many swaps occurred.

SOURCE_FUNCTION, SOURCE_FILE, and SOURCE_LINE provide information indicating where in the source code the profiled state executes.

These are all the contents of the article "how MySQL uses profile to analyze statement performance consumption". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.