之前我介绍过msyql查询优化explain检查命令的使用,explain主要是检查sql语句的基本性能,sql是否优秀,但不能查看具体的涉及硬件资源的开销,今天要介绍的这个profiling工具可以更细节的查看资源的开销,比较详细。
首先这款性能检查工具是针对每个session生效的,session结束了就要重要发起查询检测。
默认是关闭的,需要手动开启:
SET profiling = 1;
开启之后,发往mysql服务器的语句可以通过SHOW PROFILES显示出来,默认显示15条,最大设置为100,通过设置变量profiling_history_size实现,设置为0将会禁用profiling。
语法 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
关于type的定义英文也简单:
使用示例
查看有没有启用profiling
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
开启profiling
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
运行要分析的SQL语句
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)
检查所有抓取到的分析语句性能指标
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query |
+----------+----------+--------------------------+
| 0 | 0.000088 | SET PROFILING = 1 |
| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |
| 2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)
显示单个分析语句性能指标,指最近执行次数最多的那一条
mysql> SHOW PROFILE;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table | 0.000056 |
| After create | 0.011363 |
| query end | 0.000375 |
| freeing items | 0.000089 |
| logging slow query | 0.000019 |
| cleaning up | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)
具体查看某条分析语句的性能
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| query end | 0.000107 |
| freeing items | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)
你也可以查看CPU或者其他资源消耗信息
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 | 0.000002 |
| creating table | 0.000056 | 0.000028 | 0.000028 |
| After create | 0.011363 | 0.000217 | 0.001571 |
| query end | 0.000375 | 0.000013 | 0.000028 |
| freeing items | 0.000089 | 0.000010 | 0.000014 |
| logging slow query | 0.000019 | 0.000009 | 0.000010 |
| cleaning up | 0.000005 | 0.000003 | 0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)
其他使用方式
也可以通过查表的方式查看分析语句的性能,所有show能看到的都会记录在INFORMATION_SCHEMA表中,比如:
SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ;
SHOW与INFORMATION_SCHEMA对应关系表:
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
---|---|---|
QUERY_ID | Query_ID | |
SEQ | ||
STATE | Status | |
DURATION | Duration | |
CPU_USER | CPU_user | |
CPU_SYSTEM | CPU_system | |
CONTEXT_VOLUNTARY | Context_voluntary | |
CONTEXT_INVOLUNTARY | Context_involuntary | |
BLOCK_OPS_IN | Block_ops_in | |
BLOCK_OPS_OUT | Block_ops_out | |
MESSAGES_SENT | Messages_sent | |
MESSAGES_RECEIVED | Messages_received | |
PAGE_FAULTS_MAJOR | Page_faults_major | |
PAGE_FAULTS_MINOR | Page_faults_minor | |
SWAPS | Swaps | |
SOURCE_FUNCTION | Source_function | |
SOURCE_FILE | Source_file | |
SOURCE_LINE | Source_line |
注意
INFORMATION_SCHEMA这个表的使用方式已经在mysql5.7.2已经标记废除了,在未来的版本将会彻底删除掉,SHOW的使用方式在未来的版本也会替代掉,替代使用方式为MySQL Performance Schema,具体的参考官网的使用:https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html 以上profiling所有介绍翻译来源于官网,原版可以参考:https://dev.mysql.com/doc/refman/5.7/en/show-profile.html