领购物优惠券9块9包邮
首页 > 数据库 > MySQL教程

MySQL性能分析工具profile使用教程

   2015-08-01 0
核心提示：这篇文章主要介绍了MySQL性能分析工具profile使用教程,本文描述了如何使用MySQL profile,不涉及具体的样例分析,需要的朋友可以参考下

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。本文描述了如何使用MySQL profile，不涉及具体的样例分析。

1、有关profile的描述

复制代码 代码如下:

--当前版本 
root@localhost[sakila]> show variables like 'version'; 
+---------------+---------------------------------------+ 
| Variable_name | Value                                 | 
+---------------+---------------------------------------+ 
| version       | 5.6.17-enterprise-commercial-advanced | 
+---------------+---------------------------------------+ 
 
--查看profiling系统变量 
root@localhost[sakila]> show variables like '%profil%'; 
+------------------------+-------+ 
| Variable_name          | Value | 
+------------------------+-------+ 
| have_profiling         | YES   |   --只读变量，用于控制是否由系统变量开启或禁用profiling 
| profiling              | OFF   |   --开启SQL语句剖析功能 
| profiling_history_size | 15    |   --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling 
+------------------------+-------+ 
 
profiling [539] 
If set to 0 or OFF (the default), statement profiling is disabled. If set to 1 or ON, statement prof 
is enabled and the SHOW PROFILE and SHOW PROFILES statements provide access to prof 
information. See Section 13.7.5.32, “SHOW PROFILES Syntax”. 
 
This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release. 
profiling_history_size [539] 
The number of statements for which to maintain profiling information if profiling [539] is 
enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively 
disables profiling. See Section 13.7.5.32, “SHOW PROFILES Syntax”. 
This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release. 
 
 
--获取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              --显示交换次数相关开销的信息  
 
The SHOW PROFILE and SHOW PROFILES statements display profiling 
information that indicates resource usage for statements executed 
during the course of the current session. 
 
*Note*: These statements are deprecated as of MySQL 5.6.7 and will be 
removed in a future MySQL release. Use the Performance Schema instead; 
see http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html. 
--上面描述从5.6.7开始该命令将会被移除，用Performance Schema instead代替 
--在Oracle数据库中，是通过autotrace来剖析单条SQL并获取真实的执行计划以及其开销信息 

2、开启porfiling

复制代码 代码如下:

--启用session级别的profiling 
root@localhost[sakila]> set profiling=1; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
--验证修改后的结果 
root@localhost[sakila]> show variables like '%profil%'; 
+------------------------+-------+ 
| Variable_name          | Value | 
+------------------------+-------+ 
| have_profiling         | YES   | 
| profiling              | ON    | 
| profiling_history_size | 15    | 
+------------------------+-------+ 
 
--发布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 | 
+---------+------+--------------------------------------------------------------------------------------------------------------+ 

3、获取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 profile 性能分析工具
反对 0举报 0 评论 0
 

免责声明：本文仅代表作者个人观点，与乐学笔记（本网）无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
    本网站有部分内容均转载自其它媒体，转载目的在于传递更多信息，并不代表本网赞同其观点和对其真实性负责，若因作品内容、知识产权、版权和其他问题，请及时提供相关证明等材料并与我们留言联系，本网站将在规定时间内给予删除等相关处理.

  • sql：mysql：函数：TIMESTAMPDIFF函数实现TimeStamp字段相减，求得时间差
    sql：mysql：函数：TIMESTAMPDIFF函数实现TimeS
     函数内指定是minute，则最终结果value值的单位是分钟，如果函数内指定为hours，则最终结果value值单位为小时。//UPLOAD_TIME 减去 CREATE_DTTM 求得时间差，以分钟数计时select avg(TIMESTAMPDIFF(MINUTE,CREATE_DTTM,UPLOAD_TIME)) value,LEFT(CREATE_DTTM
    03-08
  • mysql下如何执行sql脚本 执行SQL脚本
    1.编写sql脚本,假设内容如下:  create database dearabao;  use dearabao;  create table niuzi (name varchar(20));  保存脚本文件,假设我把它保存在F盘的hello world目录下,于是该文件的路径为:F:\hello world\niuzi.sql2.执行sql脚本,可以有2种方法: 
    02-10
  • MySQL 5.7版本sql_mode=only_full_group_by问题
    用到GROUP BY 语句查询时com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not functionally dependent on colu
    02-10
  • Oracle迁移到MySQL性能下降的注意点 oracle数据
    背景：最近有较多的客户系统由原来由Oracle改造到MySQL后出现了性能问题CPU 100%，或是后台的CRM系统复杂SQL在业务高峰的时候出现堆积导致业务故障。在我的记忆里面淘宝最初从Oracle迁移到MySQL期间也遇到了很多SQL的性能问题，记忆最为深刻的子查询，当初的
    02-10
  • MySQL与Oracle 差异比较之六触发器
    触发器编号类别ORACLEMYSQL注释1创建触发器语句不同create or replace trigger TG_ES_FAC_UNIT  before insert or update or delete on ES_FAC_UNIT  for each rowcreate trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_u
    02-10
  • mysql where条件：某时间字段为今天的sql语句
    1.查询：注册时间为今天的所有用户数：select count(*) from customer where TO_DAYS(createtime) = TO_DAYS(NOW())2.获取当前时间到凌晨24点还有多长时间：（Java中可用于判断某时间是否为今天）final Calendar cal = Calendar.getInstance();    ca
    02-10
  • mysql中的sql
    变量用户变量: 在用户变量前加@系统变量: 在系统变量前加@@运算符算术运算符有: +(加), -(减), * (乘), / (除) 和％ (求模) 五中运算位运算符有:(位于), | (位或), ^ (位异或), ~ (位取反),(位右移),(位左移)比较运算符有: = (等于),(大于),(小于), = (大
    02-10
  • mysql5.7配置文件修改sql_mode 重启无效解决方法。this is incompatible with sql_mode=only_full_group_by
    mysql5.7配置文件修改sql_mode 重启无效解决方
    whereis my.cnf找到配置路径：/etc/my.cnf找到[mysqld]，在下面添加sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION重要：如果没有[mysqld]，一定要先添加[mysqld]再在下
    02-10
  • mysql 8 查询报错(sql_mode=only_full_group_by)
    mysql 8 查询报错(sql_mode=only_full_group_by
    Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_
    02-10
  • Oracle、MySql、Sql Server比对
    MySql:廉价(部分免费)：当前，MySQL採用双重授权(DualLicensed)，他们是GPL和MySQLAB制定的商业许可协议。假设你在一个遵循GPL的***（开源）项目中使用MySQL，那么你能够遵循GPL协议免费使用MySQL。否则，你须要购买MySQLAB制定的那个商业许可协议。Windows $
    02-10
更多>MySQL教程
MySQL中表子查询与关联子查询的基础学习教程 CentOS系统下编译安装MySQL以及设置相关yum源的教程
更多>Oracle教程
Oracle数据库中表空间的基本管理操作小结 在Linux系统上同时监控多个Oracle数据库表空间的方法
更多>SQL Server
SQL SERVER修改函数名容易引发的问题分析 SQLSERVER分页查询关于使用Top方式和row_number()解析函数的不同
更多>MongoDB
教大家8天学通MongoDB——第一天 基础入门篇 MongoDB运行状态监控、性能分析工具mongostat详解
更多>PostgreSQL
PostgreSQL角色与用户管理介绍 Windows下PostgreSQL安装图解
点击排行
网站首页  |  关于我们  |  联系方式  |  使用协议  |  版权隐私  |  网站地图  |  网站留言  |  违规举报