• 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html
  • 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html

mysql语句性能开销检测profiling详解

技术杂谈 勤劳的小蚂蚁 3个月前 (01-12) 79次浏览 已收录 0个评论 扫描二维码

之前我介绍过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的定义英文也简单:

  • ALL displays all information

  • BLOCK IO displays counts for block input and output operations

  • CONTEXT SWITCHES displays counts for voluntary and involuntary context switches

  • CPU displays user and system CPU usage times

  • IPC displays counts for messages sent and received

  • MEMORY is not currently implemented

  • PAGE FAULTS displays counts for major and minor page faults

  • SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs

  • SWAPS displays swap counts

使用示例

查看有没有启用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 NameSHOW NameRemarks
QUERY_IDQuery_ID
SEQ

STATEStatus
DURATIONDuration
CPU_USERCPU_user
CPU_SYSTEMCPU_system
CONTEXT_VOLUNTARYContext_voluntary
CONTEXT_INVOLUNTARYContext_involuntary
BLOCK_OPS_INBlock_ops_in
BLOCK_OPS_OUTBlock_ops_out
MESSAGES_SENTMessages_sent
MESSAGES_RECEIVEDMessages_received
PAGE_FAULTS_MAJORPage_faults_major
PAGE_FAULTS_MINORPage_faults_minor
SWAPSSwaps
SOURCE_FUNCTIONSource_function
SOURCE_FILESource_file
SOURCE_LINESource_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



    丨极客文库, 版权所有丨如未注明 , 均为原创丨
    本网站采用知识共享署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议进行授权
    转载请注明原文链接:mysql语句性能开销检测profiling详解
    喜欢 (0)
    [247507792@qq.com]
    分享 (0)
    勤劳的小蚂蚁
    关于作者:
    温馨提示:本文来源于网络,转载文章皆标明了出处,如果您发现侵权文章,请及时向站长反馈删除。

    您必须 登录 才能发表评论!

    • 精品技术教程
    • 编程资源分享
    • 问答交流社区
    • 极客文库知识库

    客服QQ


    QQ:2248886839


    工作时间:09:00-23:00