Preface

1. When we do mysql performance analysis, there are three most commonly used ways:

   (1) Slow query (analysis of sql with problems)
   (2) Explain (shows how mysql uses indexes to process select statements and join tables. It can help to select better indexes and write more optimized query statements.
   (3) Profile.

2. This chapter mainly gives a brief overview of profile, which is used to analyze the performance of an sql statement.
3. Profiling was not available until version 5.0.3 of mysql. But after MySQL 5.7, profile information will be gradually discarded, and MySQL recommends performance schema.
4. profile This tool can be used to query the status of SQL execution, how long does System lock and Table lock take, etc. It is very important to locate the I/O consumption and CPU consumption of a statement. (IO and CPU are the two largest resources consumed by SQL statement execution)

profile tool use

1. View your own version of mysql:

   mysql> select version(); 
   +------------+
   | version()  |
   +------------+
   | 5.6.35-log |
   +------------+ 

2. See if profile functionality is turned on (profiling=on stands for opening):

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

3. Open profile:

   mysql> set profile=1; 

4. After opening the profile, execute the sql statement to be analyzed:

   mysql> select t1.*,t2.action from pre_forum_thread as t1
   left join 
   (select a.* from pre_forum_threadmod as a,(select tid,max(dateline) as dateline from pre_forum_threadmod group by tid) as b
   where a.tid=b.tid and a.dateline=b.dateline) as t2
   on t1.tid=t2.tid
   where t1.displayorder>=0 and t1.fid in (47,49) and t1.tid > 100318 
   and (t1.authorid =7683017 or t2.action<>'DWN' or t2.action is null )
   order by t1.dateline desc limit 20;

5. View the generated profile information:

   mysql> show profiles;
   +----------+------------+--------------------------------------------------------------------------------------------------------+
   | Query_ID | Duration  | Query                                                                                                  |
   +----------+------------+--------------------------------------------------------------------------------------------------------+
   |        1 | 1.37183777 | select t1.*,t2.action from pre_forum_thread as t1  |
   |        2 | 0.00078796 | show columns from `bbs`.`t2` |
   |        3 | 0.00150425 | show columns from `bbs`.`pre_forum_thread` |
   +----------+------------+--------------------------------------------------------------------------------------------------------+

6. Gets the overhead of the specified query statement:

   mysql> show profile for query 2; 
   +----------------------+----------+
   | Status              | Duration |
   +----------------------+----------+
   | starting            | 0.000147 |
   | checking permissions | 0.000023 |
   | Opening tables      | 0.000047 |
   | init                | 0.000081 |
   | System lock          | 0.000031 |
   | optimizing          | 0.000034 |
   | statistics          | 0.001650 |
   | preparing            | 0.000046 |
   | executing            | 0.000018 |
   | Sending data        | 2.460588 |
   | end                  | 0.000041 |
   | query end            | 0.000019 |
   | closing tables      | 0.000022 |
   | freeing items        | 0.000055 |
   | cleaning up          | 0.000085 |
   +----------------------+----------+ 
   

7. Close profile:

   mysql> set profiling=0;

8. Specific parameters:

   type: 
      ALL -- Displays all overhead information 
    | BLOCK IO -- Display Block IO Related Overhead 
    | CONTEXT SWITCHES -- Context Switching Related Overhead 
    | CPU -- Displays CPU-related overhead information 
    | IPC -- Display Sending and Receiving Related Overhead Information 
    | MEMORY -- Display memory-related overhead information 
    | PAGE FAULTS -- Display page error-related overhead information 
    | SOURCE -- Displays overhead information related to Source_function, Source_file, Source_line 
    | SWAPS -- Displays information about the cost associated with the number of exchanges 
   
   For example, to view cpu and io overhead, you can execute commands:
   mysql> SHOW profile CPU,BLOCK IO  FOR query 2;

summary

1. General simple process:

   (1)set profiling=1; //Open profile analysis
   (2)run your sql1;
   (3)run your sql2;
   (4)show profiles;    //View the statement analysis of sql1,sql2
   (5)SHOW profile CPU,BLOCK IO io FOR query 1; //View CPU, IO consumption
   (6)set profiling=0; //Close profile analysis

Reference link

https://blog.csdn.net/ty_hf/a...
https://www.linuxidc.com/Linu...