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

--启用session级别的profiling 
root@localhost[sakila]> set profiling=1; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
--验证修改后的结果 
root@localhost[sakila]> show variables like '%profil%'; 
+------------------------+-------+ 
| Variable_name          | Value | 
+------------------------+-------+ 
| have_profiling         | YES   | 
| profiling              | ON    | 
| profiling_history_size | 15    | 
+------------------------+-------+ 
 
--发布SQL查询 
root@localhost[sakila]> select count(*) from customer; 
+----------+ 
| count(*) | 
+----------+ 
|      599 | 
+----------+ 
 
--查看当前session所有已产生的profile 
root@localhost[sakila]> show profiles; 
+----------+------------+--------------------------------+ 
| Query_ID | Duration   | Query                          | 
+----------+------------+--------------------------------+ 
|        1 | 0.00253600 | show variables like '%profil%' | 
|        2 | 0.00138150 | select count(*) from customer  | 
+----------+------------+--------------------------------+ 
2 rows in set, 1 warning (0.01 sec) 
 
--我们看到有2个warning，之前一个，现在一个 
root@localhost[sakila]> show warnings;    --下面的结果表明SHOW PROFILES将来会被Performance Schema替换掉 
+---------+------+--------------------------------------------------------------------------------------------------------------+ 
| Level   | Code | Message                                                                                                      | 
+---------+------+--------------------------------------------------------------------------------------------------------------+ 
| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead | 
+---------+------+--------------------------------------------------------------------------------------------------------------+ 

--可以直接使用show profile来查看上一条SQL语句的开销信息 
--注，show profile之类的语句不会被profiling，即自身不会产生Profiling 
--我们下面的这个show profile查看的是show warnings产生的相应开销 
root@localhost[sakila]> show profile;   
+----------------+----------+ 
| Status         | Duration | 
+----------------+----------+ 
| starting       | 0.000141 | 
| query end      | 0.000058 | 
| closing tables | 0.000014 | 
| freeing items  | 0.001802 | 
| cleaning up    | 0.000272 | 
+----------------+----------+ 
 
--如下面的查询show warnings被添加到profiles 
root@localhost[sakila]> show profiles; 
+----------+------------+--------------------------------+ 
| Query_ID | Duration   | Query                          | 
+----------+------------+--------------------------------+ 
|        1 | 0.00253600 | show variables like '%profil%' | 
|        2 | 0.00138150 | select count(*) from customer  | 
|        3 | 0.00228600 | show warnings                  | 
+----------+------------+--------------------------------+ 
 
--获取指定查询的开销 
root@localhost[sakila]> show profile for query 2; 
+----------------------+----------+ 
| Status               | Duration | 
+----------------------+----------+ 
| starting             | 0.000148 | 
| checking permissions | 0.000014 | 
| Opening tables       | 0.000047 | 
| init                 | 0.000023 | 
| System lock          | 0.000035 | 
| optimizing           | 0.000012 | 
| statistics           | 0.000019 | 
| preparing            | 0.000014 | 
| executing            | 0.000006 | 
| Sending data         | 0.000990 | 
| end                  | 0.000010 | 
| query end            | 0.000011 | 
| closing tables       | 0.000010 | 
| freeing items        | 0.000016 | 
| cleaning up          | 0.000029 | 
+----------------------+----------+ 
 
--查看特定部分的开销，如下为CPU部分的开销 
root@localhost[sakila]> show profile cpu for query 2 ; 
+----------------------+----------+----------+------------+ 
| Status               | Duration | CPU_user | CPU_system | 
+----------------------+----------+----------+------------+ 
| starting             | 0.000148 | 0.000000 |   0.000000 | 
| checking permissions | 0.000014 | 0.000000 |   0.000000 | 
| Opening tables       | 0.000047 | 0.000000 |   0.000000 | 
| init                 | 0.000023 | 0.000000 |   0.000000 | 
| System lock          | 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 | 
| Sending data         | 0.000990 | 0.001000 |   0.000000 | 
| end                  | 0.000010 | 0.000000 |   0.000000 | 
| query end            | 0.000011 | 0.000000 |   0.000000 | 
| closing tables       | 0.000010 | 0.000000 |   0.000000 | 
| freeing items        | 0.000016 | 0.000000 |   0.000000 | 
| cleaning up          | 0.000029 | 0.000000 |   0.000000 | 
+----------------------+----------+----------+------------+ 
 
--如下为MEMORY部分的开销 
root@localhost[sakila]> show profile memory for query 2 ; 
+----------------------+----------+ 
| Status               | Duration | 
+----------------------+----------+ 
| starting             | 0.000148 | 
| checking permissions | 0.000014 | 
| Opening tables       | 0.000047 | 
| init                 | 0.000023 | 
| System lock          | 0.000035 | 
| optimizing           | 0.000012 | 
| statistics           | 0.000019 | 
| preparing            | 0.000014 | 
| executing            | 0.000006 | 
| Sending data         | 0.000990 | 
| end                  | 0.000010 | 
| query end            | 0.000011 | 
| closing tables       | 0.000010 | 
| freeing items        | 0.000016 | 
| cleaning up          | 0.000029 | 
+----------------------+----------+ 
 
--同时查看不同资源开销 
root@localhost[sakila]> show profile block io,cpu for query 2; 
+----------------------+----------+----------+------------+--------------+---------------+ 
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | 
+----------------------+----------+----------+------------+--------------+---------------+ 
| starting             | 0.000148 | 0.000000 |   0.000000 |            0 |             0 | 
| checking permissions | 0.000014 | 0.000000 |   0.000000 |            0 |             0 | 
| Opening tables       | 0.000047 | 0.000000 |   0.000000 |            0 |             0 | 
| init                 | 0.000023 | 0.000000 |   0.000000 |            0 |             0 | 
| System lock          | 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 | 
| Sending data         | 0.000990 | 0.001000 |   0.000000 |            0 |             0 | 
| end                  | 0.000010 | 0.000000 |   0.000000 |            0 |             0 | 
| query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 | 
| closing tables       | 0.000010 | 0.000000 |   0.000000 |            0 |             0 | 
| freeing items        | 0.000016 | 0.000000 |   0.000000 |            0 |             0 | 
| cleaning up          | 0.000029 | 0.000000 |   0.000000 |            0 |             0 | 
+----------------------+----------+----------+------------+--------------+---------------+ 
 
 
--下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列 
root@localhost[sakila]> set @query_id=2; 
 
root@localhost[sakila]> SELECT STATE, SUM(DURATION) AS Total_R, 
    ->   ROUND( 
    ->        100 * SUM(DURATION) / 
    ->           (SELECT SUM(DURATION) 
    ->            FROM INFORMATION_SCHEMA.PROFILING 
    ->            WHERE QUERY_ID = @query_id 
    ->        ), 2) AS Pct_R, 
    ->     COUNT(*) AS Calls, 
    ->     SUM(DURATION) / COUNT(*) AS "R/Call" 
    ->  FROM INFORMATION_SCHEMA.PROFILING 
    ->  WHERE QUERY_ID = @query_id 
    ->  GROUP BY STATE 
    ->  ORDER BY Total_R DESC; 
+----------------------+----------+-------+-------+--------------+ 
| STATE                | Total_R  | Pct_R | Calls | R/Call       | 
+----------------------+----------+-------+-------+--------------+ 
| Sending data         | 0.000990 | 71.53 |     1 | 0.0009900000 |--最大耗用时间部分为发送数据 
| starting             | 0.000148 | 10.69 |     1 | 0.0001480000 | 
| Opening tables       | 0.000047 |  3.40 |     1 | 0.0000470000 | 
| System lock          | 0.000035 |  2.53 |     1 | 0.0000350000 | 
| cleaning up          | 0.000029 |  2.10 |     1 | 0.0000290000 | 
| init                 | 0.000023 |  1.66 |     1 | 0.0000230000 | 
| statistics           | 0.000019 |  1.37 |     1 | 0.0000190000 | 
| freeing items        | 0.000016 |  1.16 |     1 | 0.0000160000 | 
| preparing            | 0.000014 |  1.01 |     1 | 0.0000140000 | 
| checking permissions | 0.000014 |  1.01 |     1 | 0.0000140000 | 
| optimizing           | 0.000012 |  0.87 |     1 | 0.0000120000 | 
| query end            | 0.000011 |  0.79 |     1 | 0.0000110000 | 
| end                  | 0.000010 |  0.72 |     1 | 0.0000100000 | 
| closing tables       | 0.000010 |  0.72 |     1 | 0.0000100000 | 
| executing            | 0.000006 |  0.43 |     1 | 0.0000060000 | 
+----------------------+----------+-------+-------+--------------+ 
 
--开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表 
--如下面的查询，部分信息省略 
profiling 
root@localhost[information_schema]> select * from profiling limit 3,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]> set profiling=off; 
Query OK, 0 rows affected, 1 warning (0.00 sec)