性能分析之MySQL Report分析

声明:近期在工作时需要用到mysqlreport时,做的一些整理。

MySQL report分析

基本信息

mysql当前的版本,运行的时间,以及当前系统时间。 MySQL服务器版本信息表明MySQL服务器包含和不包含哪些特点。 MySQL服务器运行时间表明报告价值的代表性。服务器运行时间对于评估报告是很重要的,因为如果服务器不运行几个小时的话,输出报告有可能存在曲解和误导性。有时甚至运行几个小时时间都是不够的,比如,MySQL服务器运行了午夜的6个小时几乎没有业务访问过。最理想的情况是,MySQL服务器运行一天之后再运行mysqlreport来输出报告,这样报告的代表价值要比系统刚运行时要好的多。 在性能场景的运行周期前启动mysql,在性能场景结束后生成mysqlreport会比较有用。比如此例中,场景运行了1小时后执行了mysqlreport。

请注意,这里所指的 Key Buffer 是指MyISAM Storage Engine 所使用的 Shared Key Buffer,InnoDB 所使用的 Key Buffer 并不包含在内。 MySQL Server 的 Buffer 大略可分为二种:

Global Buffer:由所有 Client 所共享的 Buffer keybuffer innodbbufferpool innodblogbuffer innodbadditionalmempool net_buffer ...等等

Thread Buffer:个别的 Connection 所需占用的 Buffer 例如: sortbuffer myisamsortbuffer readbuffer joinbuffer readrnd_buffer ...等等。

计算 Server 至少需使用的总内存数量的方式为: minmemoryneeded = globalbuffer + (threadbuffers * max_connection) 关于 MySQL 的 Cache 机制有一点需要特别注意,MyISAM Storage Engine 将每个 table 分成三个档案储存在硬盘之中,例如若有一个数据表的名称为 example,那么就会在硬盘上发现 example.FRM, example.MYD,example.MYI 等三个档案。这三个档案所储存的数据如下: FRM: 储存这个数据表的结构 MYD: Row Data,也就是你存在 example 数据表里的数据 MYI: 此数据表的索引接下来是重点: 当MySQL要Cache某个资料表时,MySQL只会Cache索引,也就是MYI档案,而Row Data(.MYD)则是交由操作系统来负责 Cache。 到底 Key Buffer 要设定多少才够呢?如前所述,MySQL只会Cache 索引(.MYI),因此只要将数据库中所有的 MYI档案加总起来,你就会知道大概要设为多少。

这一行指示MySQL在生成报告时占用索引缓存的情况(4.1.2和以后的版本有效,因为Keyblocksunused是在4.1.2以后才增加的。)。如果前一行表示是一个高水位指示,那么这一行的占用量应该是小于或等于上一行的高水位。有时也会高于高水位指示,这是不是MySQL的Bug目前还不得而知。不管怎样,这一行和前一行的结果能够很好地指示keybuffersize的参数设置的是不是足够大。 在本例子中,MySQL服务器状态就不太好,占用了8M,是索引缓存的100%,已经是全部的空间了。

计算公式:Write hit = MySQL将索引写入硬盘的次数 / MySQL将索引写入RAM的次数

比索引写入命中率更重要的是索引读取命中率(key read hit)。这一行描述了索引读取的效率(这是个百分比比率值,分子是索引读取硬盘的量,分母是索引读取缓存的量)。索引读取命中率应该低于99%。 过低的百分比表明这会是一个问题。一个低索引命中百分比通常可能是索引空间太小了。索引空间是为了避免MySQL装载过多的索引到内存中,当这种情况发生时,MySQL会恢复从硬盘读取索引,这就会使得硬盘相当慢而且会使索引无效。 通常来讲,开始运行MySQL的前1-2个小时,这一行的值会低于99%,经过1-2个小时以后,取值就会接近99%。

所有的“操作”可分为5类:数据操作语句(DMS)、查询缓存命中率(QC Hits)、COMQUIT、其他通信命令和未知。这5个分类是动态显示的。mysqlreport按照他们的总次数降序排列。这个子报告能够明显的表示出MySQL在忙着干什么。理想情况下,MySQL应该忙于DMS 或者QC Hits,因为这些行为时真正完成某些事情的。COMQUIT,Com和Unknown类别是必要的,但是处于次要地位。 在进一步解释每一类之前,需要说明的是这部分子报告第三列表明该列值占总“操作”请求数的百分比,“操作”部分的其他子报告也是如此。在例子中,DMS数占总操作数的82.84%是正常示数。 Data manipulation statements(DMS)包括SELECT,INSERT,REPLACE,UPDATE和DELETE。基本上,DMS是MySQL数据库干的最有用的,因此,DMS应该是MySQL做的最多的。DMS子报告会详细显示。 QC Hits(Query Cache Hits) 是MySQL查询执行过程中,通过查询缓存补偿,而不是实际执行的操作数。具有一个较高的QC Hits数是令人期待的,因为QC的返回是非常快的。但是,完成有效地QC缓存是非常困难的。这在后面的Query Cache Report中的Insrt:Prune和Hit:Insert部分将深入解释。 在例子中,QC Hits是没有显示的,说明在这个report期间没有select语句。 COMQUIT 是个可以忽略的无关紧要的参数,它包含到报告中为了保证完整性。 Com_ 表示MySQL处理的各种命令,通常都是协议相关的。理想情况下,在这个指标应当比较低,因为当比较高时,说明MySQL忙而无用。该分类参数过高,则表示一些怪异的问题,后面在Com_将详细讨论。 Unknown 是一个推测的目录。理想情况下,前四部分的总和应该是等于全部“操作”数量,但通常不相等。这是因为存在一些MySQL的操作,增加了操作计数器,但是并没有表现在单独的指标上。 这一行会动态显示为"+Unknown"或者"-Unknown"。"+Unknown"表示存在更多的操作数,比mysqlreport计算的多;"-Unknown"表示mysqlreport计算的数比所有的统计数少。

DMS子报告,和DTQ子报告一样,第一列是按照降序排列的,第二列是按每秒计算的结果,第三列表明该列值占总数的百分比。表示了前文所提到数据操作数(SELECT、INSERT等)。 第一行显示的和DTQ报告中的显示一样的。 这一子报告显示MySQL数据库是哪一种类的数据库:是查询负荷高、还是插入负荷高、还是其他的。MySQL服务器都是倾向于查询负荷高(SELECT heavy)。了解是哪种类型的MySQL数据库有利于理解其他的报告值。例如,一个插入负荷高的服务器,其写入率会接近为1.0,这种类型的数据库锁表报告值也会偏高,这类数据库适合采用InnoDB类型表;一个查询负荷高的数据库,就会表现出读取率为1和一个较低的表锁值,这种类型的数据库需要采用查询缓存,适合于采用MyISAM表。 在这个例子中,服务器是一个插入高的数据库。很明显,这个数据库面向插入事务。知道数据库类型就有利于数据库参数的优化。

查询和排序报表

Scan 指的是有多少 SELECT statements 造成 MySQL 需要进行 Full Table Scan。Full Join 的意思与 Scan 差不多,但它是适用在多个 Tables 相互Join 在一起的情况。这二种情况的执行效能都非常的差,因此原则上你会希望这两个数值越低越好。但这也不是绝对的,仍然要考虑实际的情况,例如虽然Server 有很高比例的 Scan,但若这些 Scan 都是针对一些只有几十笔数据的 table,那么相对而言它依然是十分有效率的;但反之,若这些 Scan 是针对具有上百万笔数据的 table,那么就会严重影响系统效能。

在查询为主的数据库的由要开启缓存查询,并且要配置合理的查询缓存大小。

但是,查询缓存有一个需要注意的问题,那就是缓存过期策略,MySQL采用的机制是,当一个数据表有更新操作(比如update或者insert)后,那么涉及这个表的所有查询缓存都会失效。这的确令人比较沮丧,但是MySQL这样做是不希望引入新的开销而自找麻烦,所以“宁可错杀一千,不可放过一个”。这样一来,对于select和update混合的应用来说,查询缓存反而可能会添乱

比如说如下mysqlreport中查询缓存的报告:

再来分析另一个例子中的QC情况:

这个部份包含了两项信息:第一项是 Waited,代表 MySQL 需要等待以取得 table lock 的次数。第二项是 Immediate,表示MySQL 不需要等待即可立刻取得 table lock 的次数。对数据库来说『等待』几乎可以肯定是一件很不好的事情,因此 Waited 的值应该要越小越好。最具有代表性的是第三个字段 (Waited 占所有 table lock 的百分比),这个数值应该要小于 10%,大于这个值就表示table/query 的索引设计不良或是有过多的 Slow Query。

从下面的数据来看,在场景执行期间就没有发生过锁表的情况。

连接报表

Connections Report 所代表的意义与 Tables Report 相似,请各位以此类推。比较需要注意的是:若你发现 Connections 的使用率接近 100%,也许你会想调大 maxconnections 的值以允许MySQL 的 Client 建立更多连接。然而,这通常是一种错误。常常可以发现很多网络上的数据会教我们要调大 maxconnections,但却从来没有给一个明确的理由。事实上,maxconnections 的默认值(100),就算是对于负载十分沉重但有良好调校过的 Server 都已十分足够。MySQL 对于单一连接的数据处理通常只需要零点几秒的时间即可完成,就算是最大只能使用 100 个连接也够让你用上很长一段时间。若是的 Server 有着非常高的最大连接数(max connections)或是单一连接需要很长时间才可完成,那么问题八成不是 maxconnections 的值不够大而是在别的地方,例如 slow queries、索引设计不良、甚至是过于缓慢的 DNS 解析。在将 max_connections 的值调到 100 以上之前,应该要先确定真的是因为Server 过于忙碌而需要调高此数值,而不是其它地方出了问题。每秒平均连接数有可能会很高,事实上,若这个值很高而且 Server 的运作十分顺畅,那么这通常会是一个好现象,无需担心。大部份 Server 的每秒平均连接数应该都会低于 5/s。

MySQL可以将临时表创建在磁盘(Disk table)、内存(Table)以及临时文件(File)中,显然,在磁盘上创建临时表的开销最大,所以我们希望MySQL尽量不要在磁盘上创建临时表。 在MySQL的配置中,我们可以通过tmptablesize选项来设置用于存储临时表的内存空间大小,一旦这个空间不够用,MySQL将会启用磁盘来保存临时表,你可以根据mysqlreport的统计尽量给临时表设置较大的内存空间。 在本示例中,临时表的情况如下,只用到了一个临时表。

也许你会觉得创建线程的消耗不值一提,但是所谓优化都是在你系统繁忙下的救命稻草。 一个比较好的办法是在应用中尽量使用持久连接,这将在一定程度上减少线程的重复创建。另一方面,从上面的Cached=0可以看出,这些线程并没有被复用。 在本例中,threadcachesize = 64,只用到了5个线程,并且没有复用(Cached)的。

这部分数据和innodbflushlogattrx_commit参数关系非常大。

innodbflushlogattrx_commit = 1 表示事务提交时立即将事务日志写入磁盘,同时数据和索引也立即更新。这符合事务的持久性原则。

innodbflushlogattrx_commit = 0 表示事务提交时不立即将事务日志写入磁盘,而是每隔1秒写入磁盘文件一次,并且刷新到磁盘,同时更新数据和索引。这样一来,如果mysqld崩溃,那么在内存中事务日志缓冲区最近1秒的数据将会丢失,这些更新将永远无法恢复。

innodbflushlogattrx_commit = 2 表示事务提交时立即写入磁盘文件,但是不立即刷新到磁盘,而是每隔1秒刷新到磁盘一次,同时更新数据和索引。在这种情况下,即使mysqld崩溃后,位于内核缓冲区的事务日志仍然不会丢失,只有当操作系统崩溃的时候才会丢失最后1秒的数据。

显然,将innodbflushlogattrx_commit设置为0可以获得最佳性能,同时它的数据丢失可能性也最大。

Free: 空闲页,是使用率(%Used)的对立方。 Data: 数据页,列%Dirty,展示已经被修改过,但还没有被刷新到磁盘存储的数据页的比率。 Misc:用于管理分配行锁和自适应哈希索引导致的开销使用的页。 Latched: 目前正在读写、或者因为其他原因无法被刷新的页。

innodb锁报表

Waits:等待某行解锁的累积次数,最好为0。 Current:当前正在等待解锁的行个数,最好为0。 Time acquiring:显示了毫秒(ms)级行锁等待数据。分别是总值、平均值和最大值。同样最好是0次。

包括三种自描述类型:创建、读取、写入,分别用来表示缓冲池中页的创建、读取 和写入的数量和速率(即每秒操作数)。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181217B091Y200?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券