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

来源:http://www.prospettivedarte.com 作者:计算机教程 人气:106 发布时间:2020-04-23
摘要:profile是什么 MySQL性能分析工具profile使用教程,mysqlprofile 分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中,可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和s

profile是什么

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

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

1、有关profile的描述

复制代码 代码如下:

--当前版本 
[email protected][sakila]> show variables like 'version'; 
--------------- ---------------------------------------  
| Variable_name | Value                                 | 
--------------- ---------------------------------------  
| version       | 5.6.17-enterprise-commercial-advanced | 
--------------- ---------------------------------------  
 
--查看profiling系统变量 
[email protected][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的帮助 
[email protected][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 
[email protected][sakila]> set profiling=1; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
--验证修改后的结果 
[email protected][sakila]> show variables like '%profil%'; 
------------------------ -------  
| Variable_name          | Value | 
------------------------ -------  
| have_profiling         | YES   | 
| profiling              | ON    | 
| profiling_history_size | 15    | 
------------------------ -------  
 
--发布SQL查询 
[email protected][sakila]> select count(*) from customer; 
----------  
| count(*) | 
----------  
|      599 | 
----------  
 
--查看当前session所有已产生的profile 
[email protected][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,之前一个,现在一个 
[email protected][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产生的相应开销 
[email protected][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 
[email protected][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                  | 
---------- ------------ --------------------------------  
 
--获取指定查询的开销 
[email protected][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部分的开销 
[email protected][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部分的开销 
[email protected][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 | 
---------------------- ----------  
 
--同时查看不同资源开销 
[email protected][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开销,且按最大耗用时间倒序排列 
[email protected][sakila]> set @query_id=2; 
 
[email protected][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 
[email protected][information_schema]> select * from profiling limit 3,3G; 
*************************** 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会被自动关闭 
[email protected][sakila]> set profiling=off; 
Query OK, 0 rows affected, 1 warning (0.00 sec)     

当我们要对某一条sql的性能进行分析时,可以使用它。

服务器维护-查看mysql的各项性可以指标

日常维护有很多方面的工作:数据库状态监控、性能分析、SQL代码分析与优化等等。数据库巡检等等工作,你可以参考国内上海爱可生公司网站上提供的MySQL服务相关的内容来写,呵呵。还可以咨询他们。  

Profiling是从 mysql5.0.3版本以后才开放的。

mysql性可以优化,欢迎

你给的信息基本没用,你没说你数据库的应用类型,以及你运营环境

配置文件基本不用改动,具体问题具体微调,他不会对数据库优化起明显作用

优化一般分一下几个:
1.存储引擎的选择。高并发,update,insert较多、切需要事务处理适用 innodb;select为主用myisam;通常所有的表会使用相同的引擎,便于维护
2.根据数据量、cpu、内存、磁盘i/o开销可以选择分表、分库、主从负载
3.最关键的就是sql和索引了,配合适当的索引,写出高效的sql是优化的基本

优化是一个调试的过程,没有1个通用的模式,要从实际情况作合理选择  

http://www.bkjia.com/Mysql/903463.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/903463.htmlTechArticleMySQL性能分析工具profile使用教程,mysqlprofile 分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中,可以通过配置profiling参数来启用...

启动profile之后,所有查询包括错误的语句都会记录在内。

关闭会话或者set profiling=0 就关闭了。

此工具可用来查询SQL执行状态,System lock和Table lock 花多少时间等等,

对定位一条语句的I/O消耗和CPU消耗 非常重要。(SQL 语句执行所消耗的最大两部分资源就是IO和CPU)

--在mysql5.7之后,profile信息将逐渐被废弃,mysql推荐使用performance schema

mysql官网定义

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

简单的说,当前会话资源的消耗情况。

注意:show profile和show Profiles都是不建议使用的,在mysql后期的版本中可能会被删除;官网建议使用Performance Schema

怎么使用

profile默认关闭,生产环境中也建议关闭。

查看当前环境的profile设置

mysql show variables like '%profiling%'; ------------------------ ------- | Variable_name | Value | ------------------------ ------- | have_profiling | YES || profiling | OFF || profiling_history_size | 15 | ------------------------ ------- 

profiling off表示profile关闭,profiling_history_size 15表示保存最近15条SQL的资源消耗情况。

开启profile功能,可以使用命令

本文由皇牌天下投注网发布于计算机教程,转载请注明出处:MySQL性能分析工具profile使用教程,mysqlprofile

关键词:

上一篇:mysql 5.7.17的最新安装教程图文详解

下一篇:没有了

最火资讯