MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具,通过该工具可以获取一条 Query 在整个执行过程中多种资源的消耗情况,如 CPU、IO、IPC、SWAP等,以及发生的 PAGE FAULTS、CONTEXT SWITCHE等,同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。下面看看 Query Profiler 的具体用法。

  首先可以查看目前mysql中profiler是否开启:

  1. mysql> SELECT @@profiling; 
  2. +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec)

   开启profiler:

  1. mysql> SET profiling = 1; 
  2. Query OK, 0 rows affected (0.00 sec) 

    在开启 Query Profiler 功能之后,MySQL 就会自动记录所有执行的 Query 的profile 信息。下面执行 Query:

  

  1. mysql> select count(*) from order_items; 
  2. +----------+ 
  3. count(*) | 
  4. +----------+ 
  5. |   154258 | 
  6. +----------+ 
  7. 1 row in set (0.62 sec) 
  8.  
  9. mysql> show profiles; 
  10. +----------+------------+------------------------------------+ 
  11. | Query_ID | Duration   | Query                              | 
  12. +----------+------------+------------------------------------+ 
  13. |        1 | 0.04020500 | select * from orders where id=2090 | 
  14. |        2 | 0.02056800 | select count(*) from t1 | 
  15. |        3 | 0.00059800 | select count(*) from t1 | 
  16. |        4 | 0.00036700 | ser profiler=0                     | 
  17. |        5 | 0.00053300 | select @@profiling                 | 
  18. |        6 | 0.62734100 | select count(*) from order_items   | 
  19. +----------+------------+------------------------------------+ 
  20. rows in set (0.00 sec) 

获取概要信息之后,就可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中详细的 profile 信息了,如果要查看CPU和IO消耗,具体操作如下:

  1. mysql> show profile cpu, block io for query 6; 
  2. +----------------------+----------+----------+------------+--------------+---------------+ 
  3. | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | 
  4. +----------------------+----------+----------+------------+--------------+---------------+ 
  5. | starting             | 0.000064 | 0.000000 |   0.000000 |            0 |             0 | 
  6. | checking permissions | 0.000015 | 0.000000 |   0.000000 |            0 |             0 | 
  7. | Opening tables       | 0.390653 | 0.000000 |   0.000000 |            0 |             0 | 
  8. | System lock          | 0.000028 | 0.000000 |   0.000000 |            0 |             0 | 
  9. | init                 | 0.000019 | 0.000000 |   0.000000 |            0 |             0 | 
  10. | optimizing           | 0.000010 | 0.000000 |   0.000000 |            0 |             0 | 
  11. statistics           | 0.000017 | 0.000000 |   0.000000 |            0 |             0 | 
  12. | preparing            | 0.000015 | 0.000000 |   0.000000 |            0 |             0 | 
  13. | executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 | 
  14. | Sending data         | 0.235932 | 0.036002 |   0.000000 |            0 |             0 | 
  15. end                  | 0.000018 | 0.000000 |   0.000000 |            0 |             0 | 
  16. | query end            | 0.000018 | 0.000000 |   0.000000 |            0 |             0 | 
  17. | closing tables       | 0.000023 | 0.000000 |   0.000000 |            0 |             0 | 
  18. | freeing items        | 0.000500 | 0.000000 |   0.000000 |            0 |             0 | 
  19. | logging slow query   | 0.000009 | 0.000000 |   0.000000 |            0 |             0 | 
  20. | cleaning up          | 0.000009 | 0.000000 |   0.000000 |            0 |             0 | 
  21. +----------------------+----------+----------+------------+--------------+---------------+ 
  22. 16 rows in set (0.03 sec) 

下面列出了show profile的参数:

 

  1. SHOW PROFILE [type [, type] ... ] 
  2.     [FOR QUERY n] 
  3.     [LIMIT row_count [OFFSET offset]] 
  4. type: 
  5.     ALL 
  6.   | BLOCK IO 
  7.   | CONTEXT SWITCHES 
  8.   | CPU 
  9.   | IPC 
  10.   | MEMORY 
  11.   | PAGE FAULTS 
  12.   | SOURCE 
  13.   | SWAPS 

Optional type values may be specified to display specific additional types of information:

  • 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只是针对单个session的,如果session结束,profiling信息将丢失!

可以在 INFORMATION_SCHEMA中的profiling表中获取profiling信息,

下面的两句输出结果是一致的:

  1. mysql>SHOW PROFILE FOR QUERY 6; 
  2.  
  3. mysql>SELECT STATE, FORMAT(DURATION, 6) AS DURATION 
    FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 6 ORDER BY SEQ;