MySQL 的 Query Profiler使用
MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条 Query在整个执行过程中多种资源的消耗情况，如 CPU、IO、IPC、SWAP等，以及发生的 PAGE FAULTS、CONTEXT SWITCHE等，同时还能得到该Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。下面看看 Query Profiler 的具体用法。首先可以查看目前mysql中profiler是否开启：
[*]mysql> SELECT @@profiling;
[*]+-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec)
开启profiler：
[*]mysql> SET profiling = 1;
[*] Query OK, 0 rows affected (0.00 sec)
在开启 Query Profiler 功能之后，MySQL 就会自动记录所有执行的 Query 的profile 信息。下面执行 Query：
[*]mysql> select count(*) from order_items;
[*]+----------+
[*]| count(*) |
[*]+----------+
[*]| 154258 |
[*]+----------+
[*]1 row in set (0.62 sec)
[*]
[*]mysql> show profiles;
[*]+----------+------------+------------------------------------+
[*]| Query_ID | Duration | Query |
[*]+----------+------------+------------------------------------+
[*]| 1 | 0.04020500 | select * from orders where id=2090 |
[*]| 2 | 0.02056800 | select count(*) from t1 |
[*]| 3 | 0.00059800 | select count(*) from t1 |
[*]| 4 | 0.00036700 | ser profiler=0 |
[*]| 5 | 0.00053300 | select @@profiling |
[*]| 6 | 0.62734100 | select count(*) from order_items |
[*]+----------+------------+------------------------------------+
[*]6 rows in set (0.00 sec)
获取概要信息之后，就可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中详细的 profile 信息了，如果要查看CPU和IO消耗，具体操作如下：
[*]mysql> show profile cpu, block io for query 6;
[*]+----------------------+----------+----------+------------+--------------+---------------+
[*]| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
[*]+----------------------+----------+----------+------------+--------------+---------------+
[*]| starting | 0.000064 | 0.000000 | 0.000000 | 0 | 0 |
[*]| checking permissions | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
[*]| Opening tables | 0.390653 | 0.000000 | 0.000000 | 0 | 0 |
[*]| System lock | 0.000028 | 0.000000 | 0.000000 | 0 | 0 |
[*]| init | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
[*]| optimizing | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
[*]| statistics | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
[*]| preparing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
[*]| executing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
[*]| Sending data | 0.235932 | 0.036002 | 0.000000 | 0 | 0 |
[*]| end | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
[*]| query end | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
[*]| closing tables | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
[*]| freeing items | 0.000500 | 0.000000 | 0.000000 | 0 | 0 |
[*]| logging slow query | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
[*]| cleaning up | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
[*]+----------------------+----------+----------+------------+--------------+---------------+
[*]16 rows in set (0.03 sec)
下面列出了show profile的参数：
[*]SHOW PROFILE ... ]
[*]
[*] ]
[*]type:
[*] ALL
[*]| BLOCK IO
[*]| CONTEXT SWITCHES
[*]| CPU
[*]| IPC
[*]| MEMORY
[*]| PAGE FAULTS
[*]| SOURCE
[*]| SWAPS
Optional type values may be specifiedto display specific additional types of information:
[*] ALL displays all information
[*] BLOCK IO displays counts for block input andoutput operations
[*] CONTEXT SWITCHES displays counts for voluntary andinvoluntary 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 minorpage faults
[*] SOURCE displays the names of functions from thesource code, together with the name and line number of the file in which thefunction occurs
[*] SWAPS displays swap counts
注意：Profiling只是针对单个session的，如果session结束，profiling信息将丢失！
可以在 INFORMATION_SCHEMA中的profiling表中获取profiling信息，
下面的两句输出结果是一致的：
[*]mysql>SHOW PROFILE FOR QUERY 6;
[*]
[*]mysql>SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 6 ORDER BY SEQ;
页:
[1]