阿里开发实录：慢 SQL 拖垮数据库并引发故障

本文结合一个实际故障案例出发，从小白的视角分析慢 SQL 是如何打垮数据库并引发故障的。

一、案发现场

上午 9:49，应用报警：4103.ERR_ATOM_CONNECTION_POOL_FULL，应用数据库连接池满。

上午 9:49-10:08 期间，陆续出现 4200.ERR_GROUP_NOT_AVALILABLE、4201.ERR_GROUP_NO_ATOM_AVAILABLE、4202.ERR_SQL_QUERY_TIMEOUT 等数据库异常报警。

由于数据库承载了销售核心的用户组织权限功能，故障期间，期间销售工作台无法打开，大量小二反馈咨询。

上午 10:08，定位到有应用基础缓存包升级发布，上午 9 点 40 刚完成最后一批发布，时间点相吻合，尝试通过打开缓存开关，系统恢复。

二、现场结论

对此次升级缓存包应用发布内容分析，发现升级的某个二方包中，删除了本地缓存逻辑，直接请求 DB，而本次升级没有对请求的 SQL 进行优化，如下代码所示，该 SQL 从 Oracle 迁移到 MySQL，由于数据库性能的差异，最终造成慢查询，平均一次执行 2S 多，大量慢 SQL 最终打挂数据库。

SELECT CRM_USER_ID AS LOGIN_ID, CRM_ROLE_ID AS ROLE_NAME, CRM_ORG_ID AS ORG_ID  , CONCAT(CRM_USER_ID, '#', CRM_ROLE_ID, '#', CRM_ORG_ID) AS URO  , CONCAT(CRM_ORG_ID, '#', CRM_ROLE_ID) AS ORG_ID_ROLE_NAMEFROM CRM_USER_ROLE_ORGWHERE IS_DELETED = 'n'  AND CONCAT(CRM_ORG_ID, '#', CRM_ROLE_ID) = '123#abc'ORDER BY ID DESC;

经过讨论排查分析，得出以下结论：

1. 之前逻辑走本地内存，本次升级中由于涉及到的某个二方库代码变更删除了本地逻辑改查 DB。

2. 查询 DB 的 SQL 去 O 阶段没有进行优化，在 MySQL 下为慢 SQL，大量慢 SQL 查询拖垮了数据库。

三、进一步的疑问

面对上述结论，作为数据库方面的小白，有以下几个疑问，感觉需要深入挖掘：

1. 这条 SQL 为何是慢 SQL；

2. 发布的应用为非核心应用，只是与登录权限共用了一个数据库，当时发布应用的 QPS 只有 0.几，为何可以把库打挂；

3. 之前已经申请过一波连接池扩容，从 10 扩到了 15，发布的应用线上有流量的机器不过 7 台，为何可以把数据库压垮；

4. 事后复盘，发布前一天灰度时也有过慢 SQL，为何当时没有压垮数据库；

四、深入分析原理

带着以上疑问，结合以下相关知识，一层层剥开深层次的原因

4.1、慢 SQL 分析

CONCAT(CRM_ORG_ID, '#', CRM_ROLE_ID) = '123#abc' 

该 SQL 由工具直接从 Oracle 翻译过来的

• 虽然两个拼接的字段各自都有索引，但是使用函数后，MySQL 是不会使用索引的，退化为了普通查询

• 由于表数据量较大，全表 40W+数据，导致扫描行数很多，平均扫描 16W 行、逻辑读 38W 行，执行 2s 左右

4.2、业务代码排查

• 故障后第二天，有个别销售反馈页面打开较慢，有好几秒，怀疑是止血时的操作是切到了 tair 而不是回滚到本地缓存逻辑导致，不过此时还是有疑问，为何一个页面会慢好几秒呢，听起来就像是一次请求大量循环调用缓存导致；

• 代理账号经定位，确实是如上假设，此处的业务代码逻辑为查找组织下的指定角色，会递归遍历所有子组织，最差情况下，一次页面请求，会有 1000+次访问缓存/DB；

• 结合数据库当时慢 SQL 趋势，符合我们的猜测，虽然业务流量不大，但是每次请求会放大 1000 倍，最终导致问题 SQL 执行了 1.5W+次，同时同下图可以看到，其他正常 SQL 由于系统忙被排队，响应也变慢，而这些基本都是基础用户组织权限相关，所以造成了业务系统不可用；

4.3、druid 连接池？数据库连接池？

• 上文背景处有说道，应用连接池配置的为 15，切应用流量本身很小，那么是什么原因导致整个库都被拖垮呢；

• 这里要从一次 SQL 请求的链路说起，如下图所示：

• 应用层通过 tddl 访问 MySQL 数据源，其中连接管理是在 atom 层，利用 druid 进行连接池的管理，我们平时所说的 tddl 线程池，指的就是 druid 连接池，这个配置维护在 diamond 中，一般有 dba 来设置；

• 对于我们的应用来说，单个应用 7 台机器，maxPoolSize 配置为 15，数据库是单库单表，则单个应用的最大连接数为 1*15，所有应用连接数为 7*1*15=105；

• 注意以上只是应用维度的连接数推导，正常工作下连接池也不会达到 max 的，如果达到了，TDDL 会抛出 4103.ERR_ATOM_CONNECTION_POOL_FULL，应用数据库连接池满错误，与第一节现象吻合；

• 那么问题来了，这么几个连接，为何打垮整个数据库呢；看数据库的最大连接数可是有 8000；

• 经与 DBA 同学咨询，了解到了数据库 server 端的内部处理线程池与 druid 没有任何关系，两者是两个层面的东西，所以需要研究下 server 端的处理逻辑；

• 同时这里提一点，由于我们很多应用都在连接数据库，所以需要评估下数据库的最大连接数是否可以满足这么多应用的机器的链接，即应用服务器总数 * 一个数据库实例上的分库数(atom) * maxPoolSize < max_user_connections；

4.4、MySQL 数据库性能分析

• 如上图所示，案发时，有一个明显的现象，数据库 CPU 被打满，同时活跃连接数增长、数据库 rt 增长：

• 活跃连接数，当前数据库中有多少会话正在执行 SQL，是衡量数据库繁忙程度的指标；

• 根据执行时间来判断，阈值一般很低，正常情况下一条 SQL 执行很快，活跃会话很低；

• 经过与 DBA 同学请教，结合慢 SQL 明细，确定问题就是慢 SQL 执行，大量逻辑读导致的；

• server 端的链路还原如下：

• 针对数据库实例，出于保护，引入了线程池，通过参数进行控制，默认 300 个左右；

• 正常情况下，由于 SQL 执行很快，活跃会话不会很高；

• 慢 SQL 情况下，由于每一条慢 SQL 都会逻辑读 30W+行，执行 2s+，导致线程变慢，线程池里的线程被用尽，也即活跃会话数上升，如上图所示，逼近 280；

• 数据库 CPU 都在执行慢 SQL 的逻辑读和排序等操作、活跃会话数大幅增长，数据库 rt 飙升，导致其他应用请求无法与数据库建立新的会话，应用请求超时，TDDL 层进入 fast fail 状态，抛出 4201.ERR_GROUP_NO_ATOM_AVAILABLE 异常；

4.5、慢 SQL 是如何导致 CPU 打满的

• 上面分析中，有一个核心的细节，就是慢 SQL 逻辑读太多，最终导致 CPU 打满了，这里有个疑问，读写不是 io 操作吗，为什么会使 CPU load 高呢，研究了一下 innodb 的结构：

• 如下图所示，在 innodb 存储层，维护了一个缓存数据和索引信息到内存的存储区叫做 buffer pool，他会将最近访问的数据缓存到缓冲区；

• 我们说的逻辑读：也就是 SQL 在同一时间内需要访问多少个缓冲区的内存页；

• 而与之相对应的，物理读则是同一时间内需要从磁盘获取多少个数据块；

• 理想情况下，buffer pool size 应该设置的尽可能大，这样就可以减少进程的额外分页，当 buffer pool size 设置的足够大的时候，整个数据库就相当于存储在内存当中，当读取一次数据到 buffer pool size 以后，后续的读操作就不用在进行磁盘读；

• 通过现场的 cloud dba 监控也可以发现，innoDB 缓存命中率为 100%，不存在物理读的情况，也即可以认为数据库的高频数据都已全量存在于内存中了，通过查看数据库容量也可以佐证这个观点，数据+索引大小 6G 左右，未达到数据库实例配置的 8G，所以实际情况是数据都存在于内存中了，并不会有多余的 IO 操作，CPU 的性能全部都消耗在了大量的内存数据扫表(逻辑读)中 - 看现场平均扫描 16w 行数据；

• 这个结论让我不禁想到了一个类似的场景，在线上 vi 打开服务器中大文件导致 load 飙高，应用不可用的问题。vi 在将文件原样加载到内存后，还会将其转换为内部结构（线条，单词等），使用内部脚本语言执行语法高亮显示等等，所有这些都会消耗内存和 CPU 时间。

• 虽然慢 SQL 模板只一个，QPS 也不是特别高(现场峰值 100 左右，平均 20 左右)，但是由于线程池机制，快速将活跃会话(线程池)占满；

• 由于业务 QPS 水位在 3000 左右，线程池打满后，后续即使是索引甚至是主键查询的正常 SQL，也都在排队了，最终导致了雪崩效应；

4.6、关于数据库升配

• 在优化慢 SQL 的同时，考虑到数据库实例配置较低(8core 8G 100G)，也尝试与 DBA 相关同学沟通升级配置，经过多次讨论，结论为升配无用，只能优化慢 SQL 或者加缓存，以下配置核心的三个维度进行说明；

• CPU：目前实例配置为 8 核，但是 MySQL 集群的 CPU 隔离是放开的，最高可用到物理机的 64 核，所以无需升配；

• 内存：目前实例配置为 8G，内存这块直接影响的就是上述介绍的 buffer pool，如 4.5 小结分析，库总数据量都没有达到 8G，所以无需进行内存升级；

• 磁盘：目前实例配置为 100G，以目前业务发展速度也已够用；

五、总结

• 直接原因：应用升级二方库中本地缓存代码被删除；慢 SQL 没有优化；同时业务逻辑复杂，嵌套循环导致快速雪崩；

• 根本原因：慢 SQL 导致数据库 CPU 打满，活跃连接数突增，rt 上升，后续 SQL 请求都在排队，高 QPS 场景下，最终导致雪崩效应，TDDL fast fail，抛出数据库不可用异常；

• 两个池的知识点：TDDL 中的 druid 插件维护的是业务应用层与数据库连接的连接池；MySQL 服务端 也会通过线程池技术，处理会话，默认 300 左右，一般情况下 SQL 执行非常快，所以活跃线程/活跃会话非常低；

• 快速定位数据库问题的思路 -- 熟练掌握 cloud dba 的性能分析工具：

• 判断数据库是否正常：RT - 响应时间是否变大了；活跃会话 - 数据库当前是否拥堵了；

• QPS：是否有突发大流量；

• 检查执行的 SQL：如果逻辑读、DML 大幅增长，则基本可以锁定该 SQL 出了问题。

原文：https://mp.weixin.qq.com/s/XmPdG7E8xGXdZVBg7M81_w

如果感觉本文对你有帮助，点赞关注支持一下，想要了解更多 Java 后端，大数据，算法领域最新资讯可以关注我公众号【架构师老毕】私信 666 还可获取更多 Java 后端，大数据，算法 PDF+大厂最新面试题整理+视频精讲