MySQL性能分析工具profiling
MySQL数据库中，可以通过配置profiling参数来启用SQL性能分析，找出当前SQL瓶颈，进而优化、调整sql语句。
profiling参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，IPC，CPU，Memory等等。
用法：
mysql> help profile
Name:'SHOW PROFILE'
Description:
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
Profiling变量默认值为0（off），可以通过设置profiling为0或ON开启
mysql>select @@profiling;
+-------------+
|@@profiling |
+-------------+
| 0 |
+-------------+
1 row inset (0.03 sec)
mysql>show variables like '%profil%';
+------------------------+-------+
|Variable_name | Value |
+------------------------+-------+
|profiling | OFF |
|profiling_history_size | 15 |
+------------------------+-------+
2 rows inset (0.00 sec)
profiling：设置是否开启SQL性能剖析
profiling_history_size：设置是SQL性能剖析缓存的记录数
mysql>set profiling=1;
Query OK,0 rows affected (0.03 sec)
mysql>show variables like '%profil%';
+------------------------+-------+
|Variable_name | Value |
+------------------------+-------+
|profiling | ON |
|profiling_history_size | 15 |
+------------------------+-------+
2 rows inset (0.00 sec)
mysql>show variables like 'profiling_history_size';
+------------------------+-------+
|Variable_name | Value |
+------------------------+-------+
|profiling_history_size | 15 |
+------------------------+-------+
1 row inset (0.00 sec)
show profiles:返回一个列表,显示最近发送到服务器上执行的语句的资源使用情况.
显示的记录数由变量“profiling_history_size”控制,默认15条，最大值为100，可以手动设置该参数值。
mysql>set profiling_history_size = 30;
Query OK,0 rows affected (0.00 sec)
mysql>show variables like '%profil%';
+------------------------+-------+
|Variable_name | Value |
+------------------------+-------+
|profiling | ON&
654
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
