MySQL profiling的用法

原创 2015年07月07日 11:27:47

    MySQL数据库最常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候那么平瓶颈就会出现在网络上,我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。今天我们不讨论服务器硬件的性能瓶颈,只是谈谈MySQL系统本身,通常需要对数据库进行分析,常见的分析手段有慢查询日志,EXPLAIN 分析查询,profiling分析以及show命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

     这个是一系列的文章,今天只谈SHOW PROFILE Syntax。通过它我们可以清楚的知道某条Query的性能瓶颈到底在哪里,是消耗的 CPU计算太多,还是需要的的 IO 操作太多?具体的做法如下:

    1.开启profiling参数
        set profiling=1;
    2.执行Query
        select * from a;
    3.获取系统中保存的所有 Query 的 profile 概要信息
        show profiles;
    4.针对单个 Query 获取详细的 profile 信息。
        show profile all for query 1;

         在所有列中,我们只关心StatusDuration这两列。status:是profile里的状态,duration:是status状态下的耗时。因此我们关注的就是那个状态最耗时,这些状态中那些可以优化。在profile中,下面几种状态是需要重点关注的,而且大多数可以通过创建合适的索引就能完成优化。

状态 建议
System lock 确认是由于哪个锁引起的,通常是因为MySQL或InnoDB内核级的锁引起的建议:如果耗时较大再关注即可,一般情况下都还好
Sending data 从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据,再发送给客户端,数据量很大时尤其经常能看见
备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是Writing to net
建议:通过索引或加上LIMIT,减少需要扫描并且发送给客户端的数据量
Sorting result 正在对结果进行排序,类似Creating sort index,不过是正常表,而不是在内存表中进行排序
建议:创建适当的索引
Table lock 表级锁,没什么好说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表
create sort index 当前的SELECT中需要用到临时表在进行ORDER BY排序
建议:创建适当的索引
checking query cache for
querychecking privileges on cached


sending cached result to clien

storing result in query cache
和query cache相关的状态,已经多次强烈建议关闭


语法如下:

    SHOW PROFILE [type [, type] ... ]
       [FOR QUERY n]
       [LIMIT row_count [OFFSET offset]]

    type:
         ALL:显示所有的开销信息
       | BLOCK IO:显示块IO相关开销
       | CONTEXT SWITCHES:上下文切换相关开销
       | CPU:显示用户CPU时间、系统CPU时间
       | IPC:显示发送和接收相关开销信息
       | MEMORY:目前没有实现
       | PAGE FAULTS:显示页面错误相关开销信息  
       | SOURCE:显示和Source_function,Source_file,Source_line相关的开销信息
       | SWAPS:显示交换次数相关开销的信息 


profile功能只能在SESSION级别使用,还做不到像SQL Server那样可以全局开启,收集一段时间后再关闭,这点有待改进。以上信息也可以通过INFORMATION_SCHEMA.PROFILING表中的信息来查看。


  参考资料:
       https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
       https://dev.mysql.com/doc/refman/5.7/en/profiling-table.html

mysql show profiles 使用分析sql 性能

Show profiles是5.0.37之后添加的,要想使用此功能,要确保版本在5.0.37之后。 查看一下我的数据库版本 mysql> Select  version(); +--------...

Mysql使用profiling分析慢sql语句的原因

CleverCode的同事最近给我推荐了一个分析mysql中sql语句的工具,发现这个工具非常不错,能够很准确的分析出查询的过程中sql语句具体的时间花在了哪里。 CleverCode在这里总结一下,...

Mysql中Profiling之性能分析

MySQL5.0.37版本以上支持了Profiling – 官方手册。此工具可用来查询 SQL 会执行多少时间,System lock和Table lock 花多少时间等等,对定位一条语句的 I/...
  • gzh0222
  • gzh0222
  • 2012年02月06日 11:55
  • 1671

mysql 优化之 profile

NOTE:本篇博客基于mysql 5.6 一. profile相关参数 show variables like '%profil%'; +------------------------+------...

mysql profile及其对应表使用

--mysql的profile可用于查看一个sql的具体消耗 show profile all for query 1\G; --profiling has a default value of 0...
  • jumewo
  • jumewo
  • 2016年01月04日 10:17
  • 409

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

MYSQL查询优化:profile功能

日常数据库性能分析必备工具--李晓蒙 1、不过版本要在5.0.37之后。(SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37....
  • isoleo
  • isoleo
  • 2015年06月15日 22:02
  • 1804

MySQL优化工具之 profiling

 MySQL优化工具之profiling 使用慢查询日志分析出慢查询语句后,用profiling分析该语句的优化后执行效果。 查看慢查询设置 mysql> ...

mysql profiling

  • 2012年10月30日 09:54
  • 140KB
  • 下载

MySQL Profiling的使用

  • 2015年07月22日 18:38
  • 311KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL profiling的用法
举报原因:
原因补充:

(最多只允许输入30个字)