性能分析之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次。

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