Mysql profile使用

一、使用profile

①是否有这个功能

(root@localhost) [test]> show variables like '%have_pr%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| have_profiling | YES   |
+----------------+-------+
1 row in set (0.00 sec)

② 使用

1、 set profiling = 1;

2、 运行要分析的sql, select count(*) from x 

3、  show profiles;

(root@localhost) [test]> show profiles;
+----------+------------+--------------------------------------+
| Query_ID | Duration   | Query                                |
+----------+------------+--------------------------------------+
|        1 | 0.00225225 | select * from x limit 1              |
|        2 | 0.77424175 | select count(*) from x               |
|        3 | 0.00005175 | show profiles all for query 2        |
|        4 | 0.00153200 | show variables like 'profile%'       |
|        5 | 0.00095200 | show variables like '%profile%'      |
|        6 | 0.00095625 | show variables like '%have_profile%' |
|        7 | 0.00102475 | show variables like '%have_pr%'      |
|        8 | 0.00011500 | set profiling = 1                    |
+----------+------------+--------------------------------------+
8 rows in set, 1 warning (0.00 sec)

4、 show profile for query Query_ID

Duration 持续时间

(root@localhost) [test]> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000067 |  开始
| checking permissions | 0.000005 |  检查权限
| Opening tables       | 0.000017 |  打开表
| init                 | 0.001438 |  初始化
| System lock          | 0.000026 |  系统锁
| optimizing           | 0.000395 |  优化
| statistics           | 0.000011 |  统计
| preparing            | 0.000366 |  准备
| executing            | 0.000003 |  执行
| Sending data         | 0.771849 |  发送数据
| end                  | 0.000017 |  结束
| query end            | 0.000011 |  查询结束
| closing tables       | 0.000008 |  关闭表
| freeing items        | 0.000019 |  释放物品
| cleaning up          | 0.000012 |  清理
+----------------------+----------+

5、关闭profile

set profiling = 0;

二、查看详细的profile

SHOW profile all for query 2;

 可以看到profile中的每列是哪一项占用了存储

我们一般看

SHOW profile CPU,BLOCK IO FOR query 2;

(root@localhost) [test]> SHOW profile CPU,BLOCK IO FOR query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000067 | 0.000036 |   0.000019 |            0 |             0 |
| checking permissions | 0.000005 | 0.000003 |   0.000002 |            0 |             0 |
| Opening tables       | 0.000017 | 0.000010 |   0.000006 |            0 |             0 |
| init                 | 0.001438 | 0.001441 |   0.000000 |            0 |             0 |
| System lock          | 0.000026 | 0.000023 |   0.000000 |            0 |             0 |
| optimizing           | 0.000395 | 0.000395 |   0.000000 |            0 |             0 |
| statistics           | 0.000011 | 0.000011 |   0.000000 |            0 |             0 |
| preparing            | 0.000366 | 0.000366 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| Sending data         | 0.771849 | 0.771508 |   0.000000 |            0 |             0 |
| end                  | 0.000017 | 0.000009 |   0.000000 |            0 |             0 |
| query end            | 0.000011 | 0.000010 |   0.000000 |            0 |             0 |
| closing tables       | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
| freeing items        | 0.000019 | 0.000019 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000012 | 0.000013 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

 

 "Status": "query end", 状态

 "Duration": "1.751142", 持续时间

 "CPU_user": "0.008999", cpu用户

 "CPU_system": "0.003999", cpu系统

 "Context_voluntary": "98", 上下文主动切换

 "Context_involuntary": "0", 上下文被动切换

 "Block_ops_in": "8", 阻塞的输入操作

 "Block_ops_out": "32", 阻塞的输出操作

 "Messages_sent": "0", 消息发出

 "Messages_received": "0", 消息接受

 "Page_faults_major": "0", 主分页错误

 "Page_faults_minor": "0", 次分页错误

 "Swaps": "0", 交换次数

 "Source_function": "mysql_execute_command", 源功能

 "Source_file": "sql_parse.cc", 源文件

 "Source_line": "4465" 源代码行