MySQL Query Profile 介绍
MySQL 5.0.37 以上开始支持 MySQL Query Profiler, 可以查询到此 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 System lock, Table lock 花多少时间等等.
启用 MySQL Query Profile
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
测试查询语句
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row in set (0.00 sec)
查看 Profiles
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 0 | 0.00007300 | set profiling=1 |
| 1 | 0.00048366 | select count(*) from user |
+----------+------------+-----------------------------------------------+
2 rows in set (0.00 sec)
查看单条 Profile 详情
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000062 |
| checking permissions | 0.000011 |
| Opening tables | 0.000023 |
| After opening tables | 0.000023 |
| System lock | 0.000007 |
| Table lock | 0.000005 |
| After table lock | 0.000009 |
| init | 0.000028 |
| optimizing | 0.000015 |
| statistics | 0.000033 |
| preparing | 0.000015 |
| executing | 0.000007 |
| Sending data | 0.000143 |
| end | 0.000009 |
| query end | 0.000008 |
| closing tables | 0.000012 |
| freeing items | 0.000007 |
| updating status | 0.000056 |
| cleaning up | 0.000010 |
+----------------------+----------+
19 rows in set (0.00 sec)
查看 CPU 使用信息
mysql> show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000062 | 0.000033 | 0.000027 |
| checking permissions | 0.000011 | 0.000007 | 0.000005 |
| Opening tables | 0.000023 | 0.000013 | 0.000010 |
| After opening tables | 0.000023 | 0.000012 | 0.000011 |
| System lock | 0.000007 | 0.000004 | 0.000003 |
| Table lock | 0.000005 | 0.000003 | 0.000002 |
| After table lock | 0.000009 | 0.000005 | 0.000004 |
| init | 0.000028 | 0.000016 | 0.000013 |
| optimizing | 0.000015 | 0.000008 | 0.000006 |
| statistics | 0.000033 | 0.000019 | 0.000015 |
| preparing | 0.000015 | 0.000008 | 0.000006 |
| executing | 0.000007 | 0.000004 | 0.000003 |
| Sending data | 0.000143 | 0.000079 | 0.000064 |
| end | 0.000009 | 0.000005 | 0.000004 |
| query end | 0.000008 | 0.000005 | 0.000003 |
| closing tables | 0.000012 | 0.000006 | 0.000005 |
| freeing items | 0.000007 | 0.000004 | 0.000004 |
| updating status | 0.000056 | 0.000050 | 0.000039 |
| cleaning up | 0.000010 | 0.000005 | 0.000005 |
+----------------------+----------+----------+------------+
19 rows in set (0.00 sec)
除了CPU, 还可以下述属性查看不同的信息:
- all 显示所有 Profile 信息
- block io 显示数据快操作统计信息
- ipc 显示发送和接收的消息的计数
- memory 显示内存使用统计信息
- page faults 显示主要和次要页面错误的计数
- source 显示源代码中的函数名称,以及函数发生的文件的名称和行号
- swaps 显示swap的统计信息
查看默认 Profile 记录条数
mysql > show variables where variable_name='profiling_history_size'; # 默认15条
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling_history_size | 15 |
+------------------------+-------+
1 row in set (0.00 sec)
关闭 MySQL Query Profile
mysql > set profiling=0;
Query OK, 0 rows affected (0.00 sec)