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

This article introduces you how to use profile in mysql, the content is very detailed, interested friends can refer to, hope to be helpful to you.

What is profile?

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

Profiling is not available until after the mysql5.0.3 version.

After you start profile, all queries, including incorrect statements, are logged.

Close the session or set profiling=0 is closed. (if the profiling_history_size parameter is set to 0, it also has the profiling effect of turning off MySQL. )

This tool can be used to query the execution status of SQL, how much time System lock and Table lock spend, and so on.

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

-- after mysql5.7, profile information will be gradually discarded. 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.

To put it simply, the consumption of current session resources.

Note: both show profile and show Profiles are not recommended and may be deleted in later versions of mysql. Performance Schema is recommended on the official website.

How to use it

Profile is turned off by default and is also recommended in production environments.

View the profile settings for the current environment

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

Profiling off indicates that profile is off, and profiling_history_size 15 means to save the resource consumption of the last 15 SQL.

To enable the profile function, you can use the command

Set global profiling = 1

Then you can use the following command

Show profiles

Check the last 15 SQL entries.

If you want to see the details of a particular 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 interprets the fields 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, and when the session ends, the current profiling information is lost.

Use case

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 | +-+

In troubleshooting SQL execution, or which SQL execution is very slow, where is slow; profile is a very auxiliary tool.

Show the specific cost of a SQL

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 | +-- +-- share here on how to use profile in mysql. | I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.