April 16 2015

mysql-profile

查看版本
mysql> show variables like ‘version’;
+—————+————+
| Variable_name | Value      |
+—————+————+
| version       | 5.5.41-log |
+—————+————+

想使用profile功能要安装profile模块才能实现。
不过版本在5.0.37之后会把profile模块集成进去

查看是否已经启用profile,如果profilng值为0表示没有启用,如果为1表示启用
SELECT @@profiling;
可以通过SET profiling = 1;来启用session级别的profiling
mysql> show variables like ‘%profil%’;
+————————+——-+
| Variable_name          | Value |
+————————+——-+
| have_profiling         | YES   |–只读变量,用于控制是否由系统变量开启或禁用profiling
| profiling              | ON    |–开启SQL语句剖析功能
| profiling_history_size | 15    |–设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用
+————————+——-+

获取profile的帮助
mysql> 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 –显示交换次数相关开销的信息

The SHOW PROFILE and SHOW PROFILES statements display profiling
information that indicates resource usage for statements executed
during the course of the current session.

Profiling is controlled by the profiling session variable, which has a
default value of 0 (OFF). Profiling is enabled by setting profiling to
1 or ON:

–查看当前session所有已产生的profile
show profiles;
‘SHOW PROFILES’ is deprecated and will be removed in a future release. Please use Performance Schema instead
SHOW PROFILES将来会被Performance Schema替换掉

–可以直接使用show profile来查看上一条SQL语句的开销信息
–注,show profile之类的语句不会被profiling,即自身不会产生Profiling
也就是当我们先执行select count(*) from tab;再执行show profile时就分析了刚刚的select count(*)语句。
mysql> show profiles;
+———-+————+——————————–+
| Query_ID | Duration   | Query                          |
+———-+————+——————————–+
|        1 | 0.00013400 | SELECT @@profiling             |
|        2 | 0.00069725 | show tables                    |
|        3 | 0.00921850 | select count(*) from t1        |
|        4 | 0.00061925 | show variables like ‘version’  |
|        5 | 0.00035800 | show variables like ‘%profil%’ |
|        6 | 0.01802850 | help ‘profile’                 |
|        7 | 0.00056150 | show warnings                  |
+———-+————+——————————–+

获取指定查询的开销
show profile for query  3;

mysql> show profile for query  3;
+———————-+———-+
| Status               | Duration |
+———————-+———-+
| starting             | 0.005325 |
| checking permissions | 0.000059 |
| Opening tables       | 0.001979 |
| System lock          | 0.000035 |
| init                 | 0.000040 |
| optimizing           | 0.000011 |
| statistics           | 0.000030 |
| preparing            | 0.000010 |
| executing            | 0.000010 |
| Sending data         | 0.000717 |
| end                  | 0.000026 |
| query end            | 0.000006 |
| closing tables       | 0.000010 |
| freeing items        | 0.000890 |
| logging slow query   | 0.000041 |
| cleaning up          | 0.000031 |
+———————-+———-+

查看特定部分的开销,如下为CPU部分的开销
show profile cpu for query 3;
如下为MEMORY部分的开销
show profile memory for query 3;
同时查看不同资源开销
show profile block io,cpu for query 3;
下面的SQL语句用于查询query_id为3的SQL开销,且按最大耗用时间倒序排列

set @query_id=3;

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;

开启profiling后,我们可以通过show profile等方式查看,其实质是这些开销信息被记录到information_schema.profiling表
停止profile,可以设置profiling参数,或者在session退出之后,profiling会被自动关闭
set profiling=off;



Copyright 2019. All rights reserved.

Posted 2015年4月16日 by ucjmh in category "mysql

Leave a Reply

Your email address will not be published. Required fields are marked *