MySQL全局分析工具：mysqlreport

mysqlreport可以将MySQL中一些重要的状态变量变成为一个易读的报告。通过这份报告，我们就能快速地查看MySQL服务器的各种性能指标，而不用根据show status的值进行大量、繁琐的计算。

一、安装

# MySQL 8.0
sudo apt-get install libdbi-perl libdbd-mysql-perl -y

cd /tmp && git clone https://github.com/daniel-nichter/hackmysql.com.git
sudo cp ./hackmysql.com/mysqlreport/mysqlreport /usr/bin/

# MySQL 5.7
sudo apt-get install libdbi-perl libdbd-mysql-perl -y

sudo apt-get install mysql-client-5.7 -y

二、使用

# mysqlreport --user root --password 123456 --host 127.0.0.1 --port 3306 --no-mycnf --flush-status --outfile myreport-$(date +%Y%m%dT%H%M%S)
$ mysqlreport --help
mysqlreport v3.5 Apr 16 2008
mysqlreport makes an easy-to-read report of important MySQL status values.

Command line options (abbreviations work):
   --user USER       Connect to MySQL as USER
   --password PASS   Use PASS or prompt for MySQL user's password
   --host ADDRESS    Connect to MySQL at ADDRESS
   --port PORT       Connect to MySQL at PORT
   --socket SOCKET   Connect to MySQL at SOCKET
   --no-mycnf        Don't read ~/.my.cnf
   --infile FILE     Read status values from FILE instead of MySQL
   --outfile FILE    Write report to FILE
   --email ADDRESS   Email report to ADDRESS (doesn't work on Windows)
   --flush-status    Issue FLUSH STATUS; after getting current values
   --relative X      Generate relative reports. If X is an integer,
                     reports are live from the MySQL server X seconds apart.
                     If X is a list of infiles (file1 file2 etc.),
                     reports are generated from the infiles in the order
                     that they are given.
   --report-count N  Collect N number of live relative reports (default 1)
   --detach          Fork and detach from terminal (run in background)
   --help            Prints this
   --debug           Print debugging information

Visit http://hackmysql.com/mysqlreport for more information.

参数说明：

• –infile表示用文件中读取show status值，而不是从MySQL中获取。

• –outfile表示将报告输出到屏幕后，再输出的文件中。

• –email表示将报告输出到屏幕后，再通过电子邮件发送到指定地址。此选项要求sendmail位于/usr/sbin/，因此在Windows上不起作用。

• –flush-status表示在生成报告后执行“flush status”语句。

• –relative用于设置报告的生成间隔。mysqlreport通常生成的报告显示了MySQL服务器在整个正常运行时间内的值。–relative选项使mysqlreport生成与以前的报告相关的报告。如果–relative选项为X指定了一个整数值，那么mysqlreport将从MySQL服务器生成相隔X秒的相对报告。报告数由–report count选项控制。默认值为1个相对报告。例如，给定选项–relative 60，mysqlreport将生成2个报告：第一个报告将立即生成；这是开始报告或基线报告。第二个报告将在60秒后生成。第二个报告的值将相对于开始报告。例如，假设在开始的报告中总共有10.00个问题。然后假设在60秒的时间间隔内，MySQL服务器又回答了1k个问题。第二份报告将显示总共1.00k个问题，而不是11.00k个问题。如果给–relative选项指定了一个内嵌列表（类似于–infile选项所使用的列表），那么mysqlreport将仅从给定的内嵌中按给定内嵌的顺序生成相关报告。给定列表中的内嵌名称应使用空格分隔；例如：file1 file2等。在命令行中按正确的时间顺序指定内嵌很重要：先指定旧的内嵌。第一个内嵌应具有系统变量值（来自“显示变量”或“手动包含”）。内嵌可以有一组或多组MySQL SHOW STATUS值。但是请注意，“mysqladmin-r-inextended”的输出将不起作用，因为带有-r选项的mysqladmin已经将SHOW STATUS值相对化。 因为mysqlreport首先将报告写入临时文件，所以当–relative与整数（而不是与infies一起使用）一起使用时，mysqlreport将说明它正在写入哪个临时文件。这样，人们就可以在撰写相关报告时观察其进展情况。

• –report-count表示生成多少个报告。此选项仅适用于–relative，当为–relative指定一个整数值时。mysqlreport实际上生成N+1个报告：第一个报告是开始报告或基线报告。然后，生成N个相关报告。

• –detach表示后台运行。此选项将导致mysqlreport分叉，从终端分离，并在后台继续运行。分叉之后，mysqlreport将说明它正在写入哪个临时文件。此选项还需要选项–outfile或–email。如果既没有给出–outfile也没有给出–email，那么报告就会被删除，因为因为mysqlreport从终端分离，报告无法打印到终端。此选项与option–relative一起使用，这样mysqlreport就可以长时间收集相关报告，而不必使用控制终端（即用户登录）。例如，可以通过运行mysqlreport在一个小时内捕获一个相对报告，并将整个报告通过电子邮件发送给自己

三、输出解读

3.1、key - 索引报表

该部分显示了MyISAM引擎的shared key buffer的使用情况，如下：

__ Key _________________________________________________________________
Buffer used     3.00k of  16.00M  %Used:   0.02
  Current       2.92M            %Usage:  18.26
Write hit       0.00%
Read hit       50.00%

可以看到，当前的key_buffer_size为16M。在过去的一段时间中，MySQ单次使用的Key Buffer最大为3k，占总大小的0.02%，当该比值大于等于80%时，可以考虑调大key_buffer_size。需要注意的是，该值永远不可能大于95%，因为mysql内部的数据结构也会占用shared key buffer，但mysqlreport无法统计到它们。此外，还可以看到，当前总共使用了2.92M的Key Buffer，占总大小的18.26%。

Write hit和Read hit，分别指索引的写命中率和读命中率。对于索引的写命中率，在写操作（update、insert等）多的场景下命中率可能接近0%，在读操作（select等）多的场景下命中率可能高于90%，而当命中率小于0%时，表示MySQL经常将索引写入磁盘中。对于索引的读命中率，当命中率低于99%时，表示Key Buffer过小，大量索引需要从磁盘读取。

3.2、questions - 操作报表

该部分显示了MySQL Server正在忙些什么以及有多忙，其中包括MySQL的各种操作和MySQL协议通信。如下：

__ Questions ___________________________________________________________
Total          98.06k   47.46/s
  DMS          81.23k   39.32/s  %Total:  82.84
  QC Hits      16.58k    8.02/s           16.91
  COM_QUIT        200    0.10/s            0.20
  Com_            131    0.06/s            0.13
  -Unknown         82    0.04/s            0.08
Slow 5 s            0    0.00/s            0.00  %DMS:   0.00  Log:  ON
DMS            81.23k   39.32/s           82.84
  SELECT       64.44k   31.19/s           65.72         79.33
  INSERT       16.75k    8.11/s           17.08         20.61
  UPDATE           41    0.02/s            0.04          0.05
  REPLACE           0    0.00/s            0.00          0.00
  DELETE            0    0.00/s            0.00          0.00
Com_              131    0.06/s            0.13
  change_db       119    0.06/s            0.12
  show_fields       9    0.00/s            0.01
  show_status       2    0.00/s            0.00

在Total部分是MySQL对所有操作的总计，以及这些操作自MySQL Server启动以来的速率。这些总计数据包含如下内容：

• DMS，即数据操作语句。主要包括了select、insert、replcate、update、delete等操作。在一个长时间运行的系统中，该部分应该是占比最高的。
• QC Hits，即查询缓存命中率，是指MySQL从查询缓存中检索结果而不是执行实际的查询获取结果的次数
• COM_QUIT，是一些可忽略的、不重要的操作。
• Com_，是MySQL处理的各种命令，通常与协议相关。理想情况下，该值应该很低，过高则说明MySQL一直在空转。
• Unknown，是一些未知的操作。通常，该值应该很低。

在Slow部分显示了MySQL执行的慢查询数量。如上，执行时间超过5s秒的操作将被视为慢查询，当前总计到了0条慢查询。%DMS表示数据操作语句在慢查询中的比重。LOG表示是否将慢查询记录到日志中。

在DMS部分则显示了数据操作语句详细分布情况。

在Com_部分则显示了MySQL处理的各种命令的分布情况。

3.3、select and sort - 查询和排序报表

该部分显示了各种类型的select语句的详细分布情况，如下：

__ SELECT and Sort _____________________________________________________
Scan               38    0.02/s %SELECT:   0.06
Range              14    0.01/s            0.02
Full join           3    0.00/s            0.00
Range check         0    0.00/s            0.00
Full rng join       0    0.00/s            0.00
Sort scan          14    0.01/s
Sort range         26    0.01/s
Sort mrg pass       0    0.00/s

其中，Scan表示全表扫描，Range表示范围查询，Full join表示联合全表扫描。正常情况下，应该有很多的Range范围查询，而如果Scan和Full join过多，则说明SQL写得有问题。

3.4、query cache - 查询缓存报表

该部分显示了查询缓存的使用情况，如下：

__ Query Cache _________________________________________________________
Memory usage   17.81M of  32.00M  %Used:  55.66
Block Fragmnt  13.05%
Hits           16.58k    8.02/s
Inserts        48.50k   23.48/s
Prunes         33.46k   16.20/s
Insrt:Prune    1.45:1    7.28/s
Hit:Insert     0.34:1

在Memory usage部分显示了查询缓存的内存使用情况。当查询缓存占用了大量的内存时，会体现在Prunes部分。

在Block Fragmnt部分显示了查询缓存所使用内存的碎片情况。该值是可用缓存块与总块的百分比。值越大，说明碎片越多。10%到20%是平均水平。如上示例，碎片率是13.05%，这个可以接受的，但可以尝试调整query_cache_min_res_unit参数，看看碎片率是否会下降。

在Hits、Inserts、Prunes部分显示了对查询缓存的操作情况。其中，Hits表示命中查询缓存的次数、Inserts表示在查询缓存中插入新数据的次数，Prunes表示删减查询缓存中数据的次数。

在Insert:Prunes部分显示了查询缓存的波动性。Insert大于Prunes，说明查询缓存稳定；Insert小于Prunes，说明查询缓存易失。查询缓存稳定表示缓存经常被使用，查询缓存易失表示缓存大小过小（此时增大缓存大小即可）或MySQL试图缓存所有的东西（此时可以调整查询缓存的使用类型调整为2，默认为1）。

在Hit:Insert部分显示了查询缓存的有效性。在理想的情况下，MySQL应该在查询缓存中插入一堆稳定的查询，然后直接从查询缓存中获取结果。因此，如果查询缓存的有效性高，那么Hit应该大于Insert。当Insert大于或等于Hit时，表示查询缓存不稳定。

3.5、Table Locks - 表锁报表

该部分显示了MySQL锁的使用情况，如下：

__ Table Locks _________________________________________________________
Waited          1.01k    0.49/s  %Total:   1.24
Immediate      80.04k   38.74/s

在Waited部分显示了MySQL必须等待才能获得表锁的数量。该值应该尽可能低，等待表锁的百分比不应超过10%，较高的百分比意味着表或查询索引不佳或者查询速度较慢。

在Immediate部分显示了MySQL立即获得表锁的数量。

3.6、tables - 表报表

在该部分显示了表的使用情况，如下：

__ Tables ______________________________________________________________
Open              107 of 1024    %Cache:  10.45
Opened            118    0.06/s

在Open部分显示了当前打开的表数、可打开的表总数、表缓存的使用率。而在Opened部分则显示了MySQL自启动以来打开的表总数和打开速率。

在这里我们需要知道两件事：首先，表缓存使用率。如果接近100%，那么可以考虑调大table_cache。其次，打开表的速率。它可以用于确定table_cache是否太低。一般来说，这个值应该小于1/s。但是，对于一个繁忙且运行良好的MySQL服务器来说，每秒打开7个表，并以100%的表缓存使用率运行，也是没有问题的。

3.7、Connections - 连接报表

__ Connections _________________________________________________________
Max used           77 of  600      %Max:  12.83
Total             202    0.10/s

在Max used部分显示了当前并发连接数、允许的最大并发连接数、当前并发连接数与允许的最大并发连接数的比率。而在Total部分则显示了MySQL自启动以来建立连接的总数和建立连接的速率。

如果连接使用率接近100%（第一行，第三列），则可能需要调大max_connections系统变量。但我们需要知道的是，对于优化良好的服务器来说，即使在非常繁忙的场景下，采用默认值（100）也是适用的。一个到MySQL的连接应该只持续几秒钟，所以即使100个连接也可以用很长时间。如果最大连接数非常高或者随时间缓慢增加，则问题可能出在其他地方，比如查询速度慢、索引不良或甚至DNS解析速度慢等。因此，在调大max_connections之前，先定位出100个连接不够使用的根本原因，并验证这是一个合理的需求而不是其它问题，然后再进行调整。

关于每秒连接数，这个值可能相当高。事实上，如果该值很高，并且一切运行良好，这也是正常的。通常，超过10个连接/秒是可能的，但大多数服务器的连接/秒都低于5。

3.8、Created Temp - 临时表报表

该部分显示了MySQL临时表的创建情况，如下：

__ Created Temp ________________________________________________________
Disk table         10    0.00/s
Table              26    0.01/s    Size:  4.00M
File                3    0.00/s

MySQL可以在硬盘、RAM和临时文件中创建临时表，分别对应于如上的三行数据。这些值都是相对的，没有一个明确的标准。由于硬盘上的临时表是最慢的（由第一行disk table表示），所以该值应该是三个中最小的一个。只有当MySQL不能在内存（大小由tmp_table_size设置）中创建临时表时，才会在硬盘上创建临时表。tmp_table_size的值由第二行第三列的size指示。理想情况下，应该是在RAM或临时文件中创建临时表。

3.9、Threads - 线程报表

该部分显示了与MySQL线程相关的信息，如下：

__ Threads _____________________________________________________________
Running            55 of   77
Cache               0              %Hit:    0.5
Created           201    0.10/s
Slow                0    0.00/s

__ Aborted _____________________________________________________________
Clients             0    0.00/s
Connects            8    0.00/s

__ Bytes _______________________________________________________________
Sent           38.46M  18.62k/s
Received        7.98M   3.86k/s

这里需要特别注意的是，Cache和%Hit。每个到MySQL的连接都是由单独的线程处理的。在启动时，MySQL会创建一些线程，并将这些线程保存在线程缓存中，这样就不必不断地杀死和创建线程。虽然重建线程的成本并不高，但频繁地线程抖动也不好。当并发连接数超过线程缓存（由系统变量thread_cache_size设置）时，MySQL就会开始线程抖动，即疯狂地创建线程以满足新连接的需求。当发生这种情况时，线程缓存命中率会下降。因此，如果MySQL服务器有线程抖动（即线程缓存命中率很低），可以考虑调大线程缓存大小。

如果你有一个繁忙的服务器，并且有很多快速连接，请将你的线程缓存设置得足够高，以便SHOW STATUS中的Threads_created值停止增加。您的 CPU 会感谢您。…线程缓存确实不是我们最糟糕的问题。但在我们修复了所有较大的问题之后，情况变得更糟。

在这个例子中，线程缓存的命中率非常低，只有0.5%，这意味着几乎每一个新连接都会导致MySQL创建一个新线程。很容易看出原因：Cache行的第一列表示线程缓存中剩余零个线程。因此，应该调大线程缓存大小。此外，需要注意的是，这里的Created行与前面的connections total行之间的相关性，即201个线程创建和202个总连接数之间的相关性。因此，这里的线程缓存命中率几乎为0。

3.10、InnoDB Buffer Pool - InnoDB缓存池报表

我们知道，InnoDB引擎会将索引数据和表数据都缓存到Buffer Pool中，而在其内部实现上，InnoDB Buffer Pool则是由包含不同类型数据的16KB页组成。因此，该部分显示了InnoDB Buffer Pool中页的使用情况，如下：

__ InnoDB Buffer Pool __________________________________________________
Usage           3.95M of   7.00M  %Used:  56.47
Read hit       99.99%
Pages
  Free            195            %Total:  43.53
  Data            249                     55.58 %Drty:   0.00
  Misc              4                      0.89
  Latched           0                      0.00
Reads         574.56k     0.6/s
  From file       176     0.0/s            0.03
  Ahead Rnd         4     0.0/s
  Ahead Sql         2     0.0/s
Writes        160.82k     0.2/s
Flushes         1.04k     0.0/s
Wait Free           0       0/s

我们可以看到，配置的InnoDB Buffer Pool大小为7M，当前使用了3.95M，占总大小的56.47%。

Read hit是指读操作（读索引/读数据）命中缓存的比率。该值应该非常接近100%，在多数情况下，该值大于99.98%。

在Pages部分显示了Buffer Pool的详细使用情况。每一行对应不同类型的缓存页，它们分别是：

• Free，空闲页。是指剩余可用的缓存页。如上，剩余195个缓存页，占Buffer Pool总大小的43.43%。
• Data，数据页。是指缓存索引和数据所使用的页。如上，缓存索引和数据使用了249个缓存页，占Buffer Pool总大小的55.58%，其中包含0%的脏页（已修改但尚未刷新到磁盘的数据页）。
• Misc，杂页。是指因分配了管理任务（如行锁、自适应哈希索引等）而繁忙的数据页。如上，有4个繁忙的页，占Buffer Pool总大小的0.89%。
• Latched，锁页。是指当前正在进行读取或写入操作的页，或者是由于一些原因无法刷新或删除的页。

在Reads部分显示了InnoDB Buffer Pool的读取活动。它们的含义如下：

• 第一行，是缓冲池的读取情况。在繁忙的服务器上，这个值应该很高，因为InnoDB会从位于RAM的缓冲池中读取大部分页面。该值可用于衡量InnoDB缓冲池的写吞吐量。由于InnoDB所需的几乎所有内容都可以保存在缓冲池中，并可以从中检索数据，因此缓冲池的读取速度应该是很快的。如上，当前InnoDB缓冲池的读取速率为0.6k/s，但它超过200k/s也是有可能的。
• 第二行，是指因InnoDB缓冲池无法满足，而从磁盘读取的次数。
• 第三行，是InnoDB发起的随机预读数。当查询随机顺序扫描一个表的部分内容时，就会发生这种情况。
• 第四行，是InnoDB发起的顺序预读数。当查询执行顺序全表扫描时，就会发生这种情况。该值应该尽可能小。

在Writes部分显示了InnoDB Buffer Pool的写入活动。可以看作是InnoDB缓冲池的写吞吐量。该行列出了写缓冲池的次数和速率。在执行了大量的update或insert操作的服务器上，该值可能会很高。

在Flushes部分显示了Buffer Pool页面刷新的请求数。

在Wait Free部分显示了使用Buffer Pool时的等待情况。通常，对InnoDB缓冲池的写操作是在后台进行的。但在读取或创建一个页面，而又没有可用的干净页时，需要等待页面先刷新。如果缓冲池大小设置正确，则该值应该很小。

3.11、InnoDB Lock - InnoDB锁报表

我们知道，MyISAM是表级锁，InnoDB是行级锁。该部分显示了InnoDB行锁的情况，如下：

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

在Waits部分显示了等待锁的次数。该部分最好为零。

在Current部分显示了当前正在等待行锁的数量。该部分最好为零。

在Time acquiring部分显示了在等待行锁时所花费的时间情况，它们的含义分别是：

• Total：等待行锁的总毫秒数
• Average：等待行锁的平均毫秒数
• Max：等待行锁的最大毫秒数

根据经验，锁平均时间最好接近零。锁次数可以有，因为这个值是累加的，所以数据库启动时间长，用得多，锁次数就会增加。

3.12、InnoDB Data、Pages、Rows - InnoDB数据、页、行信息报表

该部分显示了用于衡量InnoDB引擎吞吐量的一些常用指标，如下：

__ InnoDB Data, Pages, Rows ____________________________________________
 Data
   Reads           225     0.0/s
   Writes          799     0.0/s
   fsync           541     0.0/s
   Pending
     Reads           0
     Writes          0
     fsync           0

 Pages
   Created          23     0.0/s
   Read            226     0.0/s
   Written       1.04k     0.0/s

 Rows
   Deleted      25.04k     0.0/s
   Inserted     25.04k     0.0/s
   Read         81.91k     0.1/s
   Updated           0       0/s

在Data部分显示了四个与InnoDB数据相关的操作情况，它们分别是：

• Read，指InnoDB引擎执行的数据读取总次数。
• Writes，指InnoDB引擎执行的数据写入总次数。
• fsync，指InnoDB引擎将数据从RAM刷入磁盘的总次数。通常，该值低于读或写。
• Pending，指当前被挂起的数据读取、写入和同步的总次数。这些值最好为零。

在Pages部分显示了三个与InnoDB缓存页相关的操作情况，它们分别是：

• Created，是指在InnoDB缓存池中创建页的数量和速率；
• Read，是指在InnoDB缓存池中读取页的数量和速率；
• Written，是指在InnoDB缓存池中写入页的数量和速率；

在Rows部分显示了四个与InnoDB表中行相关的操作情况，它们分别是：

• Deleted，是指从InnoDB表中删除的行数量和速率；
• Inserted，是指在InnoDB表中插入的行数量和速率；
• Read，是指从InnoDB表中读取的行数量和速率；
• Updated，是指在InnoDB表中更新的行数量和速率；

四、参考

https://github.com/daniel-nichter/hackmysql.com/tree/master/mysqlreport