PROFILE 可以跟踪查询语句各个阶段 Time,IO,CPU,MEMORY 等资源使用情况,比较详细。所以系统一般不会记录太多。启用是全局的,所以每个连接都保持语句的资源使用情况。
查看 PROFILE 是否启用:
MySQL> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
mysql> show variables like '%profiling%';
+------------------------+-------+
| variable_name | value |
+------------------------+-------+
| have_profiling | yes |
| profiling | off |
| profiling_history_size | 15 |
+------------------------+-------+
mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ mysql> show variables like '%profiling%'; +------------------------+-------+ | variable_name | value | +------------------------+-------+ | have_profiling | yes | | profiling | off | | profiling_history_size | 15 | +------------------------+-------+have_profiling :是否可使用 profiling
profiling :是否启用
profiling_history_size : 保留最近执行的记录数量。默认15,最大100,0相当于禁用。
启用(为全局变量):
mysql> set profiling = 1;
mysql> set profiling_history_size = 10;
mysql> set profiling = 1; mysql> set profiling_history_size = 10;查看当前连接最近执行语句情况,编号越大为当前最近执行的。
mysql> show profiles;
+----------+------------+-----------------------------------------+
| query_id | duration | query |
+----------+------------+-----------------------------------------+
| 2 | 0.00705950 | show variables like '%profiling%' |
| 3 | 0.00127400 | select * from mysql.user |
| 4 | 0.00029100 | select * from mysql.user |
| 5 | 0.00040850 | select * from mysql.user limit 10 |
| 6 | 5.00128000 | select sleep(5) |
| 7 | 0.00044425 | select * from mysql.user limit 1 |
| 8 | 0.00436100 | show variables like '%profiling%' |
| 9 | 0.00047725 | select * from mysql.slow_log |
| 10 | 0.00052150 | select * from mysql.slow_log order by 1 |
| 11 | 0.00049775 | select * from mysql.slow_log order by 2 |
+----------+------------+-----------------------------------------+
mysql> show profiles; +----------+------------+-----------------------------------------+ | query_id | duration | query | +----------+------------+-----------------------------------------+ | 2 | 0.00705950 | show variables like '%profiling%' | | 3 | 0.00127400 | select * from mysql.user | | 4 | 0.00029100 | select * from mysql.user | | 5 | 0.00040850 | select * from mysql.user limit 10 | | 6 | 5.00128000 | select sleep(5) | | 7 | 0.00044425 | select * from mysql.user limit 1 | | 8 | 0.00436100 | show variables like '%profiling%' | | 9 | 0.00047725 | select * from mysql.slow_log | | 10 | 0.00052150 | select * from mysql.slow_log order by 1 | | 11 | 0.00049775 | select * from mysql.slow_log order by 2 | +----------+------------+-----------------------------------------+
查看以上查询开销:SHOW PROFILE Syntax
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS