【MYSQL优化】MySQL如何在“海啸”下保命

作者：田杰
在数据库的日常使用中，来自应用的高并发场景并不罕见，其标志性的表现为 高新连接创建速率（CPS，比如 PHP 短连接）、发送大量请求到 DB 数据库层。

如同 海啸，大量的新建连接和请求猛烈的冲击考验着 DB 层的处理能力，非常容易出现数据库被冲击 hang 住或响应极其缓慢的情况（想象下无预知无缓冲的短时间内突然工作量翻涨数倍，会不会立时被忙哭了 ^_^）。
而数据库通常作为架构最下端的数据存取汇聚单元，其性能表现和稳定性往往决定了应用的最终表现和使用体验，可谓业务生死之大事，不可不察。
由此，我们一起看一下 “海啸” 场景下可以用来 “保命” 的各种解决方案。
注：
• 本文目标是总结高并发场景下的应对处理方法，而应对热点更新（秒杀）场景的“招数”会另文介绍。
• 本文的主旨在于方便数据库的使用者理解业务高并发请求场景下的保障 DB 可用性和稳定性的机制和方法，非机制的全面深度技术细节介绍。

1. 线程池

1.1 模型

我们举一个生活中的例子方便大家理解 线程池（Thread Pool）。
比如有个银行，有 10 个窗口（实例规格 CPU 数量)，官方说可以容纳 10 人（Client Thread）。平时呢，人也不多，一直顺畅。稍微忙一点呢，大家挤挤。这个 10 人的地方，挤个 50 人也可以（不是每个人时刻都在窗口办业务）。效率也挺高。
年底发工资、公司结算、发行纪念币来了一大帮人，大家一起挤，谁也不让，就把银行挤满了；大家接踵摩肩，动也动不了，再发生些争抢，那这银行谁也办不了业务了。
好了，来个保安（Timer Thread），搞了个队伍机制（10 个队列 loose_thread_pool_size = 10），按规定执行，一次放 10 个人。这个效率也不错。
当然了，如果一下子来了 1000 个人，那么门口等待的队伍会很长，虽然不致于把银行撑爆，但是后面的同学要等很长时间，有的会去抱怨了（应用侧等待超过自身定义的超时时间后返回错误）。
问题来了，有的同学搞不清楚买哪种纪念币，一直在看看停停，保安看他们也不像马上能决定的样子，而且窗口柜员也不是非常忙，保安就又搞了个规则，叫 “stall_limit”。
看一些同学犹豫超过 stall_limit 定义的时间，那么就算他们 stall 了，可以再放 1 个人进去（oversubscribing）。但去窗口办业务的人数是有上限的，最多 50 个人（10 个窗口每个窗口 5 个人， loose_thread_pool_oversubscribe = 4）。
之后，只能出一个，进一个; 如果都不出来，那也 hang 了。这个时候，至少要让保安能进去，把这些太慢的同学赶出来几个，让等待的队列动起来。
还有，有的同学在里面发现忘带证件了，需要等送进来。他们找地方等（lock wait）。那么他们是在等待了，这个是不算 oversubscribing 数量的，所以保安也可以放人，一直放到 thread_pool_max_threads 个人。
如果证件还没送来，那么银行就被这些等证件的霸占了（hang 了）。另外如果一下子证件都送来，那这个银行一下子忙起来，也爆了（热点更新）。
当然如果这个银行没有大量客户同时办业务的场景，是可以不需要搞个保安，不需要搞个队伍的（loose_thread_pool_enabled = OFF)。这个银行本身最多可以 50 个人，但是保安只让 10 个人进去，那效率就会低了。
还有，门口等待队伍长了，这个可以有 3 种可能，
• 顾客动作慢（慢 SQL），建议考虑优化 SQL 降低执行成本。
• 银行小， 窗口数量少（实例规格小）建议扩店（升级实例规格）。
• 窗口动作慢（物理机问题、数据库 bug；不在本文讨论范围内）。
从上面的例子中，我们可以看到 Thread Pool 是通过队列机制限制数据库的 Client Thread 的并发度（控制 Running Thread 数量），避免大量的争抢和创建 Client Thread 的开销来提升 CPU 使用 效率，保障吞吐的（在应用给与 DB 的访问压力不断增加的情况下，保持 DB 吞吐处理能力）。

1.2 适用场景

如果我们仔细品位下上面的例子，可以发现 Thread Pool 的适用场景：
• 每个要办的业务简短（OLTP 场景）且性能瓶颈在 CPU 资源上
• 场景中不存在 大量 需要长时间执行且无停顿（可以暂时不使用 CPU）的 SQL
• 能够接受一定损失（错误/开销）的业务（启用 Thread Pool 后需要一定开销，存在简单的查询比不启用 Thread Pool 的情况下执行时间增加的可能，比如被分配到了 stall 的 thread group 而要花时间等待执行）

1.3 小结

参数	开放修改？	默认值	说明
1	loose_thread_pool_enabled	Yes	是否启用 Thread Pool
2	loose_thread_pool_oversubscribe	Yes	每个 Thread Group 在出现 Stall Thread 的情况下可以额外同时执行（active）的线程个数；线程池最多可以同时执行（active）的线程数 =（thread_pool_oversubscribe + 1）* thread_pool_size；建议 >=3
3	tloose_thread_pool_size	Yes (RDS)	Thread Pool 中分组（Thread Group）的个数，建议设置为实例规格 CPU 个数
4	thread_pool_max_threads	No	Thread Pool 中最大线程数量，到达这个数量后，无法再创建新的 thread
5	thread_pool_idle_timeout	No	Thread Group 中空闲的线程退出前的空闲等待（idle）时间
6	thread_pool_stall_limit	No	Timer Thread 检查 “Stall” 情况的间隔，避免一个 thread 长时间霸占一个 thread group

那么面对存在长时间执行的查询，除了优化 SQL 降低执行成本外（有时不具有可操作性，当然如果该查询对数据时效性不敏感可以考虑转移到只读实例上执行），是否还有其他招数可用？ 请看下一招“限流”。

2. 限流

如果“海啸”来的异常猛烈，并且在“海啸”中能够定义出一批带有同样特征的查询，比如 Redis 缓存被击穿，大量相似重复查询打到 DB 层，或者如上例 Thread Pool 中的长时间执行的查询，那么在业务支持/允许降级的情况下我们可以通过对这批请求采取限流的方式来“保命”。
相对 thread pool 这种对“海啸” 全方位覆盖的应对机制，限流更像是集力量于一点的定向打击。

2.1 Statement Concurrency Control

对于 RDS for MySQL 8.0 和 PolarDB for MySQL，我们可以通过“语句并发控制”（Statement Concurrency Control）特性来实现针对指定语句的限流。
比如发现下面的查询在高并发的场景下拖累了整个实例的性能，和业务核实，业务可以接受该查询被限流。

1. # 高成本慢查询

2. select count(*)

3. from jacky.mytab

4. where cid = 90363

5. or uid = ???

Copy
确定 SQL 语句后，可以根据语句特征来调用 dbms_ccl 工具包创建规则进行限流。

1. # 增加限流规则，限制最多 1 个并发执行

2. call dbms_ccl.add_ccl_rule('select','jacky','mytab',1,'cid=;uid=');

3. # 显示当前的限流规则

4. call dbms_ccl.show_ccl_rule();

5. +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+-----------+

6. | ID | TYPE | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |

7. +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+-----------+

8. | 2 | SELECT | jacky | mytab | Y | N | 1 | 116 | 1 | 26 | cid=;uid= |

9. +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+-----------+

Copy
限流规则添加后，超过定义的并发度的 SQL 请求在 "Concurrency control waiting" 状态

限流前后对比，可以看到限流后 CPU 使用率从 100% 降低到 50% 左右，有效恢复业务可用性。

2.2 DAS 限流

对于 RDS for MySQL 5.6 和 5.7 ，控制台的 CloudDBA 功能直接集成了 SQL 限流功能。

我们来看一个真实生活中的例子，某客户在业务高峰期出现大量的集中请求，导致高配实例 CPU 完全打满，由于实例响应极其缓慢，能采集到的监控数据显示当时 活动会话达到 14700+ 。
在业务层反复调整无法恢复的情况下 在 2020.3.24 21:35 通过设置 SQL 限流恢复了业务可用性。

RDS 实例会话情况

RDS 实例 CPU 使用率情况

3. 御敌于外

上面介绍的都是数据库层面的应对之策，那么是否我们一定要被动的在数据库层面“兵来将挡”呢？有没有主动“御敌于外”的办法呢？

3.1 名词解释

名称	说明
1	短连接	通信双方有数据交互时，就建立一个 TCP 连接，数据发送完成后，则断开此 TCP 连接；通常基于 PHP 语言的应用采用短连接方式访问数据库
2	长连接	通信双方有数据交互时，首先尝试复用已有空闲 TCP 连接，如果没有空闲 TCP 连接则尝试创建新连接；数据发送完成后，通常不断开此 TCP 连接以便后续复用；通常基于 Java 语言的应用采用长连接方式访问数据库
3	syn queue	用于存储接收到的 syn 请求的连接 socket 队列，TCP 协议栈接收到 syn 后系统内核自动回复 syn,ack 同时将 syn 代表的连接放入到 syn queue 队列中，并管理是否需要重传 syn,ack；其长度由 tcp_max_syn_backlog （或 somaxconn）Linux 内核参数确定
4	accept queue	用于存储完成 TCP 三次握手的连接 socket 队列，当 MySQL 调用 accept() 时从该队列取走一个 socket 处理，其长度由 应用设置的 backlog 参数和内核参数 somaxconn 的较小值决定
5	ListenOverFlow	由于 syn queue 已经打满，新收到的 syn 请求不被处理而丢弃的场景发生数量
6	ListenDrops	由于 accept queue 已经打满，完成 TCP 三次握手的连接不被处理而丢弃的场景发生数量

3.2 短连接优化

首先我们来看看一个普通的 SQL 请求是如何被从应用通过网络发送给 DB 层进而得到处理的。

仔细看一下上述时序图，就会发现如果应用和数据库之间在没有可用的网络连接情况下，需要首先建立起一条基于 TCP/IP 协议栈的 MySQL 网络连接才能够将 SQL 请求发送给数据库实例并获取到处理的结果集。
在应用采用短连接机制（比如基于 PHP 语言开发的应用）的情况下，每个 SQL/Query 都需要和数据库实例创建一个 TCP 网络连接，需要消耗数据库实例（和其所在物理机）的 CPU 资源。
在“海啸”的场景下，采用短连接机制的应用会保持很高的新连接创建速率（CPS，大于等于 QPS），这样在高负载（QPS） 的基础上进一步消耗数据库实例的 CPU 资源，拉高 CPU 使用率，降低 CPU 使用效率，进入恶性循环容易触发数据库雪崩式崩溃。
在 CPU 资源紧张的情况下会出现大量连接请求积压无法处理而触发 ListenOverFlow 和 ListenDrops 情况出现。
这里我们看一个真实世界中的例子。
客户在 13:30 将应用从长连接模式调整为短连接模式，由于短连接模式的高并发新建连接请求速率（CPS - 每秒新建连接数），修改后实例 CPU 使用率总体上升 25+% 左右，业务侧出现大量连接失败错误并感知 RDS 实例响应缓慢。

部分 CPU 被完全打满，无法满足处理高连接请求的需求而出现 ListenOverFlow / ListenDrops。


线程池 Thread Pool 是数据库层对该场景较好的解决方案，而启用了数据库独立代理（RDS for MySQL 读写分离地址 和 PolarDB for MySQL 的集群地址）的实例还可以选择启用“短连接优化”的链路层解决方案。

当应用断开连接后，数据库独享代理会判断之前的连接是否为空闲（idle）连接，如果是空闲连接，代理会将代理与数据库之间的连接保留在连接池内一段时间（仅释放应用与代理之间的连接）。
在保留连接的这段时间内如果应用发起新连接，代理会直接从连接池里使用保留的连接，从而减少与数据库建立连接的开销。
官方文档：短连接优化