首页 数据库 mysql教程 MySQL性能分析工具profile使用教程

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

Jun 07, 2016 pm 04:27 PM
mysql profile 使用 分析 工具 性能 教程

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中,可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例,而session级别紧影响当前session。该参数开启后,后续执行的SQL语句都将

分析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)     

本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前 By 尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
1 个月前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前 By 尊渡假赌尊渡假赌尊渡假赌

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

PHP连接MySQL后页面空白,die()函数无效是什么原因? PHP连接MySQL后页面空白,die()函数无效是什么原因? Apr 01, 2025 pm 03:03 PM

PHP连接MySQL后页面空白,die()函数失效的原因分析在学习PHP和MySQL数据库连接的过程中,常常会遇到一些让人困惑...

如何用PHP的cURL库发送包含JSON数据的POST请求? 如何用PHP的cURL库发送包含JSON数据的POST请求? Apr 01, 2025 pm 03:12 PM

使用PHP的cURL库发送JSON数据在PHP开发中,经常需要与外部API进行交互,其中一种常见的方式是使用cURL库发送POST�...

如何在macOS上将apscheduler定时任务配置为服务? 如何在macOS上将apscheduler定时任务配置为服务? Apr 01, 2025 pm 06:09 PM

在macOS上将apscheduler定时任务配置为服务在macOS平台上,如果你想将apscheduler定时任务配置为一个服务,类似于ngin...

在 LangChain 中,如何使用 AgentExecutor 替代已被禁用的 initialize_agent 函数? 在 LangChain 中,如何使用 AgentExecutor 替代已被禁用的 initialize_agent 函数? Apr 01, 2025 pm 04:18 PM

在LangChain中,如何替代已被禁用的initialize_agent函数?在LangChain库中,initialize_agent...

使用Django和MySQL处理几十万到一两百万条数据时,4核8G内存服务器应选择什么样的缓存方案? 使用Django和MySQL处理几十万到一两百万条数据时,4核8G内存服务器应选择什么样的缓存方案? Apr 01, 2025 pm 11:36 PM

使用Django和MySQL处理大数据量的缓存方案在使用Django和MySQL数据库时,如果你的数据量达到几十万到一两百万条�...

Python参数注解可以使用字符串吗? Python参数注解可以使用字符串吗? Apr 01, 2025 pm 08:39 PM

Python参数注解的另类用法在Python编程中,参数注解是一种非常有用的功能,可以帮助开发者更好地理解和使用函...

Linux系统自带Python解释器能删除吗? Linux系统自带Python解释器能删除吗? Apr 02, 2025 am 07:00 AM

关于Linux系统自带Python解释器的删除问题许多Linux发行版在安装时会预装Python解释器,它并非通过软件包管理器�...

MongoDB在Debian上的高可用性如何保障 MongoDB在Debian上的高可用性如何保障 Apr 02, 2025 am 07:21 AM

本文介绍如何在Debian系统上构建高可用性的MongoDB数据库。我们将探讨多种方法,确保数据安全和服务持续运行。关键策略:副本集(ReplicaSet):利用副本集实现数据冗余和自动故障转移。当主节点出现故障时,副本集会自动选举新的主节点,保证服务的持续可用性。数据备份与恢复:定期使用mongodump命令进行数据库备份,并制定有效的恢复策略,以应对数据丢失风险。监控与报警:部署监控工具(如Prometheus、Grafana)实时监控MongoDB的运行状态,并

See all articles