`
hongtoushizi
  • 浏览: 378827 次
  • 性别: Icon_minigender_1
  • 来自: 天津
社区版块
存档分类
最新评论

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

mysqlprofile 
阅读更多

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

 

转载自： http://www.jb51.net/article/56954.htm

分享到：
| MySQL Innodb数据库性能实践——VARCHAR vs ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    MYSQL性能调优工具介绍与应用

    ### MySQL性能调优工具介绍与应用 MySQL作为全球最广泛使用的开源关系型数据库之一，在企业级应用中的地位不可动摇。为了确保MySQL能够高效稳定地运行，掌握一系列性能调优工具至关重要。本文将详细介绍多种MySQL...

    mysql性能工具的使用.txt

    ### MySQL性能工具的使用 在数据库管理与维护的过程中，MySQL的性能优化是非常关键的一个环节。通过对MySQL性能的分析，可以有效地提升系统的响应速度、降低资源消耗，并提高整体的服务质量。本文将详细介绍几种...

    MySQL使用profile查询性能的操作教程

    MySQL的Profiler功能是一种用于分析SQL语句执行性能的工具，它可以帮助数据库管理员和开发者了解查询在执行过程中的各个阶段所消耗的时间，从而优化查询效率。Profiler功能自MySQL 5.0.37版本开始引入，提供了对SQL...

    MySQL性能分析show profiles详解（csdn）————程序.pdf

    `SHOW PROFILES`是MySQL提供的一种工具，用于分析SQL语句在执行过程中的资源消耗情况，帮助我们定位性能瓶颈。本篇将深入讲解`SHOW PROFILES`的使用方法和解析结果。 首先，`SHOW PROFILES`默认是关闭的，我们需要...

    MYSQL性能调优工具介绍

    二 性能分析工具介绍（mytop,innotop,orzdba,tcpdump,pt-query-digest,tbdba-slow-picker.pl,iotop,io-profile,nicstat,mpstat,iostat,vmstat,tcprstat,dstat,oprofile,vmtouch） 三 主从复制延时解决（relay-fetch...

    Windows下Neor Profile SQL 配置监控centos（linux）mysql详细步骤

    Neor Profile SQL 是一款强大的数据库性能分析工具，可以帮助开发人员和DBA们深入地了解数据库操作的性能表现。本文将详细介绍如何在Windows环境下配置Neor Profile SQL来监控CentOS（Linux）服务器上的MySQL数据库...

    Mysql自带profiling性能分析工具使用分享

    在优化MySQL性能时，理解查询执行的细节至关重要。通过`profiling`，我们可以定位性能问题，然后使用索引优化、查询重构、优化器Hint等手段来改进查询效率。同时，了解其他工具如`mysqldumpslow`和`mysqlslap`，可以...

    mysql中profile的使用方法教程

    MySQL中的`PROFILE`功能是一种用于分析SQL语句执行性能的工具，它可以帮助开发者了解SQL语句在执行过程中各个阶段的时间消耗，从而优化数据库操作。在MySQL 5.0.3版本之后，`PROFILE`功能被引入，但它在较新的版本...

    2.mysql查询性能优化1

    在本节中，我们讨论了 MySQL 查询性能优化的几个重要方面，包括查询优化的重要性、查询优化技术、MySQL 查询优化工具等。通过了解这些知识点，数据库管理员可以更好地优化 MySQL 查询性能、提高数据库的响应速度、...

    mysql辅助工具

    ### 性能工具 - **pt-query-digest** - **功能**：从慢查询日志或其他日志文件中提取并分析SQL查询，帮助定位性能瓶颈。 - **使用场景**：性能调优、查询分析。 - **pt-ioprofile** - **功能**：跟踪特定进程的...

    mysql集群配置教程

    6. **环境变量设置**：编辑`/etc/profile`文件，将MySQL的bin目录添加到PATH环境变量中，使得命令行可以直接调用MySQL的工具，如`ndb_mgm`。 7. **复制管理工具**：将`ndb_mgm`等管理工具复制到全局可执行路径，...

    【MySQL】SQL性能分析 (七).pdf

    以上内容涵盖了MySQL数据库性能优化的关键领域，包括SQL执行频率分析、慢查询日志分析、Profile详情分析以及EXPLAIN执行计划分析。通过这些工具和技术的应用，不仅可以提高数据库性能，还能确保应用程序的高效稳定...

    mysql诊断分析.pdf

    基准测试是评估MySQL性能的基础步骤，它帮助我们理解在特定配置（硬件、操作系统、数据库设置等）下，数据库能够达到的性能水平。通过基准测试，我们可以找到性能瓶颈并调整配置以满足实际系统需求。常用的基准测试...

Magicbox
Global site tag (gtag.js) - Google Analytics