22丨MySQL：数据库级监控及常用计数器解析（上）
下载APP
关闭
渠道合作
推荐作者
22丨MySQL：数据库级监控及常用计数器解析（上）
2020-02-10 高楼 来自北京
《性能测试实战30讲》
课程介绍
![](data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" width="1142" height="640"><rect fill-opacity="0"/></svg>)
讲述：高楼
时长29:21大小26.89M
数据库是一个非常大的话题，我们在很多地方，都会看到对数据库的性能分析会包括以下部分。
但其实呢，以上这些内容都是我们应该具备的基础知识，所以我今天要讲的就是，具备了这些基础知识之后我们应该干什么事情。
也就是说，从性能瓶颈判断分析的角度入手，才是性能从业人员该有的逻辑。每次我分析一个性能问题时，逻辑总是这样的：
先画出整个系统的架构图。
列出整个系统中用到了哪些组件。这一步要确定用哪些监控工具来收集数据，具体的内容你可以看下之前讲到的监控设计相关的内容。
掌握每个组件的架构图。在这一步中需要列出它们的关键性能配置参数。
在压力场景执行的过程中收集状态计数器。
通过分析思路画出性能瓶颈的分析决策树。
找到问题的根本原因。
提出解决方案并评估每个方案的优缺点和成本。
这是我一直强调的分析决策树的创建逻辑。有了这些步骤之后，即使不熟悉一个系统，你也可以进行性能分析。
对于 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 是指 MyISAM 引擎使用的Shared Key Buffer，InnoDB 所使用的Key Buffer不在这里统计。
从上面的数据来看，MySQL 每次分配的Key Buffer最大是 5K，占 8M 的 0.06%，还是很小的。下一行中的数据可以看到的是当前只用了 1.46M，占 8M 的 18.24%。
显然这个 Key Buffer 是够用的，如果这个使用率高，你就得增加key_buffer_size的值了。
操作报表
从这个数据可以看到的信息量就有点大了，它可以反应出来这个数据库现在忙不忙。
从 32.5 每秒的操作量上来说，还是有点忙的。你还可以看到下面有操作数的细分，其实我不太愿意看下面的这些细分，描述上除了QC Hits和DMS的意思比较清晰之外，其他的几个值理解起来比较费劲。我也不建议你看下面那几个，因为它们对性能分析来说没起到什么正向的作用。
而 Slow 那这一行就很重要了，从这行可以看出slow log的时间是设置为 2 秒的，并且每秒还出现 1.6 个的慢日志，可见这个系统的 SQL 的慢日志实在是有点多。
DMS部分可以告诉我们这个数据库中各种 SQL 所占的比例。其实它是具有指向性的，像我们的这个例子中，显然是SELECT多，那如果要做 SQL 优化的话，肯定优先考虑SELECT的语句，才会起到立竿见影的效果。
查询和排序报表
这个报表具有着绝对的问题指向性。这里的Scan（全表扫描）和Full join（联合全表扫描）在场景执行过程中实在是太多了，这显然是 SQL 写得有问题。
Range 范围查询很正常，本来就应该多。
查询缓存报表
在这部分中，我们看的关键点是，Query Cache没用！因为各种query都没有缓存下来。同时这里我们还要看一个关键值，那就是Block Fragment，它是表明Query Cache碎片的，值越高，则说明问题越大。
如果你看到下面这样的数据，就明显没有任何问题。
这个数据明显看到缓存了挺多的数据。Hits 这一行指的是每秒有多少个 SELECT 语句从Query Cache中取到了数据，这个值是越大越好。
而通过Insrt:Prune的比值数据，我们可以看到 Insert 远远大于 Prune（每秒删除的Query Cache碎片)，这个比值越大就说明Query Cache越稳定。如果这个值接近 1：1 那才有问题，这个时候就要加大Query Cache或修改你的 SQL 了。
而通过下面的Hit:Insert的值，我们可以看出命中要少于插入数，说明插入的比查询的还要多，这时就要去看这个性能场景中是不是全是插入了。如果我们查看了，发现 SELECT 语句还是很多的，而这个比值又是 Hit 少，那么我们的场景中使用的数据应该并不是插入的数据。其实在性能场景的执行过程中经常这样。所以在性能分析的过程中，我们只要知道这个值就可以了，并不能说明Query Cache就是无效的了。
表信息报表
这个很明显了，表锁倒是不存在。但是你看现在table_open_cache已经达到上限了，设置为 2000，而现在已经达到了 2000，同时每秒打开表 4.1 个。
这些数据说明了什么呢？首先打开的表肯定是挺多的了，因为达到上限了嘛。这时候你会自然而然地想到去调table_open_cache参数。但是我建议你调之前先分析下其他的部分，如果在这个性能场景中，MySQL 的整体负载就会比较高，同时也并没有报错，那么我不建议你调这个值。如果负载不高，那再去调它。
连接报表和临时表
这个数据连接还完全够用，但是从临时表创建在磁盘（Disk table）和临时文件（File) 上的量级来说，还是有点偏大了，所以，可以增大tmp_table_size。
线程报表
当 Running 的线程数超过配置值时，就需要增加thread_cache_size。但是从这里来看，并没有超过，当前配置了 79，只用到了 45。而这里 Cached 的命中%Hit是越大越好，我们通常都希望在 99% 以上。
InnoDB 缓存池报表
这个部分对 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 锁报表
这个信息就有意思了。显然在这个例子中，锁的次数太多了，并且锁的时间都还不短，平均时间都能达到 754ms，这显然是不能接受的。
那就会有人问了，锁次数和锁的平均时间多少才是正常呢？在我的经验中，锁平均时间最好接近零。锁次数可以有，这个值是累加的，所以数据库启动时间长，用得多，锁次数就会增加。
InnoDB 其他信息
这里的数据可以明确告诉你的一点是，在这个性能场景中，插入占有着绝对的量级。
总结
好了，我们拿一个 mysqlreport 报表从上到下看了一遍之后，你是不是觉得对 MySQL 有点感觉了？这里我给一个结论性的描述吧：
在这个性能场景中，慢日志太多了，需要定向监控看慢 SQL，找到慢 SQL 的执行计划。
在这个插入多的场景中，锁等待太多，并且等待的时候又太长，解决慢 SQL 之后，这里可能会解决，但还是要分析具体的原因的，所以这里也是指向了 SQL。
这里为什么要描述得这么细致呢？主要是因为当你看其他一些工具的监控数据时，分析思路是可以共用的。
但是有人说这里还有一个问题：SQL 怎么看？
其实对于我们分析的逻辑来说，在数据库中看 SQL 就是在做定向的分析了。请你不要相信一些人所吹嘘的那样，一开始就把所有的 SQL 执行时间统计出来，这真的是完全没有必要的做法。因为成本太高了。
在下一篇文章里，我们换个工具来看看 SQL 的执行时间到底应该怎么分析。
思考题
最后给你留两道思考题吧，MySQL 中全局监控工具可以给我们提供哪些信息？以及，如何判断 MySQL 状态值和配置值之间的关系呢？
欢迎你在评论区写下你的思考，也欢迎把这篇文章分享给你的朋友或者同事。
分享给需要的人，Ta购买本课程，你将得18元
生成海报并分享赞 5
提建议
© 版权归极客邦科技所有，未经许可不得传播售卖。 页面已增加防盗追踪，如有侵权极客邦将依法追究其法律责任。
上一篇
21丨Tomcat：中间件监控及常用计数器解析
下一篇
23丨MySQL：数据库级监控及常用计数器解析（下）
精选留言(13)
蔡森冉2020-03-24看到这里真是测试方向从一个坑到另一个坑的过程，没有开发基础真的不行，前两天刚搞jmeter+influxdb++grafana，接着是找了系统监控相关的代码和中间件基本上不了解，然后到数据库。这个过程中数据库中数据拿到如何看看完这篇了解了一些。但是回想一下前面几个怎么分析又完全被不知道了。分析每一个方面的需要关注的数据如何分析，这成为了最难的学习内容，就像老师常说工具不重要，关键得到我们想要的，但是对我来说就是图形数据出来了，但是对我就是一堆数据，分析过程关注点，还有场景不同会有什么结果，数据什么场景，正常数据应该是什么样，脑中十万个为什么。。。展开作者回复: 你在开窍的前夜，只需要往前走一小步即可。坚持吧。
11
Geek_f932342020-02-11MySQL 中全局监控工具可以给我们提供哪些信息？ 索引报表、操作报表、查询和排序报表、查询缓存报表、表锁报表、表信息报表、连接报表和临时报表、线程报表、innodb缓存池报表、innodb锁报表、 如何判断 MySQL 状态值和配置值之间的关系呢？ SHOW GLOBAL VARIABLES;用来查看配置的参数值，和SHOW GLOBAL status;用来查询状态值 测试结束后通过mysql监控工具查看和分析状态值，从而判断数据库配置值是否合理展开作者回复: 非常正确。
11
LQQ2020-06-30老师 请教一个问题，文中提到的状态计数器、打开表、查询缓存等数据给刷新一下，具体怎么刷新？作者回复: flush 命令，你查一下。
4
新思维2020-03-15看完文章觉得对一个性能测试工程师的要求太高了，需要把报表中的每个参数的意思都得搞懂，达到了对DBA的要求作者回复: 是对一个性能测试团队的要求。 如果一个人足够努力也可以做得到的。
4
johnny2021-06-24老师，我是这样理解的，理解不对的地方老师帮我纠正一下。 如何判断 MySQL 状态值和配置值之间的关系呢？ 状态值和配置值： 状态值也称为状态计数器，可以通过show global status命令来查看有哪些状态计数器。 配置值也称为配置参数，可以通过show global variables命令来查看有哪些配置参数。 两者关系： 状态值一定程度上反映了配置值的合理性。 比如Max_used_connections是一个状态值，它反映了配置值max_connections的合理性； 在比如Open_tables是一个状态值，它反映了配置值table_open_cache的合理性，但是不能看到Open_tables过高就去调整table_open_cache，因为有可能是其它原因导致的。 所以说分析状态值超过既定指标的原因，我们除了要分析配置值外，还要分析sql语句或者其它组件。展开作者回复: 理解得非常对。
1
若丶相依2020-10-17运维看的津津有味。作者回复: 只要是技术都是相通的，职位嘛，那是21世纪才有的东西。哈哈。
1
月亮和六便士2020-04-25老师，一条SQL语句（插入语句）单执行特别快，高并发很慢，这种情况是不是锁等待造成的？还是老规矩看执行计划？作者回复: 可不，还是老规矩。
1
廖志勇2022-10-23 来自广东你好，老师 ，有两个问题请教下： 1、mysqlreport 是否需要管理员权限（网上没有找到相关资料）。我们数据库管的严，管理源权限不开放，查询出来的结果始终不变的，比如开启慢查询了，但是慢查询比例一直为0。但是查apm工具，明显有慢sql 2、我们的数据库服务器有个多个项目数据库，mysqreport 只能是查询整个数据库服务器的数据指标？是否可以统计特点的项目库指标？展开作者回复: 1. 有性能字典表查询权限的数据库用户就可以。 有慢sql但又看不到，可能是因为设置的阈值不同。 2. 好像不能。
问号和感叹号2022-07-04 来自北京mysqlreport怎么安装呢 windows和linux下都尝试过 各种不成功。。。作者回复: 这个......那个......，还是问下度娘吧。
- jy2021-05-18老师，请教下：文中“在这个性能场景中，慢日志太多了，需要定向监控看慢 SQL，找到慢 SQL 的执行计划”，是不是需要先把慢查询日志开启？设置慢查询的阈值？ 这样才可以在全局监控中看到有慢日志
作者回复: 慢查询日志是每个mysql都应该在部署时开启的。在我们这个项目中一开始就把这些需要监控的点做了梳理了。
- Geek_f9e0e52021-03-11oracle就看AWR么
作者回复: 不止，还有其他的监控工具可以看。选择自己喜欢的就行。
共 2 条评论 
餘生2020-03-20我在公司测试用elk就可以监控到慢查询的语句，还有其他监控工具能看到主从库延迟那些是不是就够了作者回复: 可以的，工具无所谓，目的达到就好。
小老鼠2020-03-041，模糊查询的SQL为select * from paper where title like ‘%var%’，这个是全表扫描的，性能肯定不好，有没有更好的解决方案？2,对于数据库读写分离的架构，有什么办法提高性能？作者回复: 如果你非要这样做，就换数据库吧，没必要用mysql。
共 2 条评论
