MySQL性能分析工具profile使用教程

2024-04-07 03:17:04 264

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。本文描述了如何使用MySQLprofile，不涉及具体的样例分析。

1、有关profile的描述


--当前版本 

root@localhost[sakila]>showvariableslike'version'; 

+---------------+---------------------------------------+ 

|Variable_name|Value                                | 

+---------------+---------------------------------------+ 

|version      |5.6.17-enterprise-commercial-advanced| 

+---------------+---------------------------------------+ 

 

--查看profiling系统变量 

root@localhost[sakila]>showvariableslike'%profil%'; 

+------------------------+-------+ 

|Variable_name         |Value| 

+------------------------+-------+ 

|have_profiling        |YES  |  --只读变量，用于控制是否由系统变量开启或禁用profiling 

|profiling             |OFF  |  --开启SQL语句剖析功能 

|profiling_history_size|15   |  --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling 

+------------------------+-------+ 

 

profiling[539] 

Ifsetto0orOFF(thedefault),statementprofilingisdisabled.Ifsetto1orON,statementprof 

isenabledandtheSHOWPROFILEandSHOWPROFILESstatementsprovideaccesstoprof 

information.SeeSection13.7.5.32,“SHOWPROFILESSyntax”. 

 

ThisvariableisdeprecatedinMySQL5.6.8andwillberemovedinafutureMySQLrelease. 

profiling_history_size[539] 

Thenumberofstatementsforwhichtomaintainprofilinginformationifprofiling[539]is 

enabled.Thedefaultvalueis15.Themaximumvalueis100.Settingthevalueto0effectively 

disablesprofiling.SeeSection13.7.5.32,“SHOWPROFILESSyntax”. 

ThisvariableisdeprecatedinMySQL5.6.8andwillberemovedinafutureMySQLrelease. 

 

 

--获取profile的帮助 

root@localhost[sakila]>helpprofile; 

Name:'SHOWPROFILE' 

Description: 

Syntax: 

SHOWPROFILE[type[,type]...] 

   [FORQUERYn] 

   [LIMITrow_count[OFFSEToffset]] 

 

type: 

   ALL               --显示所有的开销信息 

 |BLOCKIO          --显示块IO相关开销 

 |CONTEXTSWITCHES  --上下文切换相关开销 

 |CPU               --显示CPU相关开销信息 

 |IPC               --显示发送和接收相关开销信息 

 |MEMORY            --显示内存相关开销信息 

 |PAGEFAULTS       --显示页面错误相关开销信息 

 |SOURCE            --显示和Source_function，Source_file，Source_line相关的开销信息 

 |SWAPS             --显示交换次数相关开销的信息  

 

TheSHOWPROFILEandSHOWPROFILESstatementsdisplayprofiling 

informationthatindicatesresourceusageforstatementsexecuted 

duringthecourseofthecurrentsession. 

 

*Note*:ThesestatementsaredeprecatedasofMySQL5.6.7andwillbe 

removedinafutureMySQLrelease.UsethePerformanceSchemainstead; 

seehttp://dev.mysql.com/doc/refman/5.6/en/performance-schema.html. 

--上面描述从5.6.7开始该命令将会被移除，用PerformanceSchemainstead代替 

--在Oracle数据库中，是通过autotrace来剖析单条SQL并获取真实的执行计划以及其开销信息

2、开启porfiling


--启用session级别的profiling 

root@localhost[sakila]>setprofiling=1; 

QueryOK,0rowsaffected,1warning(0.00sec) 

 

--验证修改后的结果 

root@localhost[sakila]>showvariableslike'%profil%'; 

+------------------------+-------+ 

|Variable_name         |Value| 

+------------------------+-------+ 

|have_profiling        |YES  | 

|profiling             |ON   | 

|profiling_history_size|15   | 

+------------------------+-------+ 

 

--发布SQL查询 

root@localhost[sakila]>selectcount(*)fromcustomer; 

+----------+ 

|count(*)| 

+----------+ 

|     599| 

+----------+ 

 

--查看当前session所有已产生的profile 

root@localhost[sakila]>showprofiles; 

+----------+------------+--------------------------------+ 

|Query_ID|Duration  |Query                         | 

+----------+------------+--------------------------------+ 

|       1|0.00253600|showvariableslike'%profil%'| 

|       2|0.00138150|selectcount(*)fromcustomer | 

+----------+------------+--------------------------------+ 

2rowsinset,1warning(0.01sec) 

 

--我们看到有2个warning，之前一个，现在一个 

root@localhost[sakila]>showwarnings;   --下面的结果表明SHOWPROFILES将来会被PerformanceSchema替换掉 

+---------+------+--------------------------------------------------------------------------------------------------------------+ 

|Level  |Code|Message                                                                                                     | 

+---------+------+--------------------------------------------------------------------------------------------------------------+ 

|Warning|1287|'SHOWPROFILES'isdeprecatedandwillberemovedinafuturerelease.PleaseusePerformanceSchemainstead| 

+---------+------+--------------------------------------------------------------------------------------------------------------+

3、获取SQL语句的开销信息


--可以直接使用showprofile来查看上一条SQL语句的开销信息 

--注，showprofile之类的语句不会被profiling，即自身不会产生Profiling 

--我们下面的这个showprofile查看的是showwarnings产生的相应开销 

root@localhost[sakila]>showprofile;   

+----------------+----------+ 

|Status        |Duration| 

+----------------+----------+ 

|starting      |0.000141| 

|queryend     |0.000058| 

|closingtables|0.000014| 

|freeingitems |0.001802| 

|cleaningup   |0.000272| 

+----------------+----------+ 

 

--如下面的查询showwarnings被添加到profiles 

root@localhost[sakila]>showprofiles; 

+----------+------------+--------------------------------+ 

|Query_ID|Duration  |Query                         | 

+----------+------------+--------------------------------+ 

|       1|0.00253600|showvariableslike'%profil%'| 

|       2|0.00138150|selectcount(*)fromcustomer | 

|       3|0.00228600|showwarnings                 | 

+----------+------------+--------------------------------+ 

 

--获取指定查询的开销 

root@localhost[sakila]>showprofileforquery2; 

+----------------------+----------+ 

|Status              |Duration| 

+----------------------+----------+ 

|starting            |0.000148| 

|checkingpermissions|0.000014| 

|Openingtables      |0.000047| 

|init                |0.000023| 

|Systemlock         |0.000035| 

|optimizing          |0.000012| 

|statistics          |0.000019| 

|preparing           |0.000014| 

|executing           |0.000006| 

|Sendingdata        |0.000990| 

|end                 |0.000010| 

|queryend           |0.000011| 

|closingtables      |0.000010| 

|freeingitems       |0.000016| 

|cleaningup         |0.000029| 

+----------------------+----------+ 

 

--查看特定部分的开销，如下为CPU部分的开销 

root@localhost[sakila]>showprofilecpuforquery2; 

+----------------------+----------+----------+------------+ 

|Status              |Duration|CPU_user|CPU_system| 

+----------------------+----------+----------+------------+ 

|starting            |0.000148|0.000000|  0.000000| 

|checkingpermissions|0.000014|0.000000|  0.000000| 

|Openingtables      |0.000047|0.000000|  0.000000| 

|init                |0.000023|0.000000|  0.000000| 

|Systemlock         |0.000035|0.000000|  0.000000| 

|optimizing          |0.000012|0.000000|  0.000000| 

|statistics          |0.000019|0.000000|  0.000000| 

|preparing           |0.000014|0.000000|  0.000000| 

|executing           |0.000006|0.000000|  0.000000| 

|Sendingdata        |0.000990|0.001000|  0.000000| 

|end                 |0.000010|0.000000|  0.000000| 

|queryend           |0.000011|0.000000|  0.000000| 

|closingtables      |0.000010|0.000000|  0.000000| 

|freeingitems       |0.000016|0.000000|  0.000000| 

|cleaningup         |0.000029|0.000000|  0.000000| 

+----------------------+----------+----------+------------+ 

 

--如下为MEMORY部分的开销 

root@localhost[sakila]>showprofilememoryforquery2; 

+----------------------+----------+ 

|Status              |Duration| 

+----------------------+----------+ 

|starting            |0.000148| 

|checkingpermissions|0.000014| 

|Openingtables      |0.000047| 

|init                |0.000023| 

|Systemlock         |0.000035| 

|optimizing          |0.000012| 

|statistics          |0.000019| 

|preparing           |0.000014| 

|executing           |0.000006| 

|Sendingdata        |0.000990| 

|end                 |0.000010| 

|queryend           |0.000011| 

|closingtables      |0.000010| 

|freeingitems       |0.000016| 

|cleaningup         |0.000029| 

+----------------------+----------+ 

 

--同时查看不同资源开销 

root@localhost[sakila]>showprofileblockio,cpuforquery2; 

+----------------------+----------+----------+------------+--------------+---------------+ 

|Status              |Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out| 

+----------------------+----------+----------+------------+--------------+---------------+ 

|starting            |0.000148|0.000000|  0.000000|           0|            0| 

|checkingpermissions|0.000014|0.000000|  0.000000|           0|            0| 

|Openingtables      |0.000047|0.000000|  0.000000|           0|            0| 

|init                |0.000023|0.000000|  0.000000|           0|            0| 

|Systemlock         |0.000035|0.000000|  0.000000|           0|            0| 

|optimizing          |0.000012|0.000000|  0.000000|           0|            0| 

|statistics          |0.000019|0.000000|  0.000000|           0|            0| 

|preparing           |0.000014|0.000000|  0.000000|           0|            0| 

|executing           |0.000006|0.000000|  0.000000|           0|            0| 

|Sendingdata        |0.000990|0.001000|  0.000000|           0|            0| 

|end                 |0.000010|0.000000|  0.000000|           0|            0| 

|queryend           |0.000011|0.000000|  0.000000|           0|            0| 

|closingtables      |0.000010|0.000000|  0.000000|           0|            0| 

|freeingitems       |0.000016|0.000000|  0.000000|           0|            0| 

|cleaningup         |0.000029|0.000000|  0.000000|           0|            0| 

+----------------------+----------+----------+------------+--------------+---------------+ 

 

 

--下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列 

root@localhost[sakila]>set@query_id=2; 

 

root@localhost[sakila]>SELECTSTATE,SUM(DURATION)ASTotal_R, 

   ->  ROUND( 

   ->       100*SUM(DURATION)/ 

   ->          (SELECTSUM(DURATION) 

   ->           FROMINFORMATION_SCHEMA.PROFILING 

   ->           WHEREQUERY_ID=@query_id 

   ->       ),2)ASPct_R, 

   ->    COUNT(*)ASCalls, 

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

   -> FROMINFORMATION_SCHEMA.PROFILING 

   -> WHEREQUERY_ID=@query_id 

   -> GROUPBYSTATE 

   -> ORDERBYTotal_RDESC; 

+----------------------+----------+-------+-------+--------------+ 

|STATE               |Total_R |Pct_R|Calls|R/Call      | 

+----------------------+----------+-------+-------+--------------+ 

|Sendingdata        |0.000990|71.53|    1|0.0009900000|--最大耗用时间部分为发送数据 

|starting            |0.000148|10.69|    1|0.0001480000| 

|Openingtables      |0.000047| 3.40|    1|0.0000470000| 

|Systemlock         |0.000035| 2.53|    1|0.0000350000| 

|cleaningup         |0.000029| 2.10|    1|0.0000290000| 

|init                |0.000023| 1.66|    1|0.0000230000| 

|statistics          |0.000019| 1.37|    1|0.0000190000| 

|freeingitems       |0.000016| 1.16|    1|0.0000160000| 

|preparing           |0.000014| 1.01|    1|0.0000140000| 

|checkingpermissions|0.000014| 1.01|    1|0.0000140000| 

|optimizing          |0.000012| 0.87|    1|0.0000120000| 

|queryend           |0.000011| 0.79|    1|0.0000110000| 

|end                 |0.000010| 0.72|    1|0.0000100000| 

|closingtables      |0.000010| 0.72|    1|0.0000100000| 

|executing           |0.000006| 0.43|    1|0.0000060000| 

+----------------------+----------+-------+-------+--------------+ 

 

--开启profiling后，我们可以通过showprofile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表 

--如下面的查询，部分信息省略 

profiling 

root@localhost[information_schema]>select*fromprofilinglimit3,3\G; 

***************************1.row*************************** 

          QUERY_ID:1 

               SEQ:5 

             STATE:init 

          DURATION:0.000020 

          CPU_USER:0.000000 

        CPU_SYSTEM:0.000000 

 CONTEXT_VOLUNTARY:0 

CONTEXT_INVOLUNTARY:0 

      BLOCK_OPS_IN:0 

     BLOCK_OPS_OUT:0 

     MESSAGES_SENT:0 

 MESSAGES_RECEIVED:0 

 PAGE_FAULTS_MAJOR:0 

 PAGE_FAULTS_MINOR:0 

             SWAPS:0 

   SOURCE_FUNCTION:mysql_prepare_select 

       SOURCE_FILE:sql_select.cc 

       SOURCE_LINE:1050 

 

--停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭 

root@localhost[sakila]>setprofiling=off; 

QueryOK,0rowsaffected,1warning(0.00sec)

MySQL性能分析工具profile使用教程

热门推荐

随机推荐