22&23丨MySQL：数据库级监控及常用计数器解析

最新推荐文章于 2024-03-12 10:53:12 发布

weixin_44294159

最新推荐文章于 2024-03-12 10:53:12 发布

阅读量205

点赞数

分类专栏：性能测试文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_44294159/article/details/131088307

版权

22丨MySQL：数据库级监控及常用计数器解析（上）

数据库是一个非常大的话题，我们在很多地方，都会看到对数据库的性能分析会包括以下部分。

但其实呢，以上这些内容都是我们应该具备的基础知识，所以我今天要讲的就是，具备了这些基础知识之后我们应该干什么事情。

也就是说，从性能瓶颈判断分析的角度入手，才是性能从业人员该有的逻辑。每次我分析一个性能问题时，逻辑总是这样的：

先画出整个系统的架构图。
列出整个系统中用到了哪些组件。这一步要确定用哪些监控工具来收集数据，具体的内容你可以看下之前讲到的监控设计相关的内容。
掌握每个组件的架构图。在这一步中需要列出它们的关键性能配置参数。
在压力场景执行的过程中收集状态计数器。
通过分析思路画出性能瓶颈的分析决策树。
找到问题的根本原因。
提出解决方案并评估每个方案的优缺点和成本。

这是我一直强调的分析决策树的创建逻辑。有了这些步骤之后，即使不熟悉一个系统，你也可以进行性能分析。

对于 MySQL 数据库来说，我们想对它进行分析，同样也需要看它的架构图。如下图所示（这是 MySQL5 版本的架构示意图）：

这里就有一个问题了：看架构图是看什么？这个图够细吗？

首先，看架构图，一开始肯定是看大而全的架构。比如说上图，我们知道了，MySQL 中有 Connection Pool、SQL Interface、Parser 等这些大的模块。

其次，我们得知道这些模块的功能及运行逻辑。比如说，我们看到了这些模块之后，需要知道，当一个 SQL 通过 Connection Pool 进到系统之后，需要先进入 SQL Interface 模块判断这个语句，知道它是一个什么样的 SQL，涉及到了什么内容；然后通过 Parser 模块进行语法语义检查，并生成相应的执行计划；接着到 Optimizer 模块进行优化，判断走什么索引，执行顺序之类的；然后就到 Caches 中找数据，如果在 Caches 中找不到数据的话，就得通过文件系统到磁盘中找。

这就是一个大体的逻辑。但是知道了这个逻辑还不够。还记得前面我们说的对一个组件进行“全局—定向”的监控思路吧。

这里我们也得找工具实现对 MySQL 的监控，还好 MySQL 的监控工具非常多。

在讲 MySQL 的监控工具之前，我们先来了解下 MySQL 中的两个 Schema，分别是information_schema和performance_schema 。

为什么呢？

information_schema保存了数据库中的所有表、列、索引、权限、配置参数、状态参数等信息。像我们常执行的show processlist;就来自于这个 schema 中的 processlist 表。

performance_schema提供了数据库运行时的资源消耗情况，它以较低的代价收集信息，可以提供不少性能数据。

所以这两个 Schema 对我们来说就非常重要了。

你没事的时候，也可以查一下它们相关的各个表，一个个看着玩。监控工具中的很多数据来自于它们。

还有两个命令是你在分析 MySQL 时一定要学会的：SHOW GLOBAL VARIABLES;和SHOW GLOBAL status;。前一个用来查看配置的参数值，后一个用来查询状态值。当你没有其他工具可用的时候，就可以用这两个命令的输出结果来分析。对于全局监控来说，这两个命令绝对够用。

对于 MySQL 的监控工具有很多，但我主要讲的是以下几个工具：

mysqlreport、pt-query-digest、mysql_exportor+Prometheus+Grafana。

今天我们先来说一下 mysqlreport。

全局分析：mysqlreport

这个工具执行之后会生成一个文本文件，在这个文本文件中包括了如下这些内容。

我觉得这个工具是属于既不浪费资源，又能全局监控 MySQL 的很好的工具。

在我们执行性能场景时，如果想让 mysqlreport 抓取到的数据更为准确，可以先重启一下数据库。如果你觉得重启数据库这个动作实在是有点大，可以先把状态计数器、打开表、查询缓存等数据给刷新一下。

我认为 mysqlreport 有一些重要的知识点需要你知道，在这里我找一个例子给你解释一下。

索引报表


_ Key _________________________________________________________________
Buffer used     5.00k of   8.00M  %Used:   0.06
  Current       1.46M            %Usage:  18.24

请注意，这里所指的 Key Buffer 是指 MyISAM 引擎使用的Shared Key Buffer，InnoDB 所使用的Key Buffer不在这里统计。

从上面的数据来看，MySQL 每次分配的Key Buffer最大是 5K，占 8M 的 0.06%，还是很小的。下一行中的数据可以看到的是当前只用了 1.46M，占 8M 的 18.24%。

显然这个 Key Buffer 是够用的，如果这个使用率高，你就得增加key_buffer_size的值了。

操作报表


__ Questions ___________________________________________________________
Total         126.82M    32.5/s
  +Unknown     72.29M    18.5/s  %Total:  57.00
  Com_         27.63M     7.1/s           21.79
  DMS          26.81M     6.9/s           21.14
  COM_QUIT     45.30k     0.0/s            0.04
  QC Hits      38.18k     0.0/s            0.03
Slow 2 s        6.21M     1.6/s            4.90  %DMS:  23.17  Log:
DMS            26.81M     6.9/s           21.14
  SELECT       20.73M     5.3/s           16.34         77.30
  INSERT        3.68M     0.9/s            2.90         13.71
  UPDATE        1.43M     0.4/s            1.13          5.33
  DELETE      983.11k     0.3/s            0.78          3.67
  REPLACE           0       0/s            0.00          0.00
Com_           27.63M     7.1/s           21.79
  admin_comma  11.86M     3.0/s            9.35
  set_option   10.40M     2.7/s            8.20
  commit        5.15M     1.3/s            4.06

从这个数据可以看到的信息量就有点大了，它可以反应出来这个数据库现在忙不忙。

从 32.5 每秒的操作量上来说，还是有点忙的。你还可以看到下面有操作数的细分，其实我不太愿意看下面的这些细分，描述上除了QC Hits和DMS的意思比较清晰之外，其他的几个值理解起来比较费劲。我也不建议你看下面那几个，因为它们对性能分析来说没起到什么正向的作用。

而 Slow 那这一行就很重要了，从这行可以看出slow log的时间是设置为 2 秒的，并且每秒还出现 1.6 个的慢日志，可见这个系统的 SQL 的慢日志实在是有点多。

DMS部分可以告诉我们这个数据库中各种 SQL 所占的比例。其实它是具有指向性的，像我们的这个例子中，显然是SELECT多，那如果要做 SQL 优化的话，肯定优先考虑SELECT的语句，才会起到立竿见影的效果。

查询和排序报表


__ SELECT and Sort _____________________________________________________
Scan            7.88M     2.0/s %SELECT:  38.04
Range         237.84k     0.1/s            1.15
Full join       5.97M     1.5/s           28.81
Range check   913.25k     0.2/s            4.41
Full rng join  18.47k     0.0/s            0.09
Sort scan     737.86k     0.2/s
Sort range     56.13k     0.0/s
Sort mrg pass 282.65k     0.1/s

这个报表具有着绝对的问题指向性。这里的Scan（全表扫描）和Full join（联合全表扫描）在场景执行过程中实在是太多了，这显然是 SQL 写得有问题。

Range 范围查询很正常，本来就应该多。

查询缓存报表


__ Query Cache _________________________________________________________
Memory usage  646.11k of   1.00M  %Used:  63.10
Block Fragmnt  14.95%
Hits           38.18k     0.0/s
Inserts         1.53k     0.0/s
Insrt:Prune    2.25:1     0.0/s
Hit:Insert    24.94:1

在这部分中，我们看的关键点是，Query Cache没用！因为各种query都没有缓存下来。同时这里我们还要看一个关键值，那就是Block Fragment，它是表明Query Cache碎片的，值越高，则说明问题越大。

如果你看到下面这样的数据，就明显没有任何问题。


__ Query Cache ______________________________________________________
Memory usage   38.05M of 256.00M  %Used:  14.86
Block Fragmnt   4.29%
Hits           12.74k    33.3/s
Inserts        58.21k   152.4/s
Insrt:Prune  58.21k:1   152.4/s
Hit:Insert     0.22:1

这个数据明显看到缓存了挺多的数据。Hits 这一行指的是每秒有多少个 SELECT 语句从Query Cache中取到了数据，这个值是越大越好。

而通过Insrt:Prune的比值数据，我们可以看到 Insert 远远大于 Prune（每秒删除的Query Cache碎片)，这个比值越大就说明Query Cache越稳定。如果这个值接近 1：1 那才有问题，这个时候就要加大Query Cache或修改你的 SQL 了。

而通过下面的Hit:Insert的值，我们可以看出命中要少于插入数，说明插入的比查询的还要多，这时就要去看这个性能场景中是不是全是插入了。如果我们查看了，发现 SELECT 语句还是很多的，而这个比值又是 Hit 少，那么我们的场景中使用的数据应该并不是插入的数据。其实在性能场景的执行过程中经常这样。所以在性能分析的过程中，我们只要知道这个值就可以了，并不能说明Query Cache就是无效的了。

表信息报表


__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate         996     0.0/s


__ Tables ______________________________________________________________
Open             2000 of 2000    %Cache: 100.00
Opened         15.99M     4.1/s

这个很明显了，表锁倒是不存在。但是你看现在table_open_cache已经达到上限了，设置为 2000，而现在已经达到了 2000，同时每秒打开表 4.1 个。

这些数据说明了什么呢？首先打开的表肯定是挺多的了，因为达到上限了嘛。这时候你会自然而然地想到去调table_open_cache参数。但是我建议你调之前先分析下其他的部分，如果在这个性能场景中，MySQL 的整体负载就会比较高，同时也并没有报错，那么我不建议你调这个值。如果负载不高，那再去调它。

连接报表和临时表


__ Connections _________________________________________________________
Max used          521 of 2000      %Max:  26.05
Total          45.30k     0.0/s


__ Created Temp ________________________________________________________
Disk table    399.77k     0.1/s
Table           5.81M     1.5/s    Size:  16.0M
File            2.13k     0.0/s

这个数据连接还完全够用，但是从临时表创建在磁盘（Disk table）和临时文件（File) 上的量级来说，还是有点偏大了，所以，可以增大tmp_table_size。

线程报表

__ Threads _____________________________________________________________
Running            45 of   79
Cached              9 of   28      %Hit:  72.35
Created        12.53k     0.0/s
Slow                0       0/s


__ Aborted _____________________________________________________________
Clients             0       0/s
Connects            7     0.0/s


__ Bytes _______________________________________________________________
Sent          143.98G   36.9k/s
Received       21.03G    5.4k/

当 Running 的线程数超过配置值时，就需要增加thread_cache_size。但是从这里来看，并没有超过，当前配置了 79，只用到了 45。而这里 Cached 的命中%Hit是越大越好，我们通常都希望在 99% 以上。

InnoDB 缓存池报表


__ InnoDB Buffer Pool __________________________________________________
Usage           1.87G of   4.00G  %Used:  46.76
Read hit      100.00%
Pages
  Free        139.55k            %Total:  53.24
  Data        122.16k                     46.60 %Drty:   0.00
  Misc            403                      0.15
  Latched                                  0.00
Reads         179.59G   46.0k/s
  From file    21.11k     0.0/s            0.00
  Ahead Rnd         0       0/s
  Ahead Sql                 0/s
Writes         54.00M    13.8/s
Flushes         3.16M     0.8/s
Wait Free           0       0/s

这个部分对 MySQL 来说是很重要的，innodb_buffer_pool_size为 4G，它会存储表数据、索引数据等。通常在网上或书籍里，你能看到有人建议将这个值设置为物理内存的 50%，当然这个值没有绝对的，还要在具体的应用场景中测试才能知道。

这里的Read hit达到 100%，这很好。

下面还有些其他的读写数据，这部分的数据将和我们在操作系统上看到的 I/O 有很大关系。有些时候，由于写入的过多，导致操作系统的I/O wait很高的时候，我们不得不设置innodb_flush_log_at_trx_commit参数（0：延迟写，实时刷；1：实时写，实时刷；2：实时写，延迟刷）和sync_binlog 参数（0：写入系统缓存，而不刷到磁盘；1：同步写入磁盘；N：写 N 次系统缓存后执行一次刷新操作）来降低写入磁盘的频率，但是这样做的风险就是当系统崩溃时会有数据的丢失。

这其实是我们做测试时，存储性能不高的时候常用的一种手段，为了让 TPS 更高一些。但是，你一定要知道生产环境中的存储是什么样的能力，以确定在生产环境中应该如何配置这个参数。


__ InnoDB Lock _________________________________________________________
Waits          227829     0.1/s
Current             1
Time acquiring
  Total     171855224 ms
  Average         754 ms
  Max            6143 ms

这个信息就有意思了。显然在这个例子中，锁的次数太多了，并且锁的时间都还不短，平均时间都能达到 754ms，这显然是不能接受的。

那就会有人问了，锁次数和锁的平均时间多少才是正常呢？在我的经验中，锁平均时间最好接近零。锁次数可以有，这个值是累加的，所以数据库启动时间长，用得多，锁次数就会增加。

InnoDB 其他信息


__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads        35.74k     0.0/s
  Writes        6.35M     1.6/s
  fsync         4.05M     1.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0


Pages
  Created      87.55k     0.0/s
  Read         34.61k     0.0/s
  Written       3.19M     0.8/s


Rows
  Deleted     707.46k     0.2/s
  Inserted    257.12M    65.9/s
  Read        137.86G   35.3k/s
  Updated       1.13M     0.3/

这里的数据可以明确告诉你的一点是，在这个性能场景中，插入占有着绝对的量级。

总结

好了，我们拿一个 mysqlreport 报表从上到下看了一遍之后，你是不是觉得对 MySQL 有点感觉了？这里我给一个结论性的描述吧：

在这个性能场景中，慢日志太多了，需要定向监控看慢 SQL，找到慢 SQL 的执行计划。

在这个插入多的场景中，锁等待太多，并且等待的时候又太长，解决慢 SQL 之后，这里可能会解决，但还是要分析具体的原因的，所以这里也是指向了 SQL。

这里为什么要描述得这么细致呢？主要是因为当你看其他一些工具的监控数据时，分析思路是可以共用的。

但是有人说这里还有一个问题：SQL 怎么看？

其实对于我们分析的逻辑来说，在数据库中看 SQL 就是在做定向的分析了。请你不要相信一些人所吹嘘的那样，一开始就把所有的 SQL 执行时间统计出来，这真的是完全没有必要的做法。因为成本太高了。

在下一篇文章里，我们换个工具来看看 SQL 的执行时间到底应该怎么分析。

SQL 的执行时间到底应该怎么分析。

23 | SQL：数据库级监控及常用计数器解析（下）

上一篇文章中，我们讲了有关数据库的全局分析，那么在今天的文章中，我们继续看看在数据库中，如何做定向分析。

还记得我在上篇文章中提到的工具吗？mysqlreport、pt-query-digest 和 mysql_exportor+Prometheus+Grafana。我们在上一篇中已经讲完了 mysqlreport，今天我们来看看剩下的这几个。

定向抓取 SQL：pt-query-digest

pt-query-digest是个挺好的工具，它可以分析slow log、general log、binary log，还能分析 tcpdump 抓取的 MySQL 协议数据，可见这个工具有多强大。pt-query-digest属于 Percona-tool 工具集，这个 Percona 公司还出了好几个特别好使的监控 MySQL 的工具。

pt-query-digest分析 slow log 时产生的报告逻辑非常清晰，并且数据也比较完整。执行命令后就会生成一个报告。

我来稍微解释一下这个报告。我们先看这个报告的第一个部分：


# 88.3s user time, 2.5s system time, 18.73M rss, 2.35G vsz
# Current date: Thu Jun 22 11:30:02 2017
# Hostname: localhost
# Files: /Users/Zee/Downloads/log/10.21.0.30/4001/TENCENT64-slow.log.last
# Overall: 210.18k total, 43 unique, 0.26 QPS, 0.14x concurrency _________
# Time range: 2017-06-12 21:20:51 to 2017-06-22 09:26:38
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        118079s   100ms      9s   562ms      2s   612ms   293ms
# Lock time            15s       0     7ms    71us   119us    38us    69us
# Rows sent          1.91M       0  48.42k    9.53   23.65  140.48    2.90
# Rows examine      13.99G       0   3.76M  69.79k 101.89k  33.28k  68.96k
# Rows affecte       3.36M       0   1.98M   16.76    0.99   4.90k       0
# Query size       102.82M       6  10.96k  512.99  719.66  265.43  719.66

从上表中可以看得出来，在这个慢日志中，总执行时间达到了 118079s，平均执行时间为 562ms，最长执行时间为 9s，标准方差为 612ms。

可见在此示例中，SQL 执行还是有点慢的。

这时也许会有人问，SQL 执行多长时间才是慢呢？之前在一个金融机构，我跟一个做核心系统的团队讨论他们的 SQL 执行时间指标。他们判断之后说，希望 SQL 平均执行时间指标定在 500ms。我说，你们要 500ms，那前面还有一连串的节点才能到达最终的用户，如果每个环节都这样要求自己，那最终的用户不就明显感觉到很慢了吗？

经过一轮轮的讨论，最后定在了 100ms 以内。

其实从我的经验上来看，对于大部分实时的业务，一个 SQL 执行的平均时间指标定在 100ms 都多了。但是对性能来说就是这样，在所有的环节中都没有固定的标准，只有经验数据和不断演化的系统性能能力。

我们再接着分析上面的数据。再来看pt-query-digest给出的负载报表：


# Profile
# Rank Query ID           Response time    Calls  R/Call V/M   Item
# ==== ================== ================ ====== ====== ===== ===========
#    1 0x6A516B681113449F 73081.7989 61.9%  76338 0.9573  0.71 UPDATE mb_trans
#    2 0x90194A5C40980DA7 38014.5008 32.2% 105778 0.3594  0.20 SELECT mb_trans mb_trans_finan
#    3 0x9B56065EE2D0A5C8  3893.9757  3.3%   9709 0.4011  0.11 UPDATE mb_finan
# MISC 0xMISC              3088.5453  2.6%  18353 0.1683   0.0 <40 ITEMS>

从这个表中可以看到，有两个 SQL 的执行时间占了总执行时间的 94%，显然这两个 SQL 是要接下来要分析的重点。

我们再接着看这个工具给出的第一个 SQL 的性能报表：


# Query 1: 0.30 QPS, 0.29x concurrency, ID 0x6A516B681113449F at byte 127303589
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.71
# Time range: 2017-06-16 21:12:05 to 2017-06-19 18:50:59
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         36   76338
# Exec time     61  73082s   100ms      5s   957ms      2s   823ms   672ms
# Lock time     19      3s    20us     7ms    38us    66us    29us    33us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine  36   5.06G   3.82k 108.02k  69.57k 101.89k  22.70k  68.96k
# Rows affecte   2  74.55k       1       1       1       1       0       1
# Query size    12  12.36M     161     263  169.75  192.76   11.55  158.58
# String:
# Databases    db_bank
# Hosts        10.21.16.50 (38297/50%)... 1 more
# Users        user1
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #########################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `db_bank` LIKE 'mb_trans'\G
#    SHOW CREATE TABLE `db_bank`.`mb_trans`\G
UPDATE mb_trans  
    SET 
    resCode='PCX00000',resultMes='交易成功',payTranStatus='P03',payRouteCode='CMA'  
    WHERE 
    seqNo='20170619PM010394356875'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  
    resCode='PCX00000',resultMes='交易成功',payTranStatus='P03',payRouteCode='CMA' from mb_trans where  
    seqNo='20170619PM010394356875'\G

从查询时间分布图上来看，这个语句的执行时间在 100ms~1s 之间居多，95% 的执行时间在 2s 以下。那么这个 SQL 就是我们接下来要调优的重点了。

第二个 SQL 我就不赘述了，因为逻辑是完全一样的。

通过对慢日志的分析，我们可以很快知道哪个 SQL 是慢的了。当然你用mysqldumpslow分析，也会得到一样的结果。

SQL 剖析：profiling

在分析数据库的性能时，显然对 SQL 的分析是绕不过去的一个环节。但是我之前也说过了，上来就对 SQL 进行全面剖析也是不合逻辑的，因为 SQL 那么多，如果对每个 SQL 都进行详细的执行步骤解析，显然会拖慢整个系统，而且，对一些执行快的 SQL 进行分析也没有什么必要，徒增资源消耗。

通过上面的分析过程，我们已经定位到了具体是哪个 SQL 执行得慢，那么下面就是要知道 SQL 的执行细节。无论是在 Oracle 还是在 MySQL 中，我们都要去看执行计划。

比如说下面这样的：

上图中select_type是子句类型的意思，有简单有复杂，但是它不能说明什么成本的问题。在这里，最重要的内容是 type，因为 type 可以告诉你访问这个表的时候，是通过什么样的方式访问的。上图中的 ALL 是全表扫描的意思。type 还有如下几个值：

执行计划中的possible_keys会列出可能使用到的索引值。key 这一列会列出执行时使用到的索引值。

以上信息就是 MySQL 的执行计划中比较重要的部分了。这些信息可以帮助我们做 SQL 的分析，为优化提供证据。

除了执行计划外，MySQL 还提供了profiling，这个有什么用呢？它可以把 SQL 执行的每一个步骤详细列出来，从一个 SQL 进入到数据库中，到执行完这整个生命周期。

MySQL 的profiling在session级生效，所以当你用了慢日志，知道哪个 SQL 有问题之后，再用这个功能是最见成效的。如果想一开始就把所有session的SQL profiling功能打开，那成本就太高了。

下面我来详细解释一下 profiling 的用法和功能。

profiling 操作步骤

profiling 的操作步骤比较简单，如下所示：

步骤一 ：set profiling=1;  //这一步是为了打开profiling功能
步骤二 ：执行语句           //执行你从慢日志中看到的语句
步骤三 ：show profiles;    //这一步是为了查找步骤二中执行的语句的ID
步骤四 ：show profile all for query id; //这一步是为了显示出profiling的结果

我们实际执行一下上面的步骤：


// 步骤一：打开profiling功能
mysql> set profiling=1;
Query OK, 0<

最低0.47元/天解锁文章

weixin_44294159

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
22&23丨MySQL：数据库级监控及常用计数器解析

好了，我们拿一个 mysqlreport 报表从上到下看了一遍之后，你是不是觉得对 MySQL 有点感觉了？这里我给一个结论性的描述吧：在这个性能场景中，慢日志太多了，需要定向监控看慢 SQL，找到慢 SQL 的执行计划。在这个插入多的场景中，锁等待太多，并且等待的时候又太长，解决慢 SQL 之后，这里可能会解决，但还是要分析具体的原因的，所以这里也是指向了 SQL。这里为什么要描述得这么细致呢？主要是因为当你看其他一些工具的监控数据时，分析思路是可以共用的。
复制链接

扫一扫