MySQL性能优化之MySQL show profile使用概述

数据与人 2020-12-15

1080

MySQL性能优化之MySQL show profile使用概述

前言：

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。

该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别仅影响当前session。

该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。

根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。本文描述了如何使用MySQL profile，不涉及具体的样例分析。

如何查看执行SQL的耗时的步骤：

开启profile、发送sql、查看profile的资源开销结果、关闭profile。

1> 默认状态下profile是OFF的

mysql> select version();
mysql> show profiles;
Empty set (0.02 sec)
mysql> show variables like "%pro%";
可以看到profiling 默认是OFF的。
复制

2> 开启profile，然后测试

开启profile

mysql> set profiling=1;
复制

获取profile的帮助

root@localhost[sakila]> help profile; 
Name: 'SHOW PROFILE' 
Description: 
Syntax: 
SHOW PROFILE [type [, type] ... ] 
[FOR QUERY n] 
[LIMIT row_count [OFFSET offset]] 
复制

type: 
ALL --显示所有的开销信息 
BLOCK IO --显示块IO相关开销 
CONTEXT SWITCHES --上下文切换相关开销 
CPU --显示CPU相关开销信息 
IPC --显示发送和接收相关开销信息 
MEMORY --显示内存相关开销信息 
PAGE FAULTS --显示页面错误相关开销信息 
SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息 
SWAPS --显示交换次数相关开销的信息
复制

3> 执行业务SQL，并用profile分析示例

发布SQL查询

root@localhost[sakila]> select count(*) from customer; 
+----------+ 
| count(*) | 
+----------+ 
| 599 | 
+----------+ 
复制

查看当前session所有已产生的profile

root@localhost[sakila]> show profiles; 
+----------+------------+--------------------------------+ 
| Query_ID | Duration | Query | 
+----------+------------+--------------------------------+ 
| 1 | 0.00253600 | show variables like '%profil%' | 
| 2 | 0.00138150 | select count(*) from customer | 
+----------+------------+--------------------------------+ 
2 rows in set, 1 warning (0.01 sec) 
复制

我们看到有2个warning，之前一个，现在一个

root@localhost[sakila]> show warnings; --下面的结果表明SHOW PROFILES将来会被Performance Schema替换掉 
+---------+------+--------------------------------------------------------------------------------------------------------------+ 
| Level | Code | Message | 
+---------+------+--------------------------------------------------------------------------------------------------------------+ 
| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead | 
+---------+------+--------------------------------------------------------------------------------------------------------------+
复制

获取SQL语句的开销信息

可以直接使用show profile来查看上一条SQL语句的开销信息

注:show profile之类的语句不会被profiling，即自身不会产生Profiling

我们下面的这个show profile查看的是show warnings产生的相应开销

root@localhost[sakila]> show profile; 
+----------------+----------+ 
| Status         | Duration | 
+----------------+----------+ 
| starting       | 0.000141 | 
| query end      | 0.000058 | 
| closing tables | 0.000014 | 
| freeing items  | 0.001802 | 
| cleaning up    | 0.000272 | 
+----------------+----------+ 
复制

如下面的查询show warnings被添加到profiles

root@localhost[sakila]> show profiles; 
+----------+------------+--------------------------------+ 
| Query_ID | Duration | Query | 
+----------+------------+--------------------------------+ 
| 1 | 0.00253600 | show variables like '%profil%' | 
| 2 | 0.00138150 | select count(*) from customer | 
| 3 | 0.00228600 | show warnings | 
+----------+------------+--------------------------------+ 
复制

获取指定查询的开销（第二条查询的开销明细）

root@localhost[sakila]> show profile for query 2; 
+----------------------+----------+ 
| Status                | Duration | 
+----------------------+----------+ 
| starting              | 0.000148 | 
| checking permissions  | 0.000014 | 
| Opening tables        | 0.000047 | 
| init                  | 0.000023 | 
| System lock           | 0.000035 | 
| optimizing            | 0.000012 | 
| statistics            | 0.000019 | 
| preparing             | 0.000014 | 
| executing             | 0.000006 | 
| Sending data          | 0.000990 | 
| end                   | 0.000010 | 
| query end             | 0.000011 | 
| closing tables        | 0.000010 | 
| freeing items         | 0.000016 | 
| cleaning up           | 0.000029 | 
+----------------------+----------+ 
复制

查看特定部分的开销，如下为CPU部分的开销

root@localhost[sakila]> show profile cpu for query 2 ; 
+----------------------+----------+----------+------------+ 
| Status                | Duration | CPU_user | CPU_system | 
+----------------------+----------+----------+------------+ 
| starting              | 0.000148 | 0.000000 | 0.000000 | 
| checking permissions  | 0.000014 | 0.000000 | 0.000000 | 
| Opening tables        | 0.000047 | 0.000000 | 0.000000 | 
| init                  | 0.000023 | 0.000000 | 0.000000 | 
| System lock           | 0.000035 | 0.000000 | 0.000000 | 
| optimizing            | 0.000012 | 0.000000 | 0.000000 | 
| statistics            | 0.000019 | 0.000000 | 0.000000 | 
| preparing             | 0.000014 | 0.000000 | 0.000000 | 
| executing             | 0.000006 | 0.000000 | 0.000000 | 
| Sending data          | 0.000990 | 0.001000 | 0.000000 | 
| end                   | 0.000010 | 0.000000 | 0.000000 | 
| query end             | 0.000011 | 0.000000 | 0.000000 | 
| closing tables        | 0.000010 | 0.000000 | 0.000000 | 
| freeing items         | 0.000016 | 0.000000 | 0.000000 | 
| cleaning up           | 0.000029 | 0.000000 | 0.000000 | 
+----------------------+----------+----------+------------+ 
复制

如下为MEMORY部分的开销

root@localhost[sakila]> show profile memory for query 2 ; 
+----------------------+----------+ 
| Status               | Duration | 
+----------------------+----------+ 
| starting             | 0.000148 | 
| checking permissions | 0.000014 | 
| Opening tables       | 0.000047 | 
| init                 | 0.000023 | 
| System lock          | 0.000035 | 
| optimizing           | 0.000012 | 
| statistics           | 0.000019 | 
| preparing            | 0.000014 | 
| executing            | 0.000006 | 
| Sending data         | 0.000990 | 
| end                  | 0.000010 | 
| query end            | 0.000011 | 
| closing tables       | 0.000010 | 
| freeing items        | 0.000016 | 
| cleaning up          | 0.000029 | 
+----------------------+----------+ 
复制

同时查看不同资源开销

root@localhost[sakila]> show profile block io,cpu for query 2; 
+----------------------+----------+----------+------------+--------------+---------------+ 
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | 
+----------------------+----------+----------+------------+--------------+---------------+ 
| starting             | 0.000148 | 0.000000 | 0.000000 | 0 | 0 | 
| checking permissions | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 
| Opening tables       | 0.000047 | 0.000000 | 0.000000 | 0 | 0 | 
| init                 | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 
| System lock          | 0.000035 | 0.000000 | 0.000000 | 0 | 0 | 
| optimizing           | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 
| statistics           | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 
| preparing            | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 
| executing            | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | 
| Sending data         | 0.000990 | 0.001000 | 0.000000 | 0 | 0 | 
| end                  | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | 
| query end            | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 
| closing tables       | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | 
| freeing items        | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | 
| cleaning up          | 0.000029 | 0.000000 | 0.000000 | 0 | 0 | 
+----------------------+----------+----------+------------+--------------+---------------+
复制

下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列

root@localhost[sakila]> set @query_id=2; 
root@localhost[sakila]> SELECT STATE, SUM(DURATION) AS Total_R, 
ROUND( 
100 * SUM(DURATION)  
(SELECT SUM(DURATION) 
FROM INFORMATION_SCHEMA.PROFILING 
WHERE QUERY_ID = @query_id 
), 2) AS Pct_R, 
COUNT(*) AS Calls, 
SUM(DURATION)  COUNT(*) AS "R/Call" 
FROM INFORMATION_SCHEMA.PROFILING 
WHERE QUERY_ID = @query_id 
GROUP BY STATE 
ORDER BY Total_R DESC; 
+----------------------+----------+-------+-------+--------------+ 
| STATE                | Total_R | Pct_R | Calls | R/Call | 
+----------------------+----------+-------+-------+--------------+ 
| Sending data         | 0.000990 | 71.53 | 1 | 0.0009900000 |--最大耗用时间部分为发送数据 
| starting             | 0.000148 | 10.69 | 1 | 0.0001480000 | 
| Opening tables       | 0.000047 | 3.40 | 1 | 0.0000470000 | 
| System lock          | 0.000035 | 2.53 | 1 | 0.0000350000 | 
| cleaning up          | 0.000029 | 2.10 | 1 | 0.0000290000 | 
| init                 | 0.000023 | 1.66 | 1 | 0.0000230000 | 
| statistics           | 0.000019 | 1.37 | 1 | 0.0000190000 | 
| freeing items        | 0.000016 | 1.16 | 1 | 0.0000160000 | 
| preparing            | 0.000014 | 1.01 | 1 | 0.0000140000 | 
| checking permissions | 0.000014 | 1.01 | 1 | 0.0000140000 | 
| optimizing           | 0.000012 | 0.87 | 1 | 0.0000120000 | 
| query end            | 0.000011 | 0.79 | 1 | 0.0000110000 | 
| end                  | 0.000010 | 0.72 | 1 | 0.0000100000 | 
| closing tables       | 0.000010 | 0.72 | 1 | 0.0000100000 | 
| executing            | 0.000006 | 0.43 | 1 | 0.0000060000 | 
+----------------------+----------+-------+-------+--------------+
复制

开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表

如下面的查询，部分信息省略

profiling 
root@localhost[information_schema]> select * from profiling limit 3,3\G; 
*************************** 1. row *************************** 
QUERY_ID: 1 
SEQ: 5 
STATE: init 
DURATION: 0.000020 
CPU_USER: 0.000000 
CPU_SYSTEM: 0.000000 
CONTEXT_VOLUNTARY: 0 
CONTEXT_INVOLUNTARY: 0 
BLOCK_OPS_IN: 0 
BLOCK_OPS_OUT: 0 
MESSAGES_SENT: 0 
MESSAGES_RECEIVED: 0 
PAGE_FAULTS_MAJOR: 0 
PAGE_FAULTS_MINOR: 0 
SWAPS: 0 
SOURCE_FUNCTION: mysql_prepare_select 
SOURCE_FILE: sql_select.cc 
SOURCE_LINE: 1050
复制

停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭

root@localhost[sakila]> set profiling=off; 
Query OK, 0 rows affected, 1 warning (0.00 sec)
复制

mysql

文章转载自数据与人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL性能优化之MySQL show profile使用概述

评论