How to use profile for Mysql tuning

The content of this article is about how to use the Mysql tuning profile. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

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

   (1) Slow query (analyzing SQL that has problems)
   (2) Explain (shows how mysql uses indexes to process select statements and connection tables. It can help choose better indexes and write more optimized query statements)
   (3) Profile (how long it will take to execute the SQL query) , and see the CPU/Memory usage, how much time Systemlock, Table lock takes during execution, etc.)
   

2. This chapter mainly gives a brief overview of the profile. Use To perform performance analysis on a certain SQL statement.

3. Profiling is only available since mysql5.0.3. However, after mysql5.7, profile information will be gradually abandoned, and mysql recommends using performance schema.

4. profileThis tool can be used to query the SQL execution status, how long System lock and Table lock take, 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)

Use the profile tool

1. to view your own mysql version:

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

2. Check whether the profile function is enabled (profiling=on means enabled):

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

3. Enable 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. Get the specified Overhead of 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. Related specific parameters:

   type: 
      ALL                --显示所有的开销信息 
    | BLOCK IO          --显示块IO相关开销 
    | CONTEXT SWITCHES  --上下文切换相关开销 
    | CPU                --显示CPU相关开销信息 
    | IPC                --显示发送和接收相关开销信息 
    | MEMORY            --显示内存相关开销信息 
    | PAGE FAULTS        --显示页面错误相关开销信息 
    | SOURCE            --显示和Source_function，Source_file，Source_line相关的开销信息 
    | SWAPS              --显示交换次数相关开销的信息 
   
   例如，想要查看cpu和io开销可以执行命令：
   mysql> SHOW profile CPU,BLOCK IO  FOR query 2;

Summary

1. ##General simple process:

   （1）set profiling=1; //打开profile分析
   （2）run your sql1;
   （3）run your sql2;
   （4）show profiles;    //查看sql1,sql2的语句分析
   （5）SHOW profile CPU,BLOCK IO io FOR query 1; //查看CPU、IO消耗
   （6）set profiling=0; //关闭profile分析

The above is the detailed content of How to use profile for Mysql tuning. For more information, please follow other related articles on the PHP Chinese website!