MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具,通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况,
如 CPU,IO,IPC,SWAP 等,以及发生的 PAGE FAULTS,CONTEXT SWITCHE 等等,同时还能得到该 Query 执行过程中 MySQL 所调用
的各个函数在源文件中的位置。
1、开启profile 参数
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
2)获取系统中保存的所有 Query 的 profile 概要信息
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------+
| 1 | 0.00081200 | show tables |
| 2 | 0.08324000 | select * from test.shop |
| 3 | 0.03196700 | select * from test.pet |
| 4 | 0.04903500 | select * from SESSION_VARIABLES |
+----------+------------+---------------------------------+
4 rows in set, 1 warning (0.00 sec)
3)针对单个 Query 获取详细的 profile 信息。
mysql> show profile cpu,block io for query 4;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000055 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000045 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.048090 | 0.004000 | 0.001000 | 0 | 0 |
| Sending data | 0.000564 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000128 | 0.000999 | 0.000000 | 0 | 0 |
| closing tables | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000036 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000022 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.05 sec)
SHOW PROFILE Syntax
SHOW PROFILE [相关的几个参数:type
[,type
] ... ] [FOR QUERYn
] [LIMITrow_count
[OFFSEToffset
]]type
: ALL | BLOCK IO | CONTEXT SWITCHES (CPU从一个线程切换到另外一个线程,需要保存当前任务的运行环境,恢复将要运行任务的运行环境,必然带来性能 消耗) | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE 显示源代码中的函数的名称,连同函数所在文件的行号和名字 | SWAPS
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES | 该版本支持profile 功能
| profiling | ON | 开启 profile 功能
| profiling_history_size | 15 | 保留profile statement 的数量,最大100,如果设置为0 ,相当于关闭profile 功能
+------------------------+-------+