一次调优的流水账

最近一直在做一个在线题库的项目,遇到了挺棘手的问题,主要原因是有人反映当问卷中的问题较多时获取问卷的速度过慢,下面主要记录一下排查思路与解决方案。

首先第一个怀疑的肯定是查询语句索引没有加上,导致查询过慢,由于使用了 Sequelize,我们修改一下配置,让 Sequelize 输出他所生成的 SQL 语句,再进行 explain 就好啦。

explain 的结果很显然,有一些字段没有加上索引,整个查询的效率相对是比较低的。

这里就踩到了第一个坑,Sequelize 在建立 n:m 关系的中间表时只会建立联合主键,但是如果中间表中还有额外字段需要读取,那么最终生成的语句会分别对两个外键进行查询。

举例来说,问卷有多个问题,问题也可以属于多个问卷,同时问题在问卷中的顺序需要固定。那么中间表就需要 问卷Id,问题Id,问题在问卷中的序号Order,这三个字段。而 Sequelize 只会建立 问卷Id和问题Id 的联合主键,但是我们在排序的过程中,需要取出 Order 到最外层,这破坏了 Sequelize 本身的机制,导致最终生成的语句会分别索引 问卷Id 和 问题Id,拖慢整个查询进度。

好了,找到了问题直接解决就行,根据 explain 的提示,在需要排序的中间表中分别对两个外键单独加上索引。使所有的 type 都为 const/ref/eq_ref 即可。这样效率应该就有了足够的保障了。

现在我们加上索引了,应该会很快了吧,workbranch 中显示的查询时间是 0.4s,应该来说是完全可以接受了。

问题并没有解决,测试环境加上索引之后,获取速度仍然惨不忍睹,于是我们远程登陆了测试数据库所在的服务器,执行了上文提到的 SQL,虽然经过了大量的输出刷屏,但是最终给出的执行时间依旧是 0.4s。

不是 SQL 的问题了,那是否是测试环境的问题?于是我们远程登陆了测试环境的服务器,执行了上文提到的 SQL,问题出现了,最终的执行时间是 25s。

很好,能够复现的问题就是好问题。

这个问题也很显然了。登录测试数据库进行连接使用的是 socket,而测试环境远程连接使用的是 TCP/IP,毫无疑问是传输网速不够导致的。那我们简单测量一下这次返回集的大小吧。我们在测试数据库所在的服务器上使用 tcpdump 抓了一下包,本地使用 wireshark 分析了一下,结论是,这个返回集大约有 30M。

这就很奇怪了,阿里云内网默认是千兆带宽,折算下来的话,再怎么说 30M 也不需要跑 25 秒吧?难道是阿里云内网出了问题?

虽然不太可能,但是我们还是测试一下吧。我们在数据库服务器上使用 dd 生成了一个 30M 的文件,然后在测试环境的机器上使用 scp 复制到本地,速度很正常,30M 的文件没有任何延迟就复制成功了。这说明内网网速是没有问题的。

那现在只有一种可能了,是 MySQL 本身对于大结果集的传输有 bug,不能跑满网速,而且由于本地环境下运行正常,所以判断是 TCP/IP 的连接上出了问题。那就尝试验证一下吧。

思路也比较清晰,我们选用一些能够提升 TCP 性能的方式,看看总体速度是否有提升。我们准备开启 BBR 来看看性能是否有提升。

由于服务器内核版本较低,我们采用本地环境进行尝试。果然,开启 BBR 后相较于未开启的场景,性能有了显著的提升。

另一方面,我们开启了 MySQL 的结果压缩功能,这个 25s 的传输被压缩到了5s,但是负面影响就是压缩有额外开销,导致所有的查询速度都被拉到了 1s 左右,这可以说是完全不能接受的。

这样就陷入了死局。由于烂 SQL 语句过多。导致这个 bug 经常会被人误认为是 SQL 语句性能低下所导致的,回答区里也是一大片”你 explain 一下看看结果”,”加一下索引就好了” 这种强答。我们既没有找到解决方案,也没有能力去读源码。所以我们选了一个很偷的办法,购买阿里云的 RDS 服务。既然这是个 MySQL 的 bug,那么 RDS 应该也是有同样的问题的。然后我们只需要发工单吐槽就可以了,让阿里的人去 push 这件事。

然后我就遇到了更神奇的事情。阿里云的 RDS 上没有这个 bug。至少 MySQL 上从命令行看是完全正常工作的。(但是同样在 RDS 上出售的 MariaDB 还是正常的复现了这个 bug)

这就很有意思了。难道是最新版的 MySQL 修复了这个问题?为了验证,我们现场编译了一份最新版的 MySQL,嗯,broken as expected。这说明确实是阿里云动手修复了这个 bug。

好,那在我们没有能力处理这个问题的时候,我们把数据库搬到 RDS 是不是就可以暂时解决了?说干就干。我们先购买了一个 RDS 部署了测试环境,然后信心满满的打开了页面。

问卷获取速度没有丝毫改变。依旧慢的吓人。这就非常奇怪了,难道数据传输也不是瓶颈?那怎么解释 25s 的命令行查询呢?

思考了很久才觉得,可能是另一个操作和 MySQL 的传输一样慢,只不过两者在同步进行,所以最初没有调试到,而当 MySQL 的传输问题解决后,这个隐藏的问题才暴露出来。

那首先想到的当然就是 Sequelize 的组装数据了。但是很快这点就被排除了。理由很简单,使用 socket 进行连接时,bug 不存在。也就是说,这个 bug 一定是由于网络传输导致的。

那既然排除了发送端的问题,那会不会是接收端的问题呢?由于 Sequelize 使用的底层库是 mysql2,我们手动起了一个 mysql2,裸跑了一下那个很重的 SQL,嗯,broken as expected。看来是驱动有问题。作为对比,我们起了一个 go 的 native 驱动执行了同样的语句,效率是正常的。

然而知道了这点并没有什么卵用。Sequelize 不支持切换底层包,mysql2 是使用了 buffer 进行分块接收,应该是出于稳定性的考虑,也不会轻易修改。那我们只能另想办法了。

之前的需求中有一个对学生的考卷做备份的需求,所以我们有一份 MongoDB 的问卷备份库,由于不需要大量的 join,所以性能相当可靠,基本上数据可以做到秒出,那么这次的解决思路很自然的就是进行读写分离了。

好了,我们现在需要明确一下需求。我们需要关注的点按重要性排序:

  • 客户端获取最新问卷的速度
  • 后台更新的可靠性
  • 后台编辑问卷请求的提交速度
  • 后台获取问卷的速度
  • MongoDB 的体积
  • 代码改动

我们先假设我们有一套完整的备份系统,我们套入场景看看这个系统需要实现什么功能。

  • 第一点,我们的备份系统应该能实现,用户获取问卷时不会触发备份机制,即此刻备份中的内容一定已经与 MySQL 同步完成。
  • 第二点,由于生成备份操作过重,可能会失败,所以我们需要有机制确保即使备份生成失败也不会产生无法预估的影响。
  • 第三点,提交修改请求后不应该在当前请求中生成备份,导致修改请求被阻塞。
  • 第四点,我们的备份系统应该在获取问卷之前完成备份操作。
  • 第五点,我们的备份系统多次备份时应该只有一份存档。
  • 第六点,我们是猛男,不用考虑这个。

行了,上面这些点列出来之后,明显有一些是互相冲突的。其中最核心的一点就是,备份系统应该在何时进行备份。我们把所有可能的情况列出来:

  • 获取问卷的请求发起前(与第 1、4 条冲突)
  • 提供一个上架操作,在上架操作中备份,学生只能访问已上架问卷(与第 4 、6条冲突)
  • 任意一次更新请求之后(与第 3 条冲突)
  • 我们在更新完成后立刻返回,并在后台进行备份(与第 2 条冲突)

看起来没有办法完美解决问题了,那我们只能比较这几种方案的牺牲程度了。很显然,方案2的牺牲更小,我们决定采用方案2。

OK,敲定了方案,我们需要确定具体实现了。

那么第一步,我们需要知道问卷信息何时被修改了,否则我们会生成很多的重复备份,不仅增加了 MongoDB 的存储开销,更重要的是生成备份这个重操作应该被尽可能的避免。

由于问卷信息量过大,显然是不适合进行 diff 的。最终我们选择引入版本号的概念,问卷的所有修改行为都会使 MySQL 中版本号+1(通过事务确保修改与版本号增加行为绑定),这样,我们只需要比对 MongoDB 备份库中的最新版本和 MySQL 中的版本号是否相同即可知道问卷是否被修改过。

OK,这样整个方案已经能够跑起来了,但是显然,目前的方案中在问卷没有上架时有很多对 MySQL 的读取行为(编辑问卷),这是可以被优化的。

优化的思路也很清晰,既然你是重复读取,那么我就改写一下获取问卷的接口就好:如果当前的问卷已被修改(基于版本号判断),那么我就在从 MySQL 中获取完数据之后进行一次备份更新,如果没有修改的话就直接返回备份即可。这个优化也可以部分缓解上文的第四条行为(毕竟备份这个重操作是无法避免的,只能尽可能的减少次数)。

OK,对照需求表,我们还剩下一个 MongoDB 体积没有进行优化,更新无非就是两件事,增加新的备份,移除旧的备份。核心关注点在于更新中途的请求处理以及并发时不会同时多次生成备份。

更新中途的请求很好处理,只需要先新增,后删除即可,查询的时候只需要在 MongoDB 中选取版本号最大的一个就好。

单例化处理由于涉及到多节点同步的问题,所以不能像本机单例那样处理,直接说思路吧,目前多机共享的空间只有数据库,所以我们在 Redis 中使用 SETNX 命令来写入标记,如果标记存在,则等待标记被清除,如果标记不存在则执行备份流程。

看起来一切都解决了?但是目前还有一个问题,就是由于备份过重,可能会出错,甚至被杀死进程,我们需要一种机制来确保即使备份流程被杀也不影响其他进程。

综合考虑下,我们准备采用看门狗的模式来确保正常运转。简单来说,就是在 SETNX 命令之后,我们使用 EXPIRE 为标记位设定一个过期时间,在我们执行备份的过程中使用 setInterval 来不断地重置过期时间,如果备份进程意外消失,那么,由于看门狗也跟着消失了,所以标记位就会被 Redis 自动清除,第二个备份进程就会被调起执行。

OK,到这里整个调优的流程就结束了。这次主要还是记一下流水账,没什么特殊的东西。

以上。

发表评论

电子邮件地址不会被公开。

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据