mysql 性能分析_Mysql 之 性能分析（mysqlreport工具）

一、mysqlreport 作用

进行MySQL的配置优化，首先必须找出MySQL的性能瓶颈所在；而SHOW STATUS输出的报告正是用来计算性能瓶颈的参考数据。mysqlreport不像SHOW STATUS那样简单的罗列数据，而是对这些参考数据加以融合计算，整理成一个个优化参考点，然后就可以根据这个优化参考点的值以及该点的衡量标准，进行对应调整。

一、mysqlreport 安装

[root@localhost ~]# yum -y install perl-DBD-MySQL #依赖包

[root@localhost ~]# yum -y install perl-DBI #依赖包

[root@localhost ~]#wget http://pkgs.fedoraproject.org/repo/pkgs/mysqlreport/mysqlreport-3.5.tgz/33a345f5e2c89b083a9ff0423f7fd7b4/mysqlreport-3.5.tgz

[root@localhost ~]#tar -zxf mysqlreport-3.5.tgz

[root@localhost mysqlreport-3.5]# ll

总用量 144

-rw-r--r-- 1 121813 18980 35147 4月 16 2008 COPYING

-rwxr-xr-x 1 121813 18980 38873 4月 16 2008 mysqlreport #分析报告工具

-rw-r--r-- 1 121813 18980 12538 4月 16 2008 mysqlreportdoc.html

-rw-r--r-- 1 121813 18980 52932 4月 16 2008 mysqlreportguide.html

一、mysqlreport 使用

[root@localhost mysqlreport-3.5]# ./mysqlreport --user test --password xxxxx --host 10.3.151.185 --socket /tmp/mysqld.sock

Use of uninitialized value in formline at ./mysqlreport line 1099.

Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829.

Use of uninitialized value in formline at ./mysqlreport line 1227.

Use of uninitialized value in formline at ./mysqlreport line 1235.

MySQL 5.6.37-log uptime 8 22:42:13 Thu Aug 23 21:57:27 2018

__ Key _________________________________________________________________

Buffer used 424.00k of 8.00M %Used: 5.18

Current 1.46M %Usage: 18.24

Write hit 100.00%

Read hit 100.00%

索引报表：3-7行 索引报表是第一个主要章节，因为索引(key或者说index)对于mysql数据库，是最最最重要的。虽然报表不可能直接告诉你这个库的索引好还是不好，但它能告诉你这个索引缓冲区(key buffer)被利用的怎么样了。 注意：本报表仅汇总默认的MyISAM表的共享key buffer信息，而不会管管理员自建的其他空间。 缓冲区使用情况：第4行 对于mysql，我们的第一个问题就是：到底用了多少key buffer？如果不太多，没问题~因为mysql只会在有需求的时候才分配系统内存给key buffer。也就是说，my.cnf中定义了’key_buffer_size=512M’，

不代表mysql启动时就创建一个512M大小的key buffer。 本行显示的，是mysql曾经使用过的key buffer峰值大小。而事实上，mysql应该用的更少，或者诡异的更多。这个更多的情况，mysql的术语叫“高水位”这个情况和my.cnf里的’key_buffer_size’是否足够大密切相关。当“水位”已经达到80-90%的时候，赶紧加大你的’key_buffer_size’吧。 注意：永远不用“担心”这个值超过95%，mysql文档指出，key buffer中的一部分会被mysql主程序用于内部数据结构，这些是mysqlreport无法统计的内容。所以，所谓的95%，其实已经是100%了…… 当前情况：第5行 这行只有在mysql版本高于4.1.2时出现，因为之前mysql的’show status’中没有’key_blocks_unused’。这行数据显示的是mysql当前使用的key buffer大小。如果上行的used%太大的话，那么这行必然不会超过used，除非碰上那个传说中的bug了。综合这两行，相信对’key_buffer_size’的设置是否合理就有谱了~ 本例中，mysql使用了60M的key buffer(12%)，这就很不错，离满负荷运行还早着呢。 写命中：第6行 从本质上说，索引是基于内存的。因为访问内存的速度比硬盘快太多了。不过，mysql从磁盘里进行一点点读写操作总是不可避免的。 这行数据显示了写索引的效率(具体意思是：写入磁盘的key与写入内存的key的比值)。这个值没有什么参考答案，而是取决于业务类型。如果mysql主要执行的是update/insert之类的操作，那么正常比值接近0%；如果执行的select居多，那比值超过90%也是正常的。不过如果你看到的是一个负数，那说明mysql总是在往那个慢的要死的磁盘里写索引，这就很不妙了。 要想知道到底比值正常与否，请参考之后的DMS报表内容。 读命中：第7行 比写命中重要多了的就是读命中。同样，这个值就是读自磁盘的key与读自内存的比值。这个比值最好别低于99%！！再低就有问题了——很可能就是key buffer太小。mysql没法从内存里读到，只好找硬盘了…… 当然，如果你刚重启过一次mysql，那在一两个小时内，命中率低一点也是正常的。

__ Questions ___________________________________________________________

Total 1.82M 2.4/s

Com_ 1.43M 1.9/s %Total: 78.94

DMS 1.01M 1.3/s 55.75

-Unknown 644.84k 0.8/s 35.49

COM_QUIT 14.44k 0.0/s 0.79

Slow 100 ms 46.73k 0.1/s 2.57 %DMS: 4.61 Log:

DMS 1.01M 1.3/s 55.75

SELECT 971.21k 1.3/s 53.45 95.87

INSERT 24.23k 0.0/s 1.33 2.39

UPDATE 17.20k 0.0/s 0.95 1.70

DELETE 388 0.0/s 0.02 0.04

REPLACE 0 0/s 0.00 0.00

Com_ 1.43M 1.9/s 78.94

admin_comma 631.92k 0.8/s 34.78

set_option 529.08k 0.7/s 29.12

commit 256.63k 0.3/s 14.12

请求报表：9-26行 第二个主要章节。它展示了很多关于mysql在做什么以及做的怎么样的内容。请求(question)包括SQL查询(query)，也包括mysql协议通讯。大家经常关注的一个性能是mysql的qps(每秒执行查询数)。不过从一个更广泛的眼光看来，这个衡量标准其实是很随意的……mysql需要处理很多其他的请求。本报表试图展现的，就是这么一个更完整的内容。 总值：第10行 本行第一列，回答自运行起mysql一共处理多少请求，第二列，得出自运行起平均每秒钟处理多少请求。大家可能以为第二列这个值就是我们想要的qps了。但mysql真的做了这么多事情么？继续往下看。 (再次提醒大家注意questions和queries的区别) 查询的总体分布报表(DTQ)：第11-15行 所有的请求都可以被粗略的分入五类：数据操作语句(DMS)，查询缓存命中(QC Hits)，COM_QUIT，其他的COM_命令以及其他未知的东东。接下来的五行分别显示这些，从大到小排列。这样你可以一眼看出mysql最重要的任务是什么了。一般的说，DMS和QCHits是主角，COM_是必须的，额，路人甲…… 再详细解释每行之前，提示一下，第三列的比值分母是上面那行的总值。比如本例中DMS占到了82.84%就挺不错的。

DMS包括：select/insert/replace/update/delete(其他的比较偏门，mysqlreport干脆就排除他们了)。正常的说，mysql最应该做的事情，就是这些DMS了。详细内容见17-22行。 QC Hits是mysql从查询缓存里直接获取结果的数量。我们梦寐以求的就是让这个命中率变高，因为这意味着mysql响应变的相当快。但这也意味着你必须接受一定的数据差异性。详细理由见QC报表中的insert/prune和hit/insert比值部分。 在本例中QC Hits达到了16.91%，看来很不错的样子。可别被这么一条数据迷惑了，38-45行的QC报表会给你一个完全不一样的结论。 COMQUIT，嗯，凑数的。 COM，如果这个值比较高的话，或许会有些问题，详见23-26行。 Unknown，理想情况下，有上面四个类别就够了。因为有时候，mysql处理了几个请求，却没有记录相应的操作数。所以Unknown有+-两种。+说明mysqlreport统计的多了，-就是少了。这个类别浮动性很大，某些特定情况下，可能会排名很靠前，不过最好还是在最底下吧。

慢查询：第16行 第16行非常重要，展示的是mysql执行的慢查询数。默认情况下，配置的’long_query_time’是10秒钟。事实上，大家都觉得这太长了，一般都改成1秒，甚至更短，mysql在版本5以后，支持到微妙us级别的。 配置的’long_query_time’会在’Slow’后面显示，默认8个字符，所以如果配置的是’9.999999 ms’，只会显示成’999.999 ‘，不过应该不会这么无聊吧~ 理想情况下最好这里永远都是0，不过不太可能，多少还是有点。只要第三列的比例低于0.05%就行。 第四列，DMS中的slow比值；第五列，慢查询日志是否记录。强烈建议选择ON。

DMS：17-22行 和DTQ一样，第一行也是总值，其余内容也是动态排名。这部分内容可以解释mysql服务器偏重于什么类型：select还是insert，或者其他。一般来说会是select吧。明确这个问题，对我们理解其他数值有很大的帮助。比如说：一个insert型的mysql，他的写比率接近1.0，同时带来比较高的表锁。然后很可能用innodb表；一个select型的mysql则相反，读比率高，表锁少，而且很可能用的是MyISAM表。 本例就是一个select型的。65.72%的总请求是select(在DMS的比例提高到79.33%)，显然我们可以朝着select的方向进行优化了。

COM_：23-26行 这部分内容都很直观，在mysql协议里都有，想com_change_db，一眼就知道是干嘛的。 如果在DTQ排名里COM_比较高，那说明mysql忙着干自己的事情而不是响应SQL查询。比如说，如果一台mysql的com_rollback高，可能糟糕了，你的事物回滚失败了。结合之前的DTQ报表分析这个东西吧~ 一般这些东西不能出什么问题，不过时不时看一眼还是有必要的。

__ SELECT and Sort _____________________________________________________

Scan 825.18k 1.1/s %SELECT: 84.96

Range 3 0.0/s 0.00

Full join 538.09k 0.7/s 55.40

Range check 0 0/s 0.00

Full rng join 0 0/s 0.00

Sort scan 1.87M 2.4/s

Sort range 3.29k 0.0/s

Sort mrg pass 0 0/s

select/sort报表：28-36行 select和sort都是select_的内容。其中最主要的是29和31行：scan和full join。scan展示的是对全表进行扫描的select语句个数。full join和scan很像，除了它还出现于多表查询。程序联合多表进行全表扫描，听起来就慢的可怕……总之，对于这两个数值，只有更低，没有最低！

__ Query Cache _________________________________________________________

Memory usage 16.81k of 1.00M %Used: 1.64

Block Fragmnt 100.00%

Hits 0 0/s

Inserts 1 0.0/s

Insrt:Prune 1:1 0/s

Hit:Insert 0.00:1

QC报表：38-45行 只有mysql版本支持QC并且my.cnf开启了QC的时候该报表才会出现。 内存使用：39行 如果内存使用的接近最大设置值，在更下面的Prune数据上也会有反应，因为QC里的查询会被踢出来。 内存碎片：40行 内存块碎片(block fragmnt)的详细解释参见《MYSQL手册》的5.14.3章节所述： ‘query_cache_min_res_unit’的默认值是4KB，大多数情况下足够用了。如果你有一个返回超小结果的海量查询，默认的块大小(即4KB)可能会导致大量的内存碎片，同样也浪费了很多空闲内存块。因为内存不足，碎片会强制删除(prune，我也不知道为啥不叫delete或者purge)QC里的部分内容。这时候你就得降低这个’query_cache_min_res_unit’设置。至于空闲块和QC的删除阀值，分别由’Qcache_free_blocks’和’Qcache_lowmem_prunes’定义。 内存碎片的计算方法是空闲内存块除以总内存块数。比值越大，碎片越多，10-20%就已经超出平均水平了。 本例的13.05%还是可以接受的，不过最好还是检查一下’query_cache_min_res_unit’是不是能调调~

Hits/Inserts/Prunes：41-43行 Hits是最重要的，它反映了select有多少是从QC里获得的应答，当然越多越好。至于insert和prune，或许从44行的比值中更好理解一下。之前有提到prune多说明qc太小，当然这只是一种可能而已。 本例中，只有55%的QC被利用，而碎片又不是太高。prune达到每秒16次，比QCHits高了一倍！打个不太恰当的(这话我加的，感觉比直接理解技术更难懂的比喻)比方：这台mysql的QC就像苹果树一样，苹果还没摘呢，树枝已经被砍掉了……

insert/prune和hit/insert比：44-45行 insert/prune是一个波动性的QC指标。一个稳定运行中的QC，insert进QC的查询数量应该大于prune掉的查询数量。而一个不稳定的QC，比值或许是1:1，甚至偏向prune。这说明两个问题：1、QC大小不够；2、mysql试图缓存一切，结果帮了倒忙~ 如果是第一种情况，简单的加大QC大小就够了。然后再观察碎片和内存使用率的情况。 但更多的时候是第二种情况。因为QC设置里开启的默认type1就是要求mysql尽可能的缓存一切东西。

mysql官方说明里这么解释这个’type 1’的：“缓存一切查询结果，除非查询时使用’select sql_no_cache’方式”。可惜这个’sql_no_cache’基本没人用。另一个稍微好一些的方式是’type 2 demand’，解释如下：“只有在查询使用’select sql_cache’时才缓存查询结果”。这个type对开发人员要求比较多，因为他们得明确指出哪些要缓存，哪些不要。不过也没人比他们更清楚到底哪些是该缓存的啦~ hit/insert用来反映QC的有效性。理想情况是：mysql插入一批稳定的查询到QC里，然后源源不断的命中这批结果……所以，如果QC的有效性足够，这个比值应该是偏向hit的。如果不幸的偏向了insert，那说明QC其实没起到太大的作用。比如说1:1，一次insert用了一次hit，然后就被替换了，这完全违背了使用QC的初衷。不过还有更糟的，比如0.34:1,一次都没用上，就被prune掉了…… 本例的QCHits不低，hit/insert却不高。再考虑到内存使用和碎片情况也还可以。或许真的有必要换成type2的DEMAND了~~

__ Table Locks _________________________________________________________

Waited 0 0/s %Total: 0.00

Immediate 1.63M 2.1/s

表锁报表：47-49行 表锁报表包括两行，一行是总数，一行是当前数。锁等待对于数据库来说永远是糟糕的事情。第三列的总比值反应了一个综述的情况，无论如何不能高过10%，否则肯定就带来一大堆的索引和慢查询问题！

__ Tables ______________________________________________________________

Open 135 of 2000 %Cache: 6.75

Opened 521 0.0/s

表报表：51-53行 也是两行，一行是当前mysql打开表的个数、表缓存的使用率，一行是mysql运行以来的平均值。 这里有两个值比较重要。一个是表缓存使用率，哪怕高到100%都行。不过要是真高到100%了，可能你的’table_cache’设置已经不够了，赶紧加大吧。第二个是当前打开表的比率，这个也能协助判断’table_cache’设置是否合理。一般这个值应该小于每秒1次。不过一个负载比较高而又运行的还不错的mysql，可能能达到每秒打开7次表，依然保持100%的表缓存~

__ Connections _________________________________________________________

Max used 37 of 2000 %Max: 1.85

Total 14.53k 0.0/s

连接数报表：55-57行 如果最大连接数曾经接近过100%，请加大’max_connection’设置。不过事实上，默认的100已经足够绝大多数哪怕相当繁忙的mysql使用了，盲目加大这个设置其实不对。一个mysql链接持续1秒钟，100个就是足足100秒。所以如果连接数太高，或者说一直在慢慢涨，问题很可能在别的地方，比如慢查询、糟糕的索引、甚至DNS解析太慢。在修改这个数的事情，还是先去研究一下为什么100个还不够呢？ 至于每秒连接数，只要mysql运行正常，高低无所谓。不过大多数mysql这个值在每秒5次以下

__ Created Temp ________________________________________________________

Disk table 45.90k 0.1/s

Table 1.67M 2.2/s Size: 512.0M

File 26 0.0/s

临时表报表：59-62行 mysql可以在内存、磁盘甚至临时文件上创建临时表。这三种情况分别对应下面的三条报告。这些数据没有标准值，不过必须要知道的是磁盘上的临时表示最慢的。mysql一般也避免在磁盘上创建临时表，除非达到了’tmp_table_size’的阀值。这个阀值会显示在内存(Table)那行的Size列后面。至于内存和临时文件的数值到底该多少，取决于mysql数据库的硬件配置。

__ Threads _____________________________________________________________

Running 3 of 9

Cached 24 of 28 %Hit: 99.74

Created 38 0.0/s

Slow 0 0/s

线程、中断、流量报表：64-76行 这三个报表是最重要的，系统级别的问题不用多说了都…… 这里面有一个需要注意的地方：66行的线程命中率。每个mysql的连接都是一个单独的线程。mysql启动时，只创建不多的几个线程和一个线程缓存，以节省不断创建和销毁线程的开销，哪怕这个开销不怎么明显。当mysql的连接数超过了线程缓存数(由’thread_cache_size’定义)时，MySQL开始出现线程抖动(‘thread thrash’)。为了接纳新的连接，mysql疯狂的创建新线程，结果自然是线程命中率大幅下滑。 线程抖动是问题么？Yahoo的Jeremy Zawondy在博客中写了如下一段话： 所以上面这个故事的教训就是：如果你的服务器老是接受一些快速连接，想办法加大你的线程缓存吧，直到你的show status命令里’threads_created’不再飙升为止！你的CPU绝对会感谢你的。线程缓存不是啥大问题。可是你解决完真的大问题后，它就是最大的问题了……(汗) 所以，如果真出现线程抖动的话，加大’thread_cache_size’吧。 比如本例，线程缓存命中率只有可怜的0.05%！基本意味着每个连接都是新创建了线程。不过看看报表其他的内容，这也是必然的：缓存里一个线程都没有(66行)，201个线程被创建(67行),202个连接(57行)……

__ Aborted _____________________________________________________________

Clients 187 0.0/s

Connects 12.89k 0.0/s

__ Bytes _______________________________________________________________

Sent 5.83G 7.5k/s

Received 370.77M 479.7/s

__ InnoDB Buffer Pool __________________________________________________

Usage 73.45M of 7.86G %Used: 0.91

Read hit 100.00%

Pages

Free 510.36k %Total: 99.09

Data 4.66k 0.91 %Drty: 0.00

Misc 37 0.01

Latched 0.00

Reads 1.20G 1.6k/s

From file 1.22k 0.0/s 0.00

Ahead Rnd 0 0/s

Ahead Sql 0/s

Writes 290.67k 0.4/s

Flushes 99.48k 0.1/s

Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________

Waits 4 0.0/s

Current 0

Time acquiring

Total 9 ms

Average 2 ms

Max 3 ms

__ InnoDB Data, Pages, Rows ____________________________________________

Data

Reads 1.33k 0.0/s

Writes 205.19k 0.3/s

fsync 182.03k 0.2/s

Pending

Reads 0

Writes 0

fsync 0

Pages

Created 1.53k 0.0/s

Read 3.13k 0.0/s

Written 99.48k 0.1/s

Rows

Deleted 1.04k 0.0/s

Inserted 25.13k 0.0/s

Read 7.65G 9.9k/s

Updated 18.03k 0.0/s

四、mysqlreport 指标参考