1 引言

研发反馈数据库多次出现慢 SQL，经排查多种原因，最终定位根因是底层容器 IO 隔离不足导致的实例间相互影响。

本中详细介绍了分析过程，并介绍相关概念，比如半同步复制、组提交、IaaS。

2 现象

数据库出现慢 SQL，在 2022-08-16 09:25:02~16 这14 秒期间，产生了 190 多条 insert/update 慢 SQL。

短时间内较多 insert/update 执行慢，并且该情况出现过多次，之前出现过 select 1 执行用时超过 10秒，严重影响业务，因此需要查到根因。

3 分析

3.1 SQL

首先，怀疑是 SQL 的原因。

3.1.1 SQL 性能

查看 update 语句的执行计划，SQL 走索引，而且扫描行数很少，因此本身不是慢 SQL。

mysql> explain update waybill13 SET again_volume = 8064.0, update_time = now() 
where waybill_code = '03011377548' G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: waybill13
   partitions: NULL
         type: range
possible_keys: idx_waybill_code
          key: idx_waybill_code
      key_len: 98
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set (0.01 sec)

3.1.2 锁等待

完成一项任务所需要的时间可以分为了两部分：执行时间与等待时间。

因此，SQL 的执行总用时 = 实际执行用时 + 锁等待时间，其中如果锁等待时间过长也会导致慢 SQL。

查看锁时长，结果显示锁时长很短，比如 0.000036秒，因此排除该原因。

因此，排除 SQL 的原因。

3.2 数据库监控

MySQL 的性能瓶颈通常出现在 CPU 与 IO，因此分别查看监控项。

3.2.1 CPU 使用率

3.2.2 磁盘IO繁忙度

3.2.3 TPS & QPS

查看 TPS 与 QPS，该时间段也没有发现异常。

因此，从数据库监控方面看，数据库性能正常。

3.3 数据库配置

3.3.1 半同步

由于 RDS 默认开启半同步，因此怀疑由于半同步超时导致响应变慢。

首先，查看半同步状态参数 Rpl_semi_sync_master_status，显示半同步复制生效中。

mysql> show status like '%Rpl_semi_sync_master_status%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

然后，查看半同步超时参数 rpl_semi_sync_master_timeout，显示超时时间为1秒。

mysql> show variables like '%rpl_semi_sync_master_timeout%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 1000  |
+------------------------------+-------+
1 row in set (0.01 sec)

如果是半同步超时导致的慢 SQL：

偶发的半同步超时导致的慢 SQL 只会持续 1 秒左右，而当前慢 SQL 持续了 14 秒，因此排除；
如果主从之间网络不稳定，有可能导致半同步持续超时持续 14 秒。

当半同步复制超时退化为异步复制时，错误日志中将记录类似报错 Timeout waiting for replay of binlog。

不过查看错误日志时，显示该时间段内没有半同步超时相关的报错。

因此，排除半同步超时的原因。

3.3.2 组提交

组提交可能导致等待延迟问题，下面进行分析。

binlog_group_commit_sync_delay 参数用于控制日志在刷盘前日志提交要等待的时间。

mysql> show variables like '%binlog_group_commit_sync_delay%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| binlog_group_commit_sync_delay | 0     |
+--------------------------------+-------+
1 row in set (0.00 sec)

binlog_group_commit_sync_delay 默认为 0，表示提交后立即刷盘，注意不代表关闭组提交。

因此，排除组提交等待延迟的原因。

最后，怀疑是数据库底层宿主机异常导致写入变慢。

3.4 根本原因

3.4.1 宿主机

查看宿主机监控，结果显示磁盘吞吐量飙高，最高达到 10G/s，导致网络打满。

那么，是什么导致磁盘吞吐量飙高呢？

3.4.2 容器

由于数据库部署在容器上，因此查看磁盘与容器的对应关系，结果显示有两个容器挂在同一块 nvme 物理盘。

nvme3n1                   259:8    0  1.8T  0 disk
└─nvme3n1p1               259:17   0  1.8T  0 part
  ├─data_vg-d--uurvvwn30z 253:3    0 1020G  0 lvm  /mnt/d-uurvvwn30z
  └─data_vg-d--mhsajvqt7w 253:4    0  620G  0 lvm  /mnt/d-mhsajvqt7w

两个容器都在运行 MySQL 数据库，因此存在竞争关系，异常期间，nvme 盘的吞吐达到了上限。

容器平台由公司 IaaS 团队负责，经确认，当前底层 IaaS 容器平台只对容器设置了 IOPS 维度的限速且已经生效，没有对磁盘吞吐量做限制，这次问题为其他容器磁盘吞吐量过大导致。

理论上，吞吐量不是必要参数，原因是 MySQL 一般场景下读写块都比较小，IOPS 限速生效的情况下正常情况下不会有吞吐瓶颈，因此还需要确认另一个容器具体是什么操作导致的吞吐量飙高，而且导致 IOPS 限速没有限制住吞吐量。

IOPS 与吞吐量的关系：

磁盘的 IOPS，也就是在一秒内，磁盘进行多少次 I/O 读写。

磁盘的吞吐量，也就是每秒磁盘 I/O 的流量，即磁盘写入加上读出的数据的大小。

因此，吞吐量 = IOPS × 数据块大小。

经确认，该数据库吞吐量飙高的原因是当时在进行物理备份，该备份是定时执行，并且是在隐藏从库上执行。

物理备份操作的是文件，而不是数据页，因此磁盘吞吐消耗较大。

3.4.3 类似案例

实际上，在此之前，也曾经遇到过类似案例，数据库重做时备份过程中导致网络流量打满。

正常情况下同网段的数据库主从会使用同一个交换机，数据传输速度很快，但是当时两台数据库却不是同一个交换机，数据传输速度较慢，最终导致网络流量打满，影响到业务。

4 知识点

4.1 半同步复制

MySQL 支持多种复制模式：

异步复制，默认模式，主库在执行完客户端提交的事务后立即返回结果给客户端，不关系从库是否接收并处理，因此数据有丢失风险；
半同步复制，MySQL 5.5 中引入，主库在应答客户端提交的事务前需要保证至少一个从库接收并写入到 relay log；
全同步复制，MySQL 5.7.17 中引入，主库在应答客户端提交的事务前需要保证所有从库接收并写入到 relay log。

从上到下，数据一致性逐渐增强，性能逐渐降低，原因是主库完成一个事务的时间变长。

因此，综合考虑性能与数据一致性时，通常建议采用半同步复制，原因是半同步复制相比于全同步复制提升了性能，相比于异步复制保证了数据一致性。

但是，半同步复制也存在风险。

比如当半同步设置超时时间后，如果一定时间内主库没有收到从库的响应（ACK），将自动切换为异步复制，仍然存在数据丢失的风险。

此外，可以参考《技术分享 | 无损半同步复制下，主从高可用切换后数据一致吗？》模拟半同步未超时情况下数据库切换后数据丢失的场景。

因此，通常在数据库主从切换过程后，选择从旧主拉取 binlog 补数或给旧主 flashback 回退数据，最终保证主从数据一致。

4.2 组提交

组提交（group commit）是并行复制（Multi-Thread Slave，MTS）的一种实现方案，因此，首先介绍下并行复制。

4.2.1 并行复制

MySQL 5.6 以前，仅支持单线程复制，在此之后，开始支持并行复制。

主库多线程并发写入，而从库单线程串行回放，对于更新压力比较大的主库，从库有可能一直追不上主库。

并行复制的模型很简单，就是将单线程拆成多个线程。如下图所示，coordinator 负责读取中转日志和分发事务，worker 线程负责真正更新日志，可以通过参数 slave_parallel_workers 调整 worker 线程的数量来控制并发。

并行复制的核心在于判断多个事务是否可以并行，而影响并发度的原因是锁，因此具体是通过是否存在锁冲突进行判断。

并行复制的实现经历了如下多个版本的演进，不同版本的区别在于并行度，期望最大化还原主库的并行度。

版本	MTS 机制	实现原理
5.6	Database	基于库级的并行复制
5.7	COMMIT_ORDER	基于组提交的并行复制
5.7.22	WRITESET / WRITESET_SESSION	基于 WRITESET 的并行复制

可见，MySQL 的并行复制支持多种策略，需要结合具体业务场景，选择不同的策略。

4.2.2 组提交

组提交的思想是通过批量提交降低 IOPS。

MySQL 多次使用组提交，比如 redo log 组提交与 binlog 组提交。

在没有开启 binlog 时，redo log 刷盘操作会成为 MySQL TPS 的性能瓶颈，通过使用 redo log 组提交，故意拉长 binlog 从 write 到 fsync 的时间，可以将多个刷盘操作合并为一个。

在开启 binlog 以后，binlog 的刷盘操作又成为了性能瓶颈。原因是为保证 redo log 和 binlog 数据一致性，MySQL 使用了两阶段提交，由 binlog 作为事务的协调者。

为缓解该问题，MySQL 再次引入 binlog 的组提交，并与 redo log 的组提交相结合，将事务的两阶段提交细化，具体是把 redo log 做 fsync 的时间推迟到步骤 2 之后，这样，binlog 也就可以组提交了。

正因如此，WAL 机制虽然没有减少磁盘读写次数，原因是每次事务提交都要写 redo log 和 binlog，但是有以下两个优点：

顺序写，磁盘顺序写远快于随机写入；
组提交，可以大幅度降低磁盘 IOPS 消耗。

binlog 组提交的思想是主库同一时间提交的事务认为是一组，同一组提交的事务不冲突，可以在从库并行回放。

为了表示主库并行度，binlog gtid_event 中新增了两个字段，包括 last_committed 和 sequence_number。当 last_committed 相同时，表示是一组事务。

如下所示，last_committed = 0、2、7 分别对应多个事务，因此可以并发回放。

#220830 17:20:19 server id 73431  end_log_pos 259 CRC32 0xad666951      GTID    last_committed=0        sequence_number=1       rbr_only=yes
#220830 17:20:19 server id 73431  end_log_pos 6986 CRC32 0xef5299fd     GTID    last_committed=0        sequence_number=2       rbr_only=yes
#220830 17:20:19 server id 73431  end_log_pos 8040 CRC32 0x4fba7aba     GTID    last_committed=2        sequence_number=3       rbr_only=yes
#220830 17:20:19 server id 73431  end_log_pos 12178 CRC32 0xcd84e796    GTID    last_committed=2        sequence_number=4       rbr_only=yes
#220830 17:20:19 server id 73431  end_log_pos 12791 CRC32 0x908a6f85    GTID    last_committed=4        sequence_number=5       rbr_only=yes
#220830 17:20:19 server id 73431  end_log_pos 13531 CRC32 0x19ca181c    GTID    last_committed=5        sequence_number=6       rbr_only=yes
#220830 17:20:19 server id 73431  end_log_pos 20313 CRC32 0xca7bd067    GTID    last_committed=6        sequence_number=7       rbr_only=yes
#220830 17:20:19 server id 73431  end_log_pos 21391 CRC32 0xeffcb86f    GTID    last_committed=7        sequence_number=8       rbr_only=yes
#220830 17:20:19 server id 73431  end_log_pos 22004 CRC32 0xc7c1ffbb    GTID    last_committed=7        sequence_number=9       rbr_only=yes
#220830 17:20:19 server id 73431  end_log_pos 22710 CRC32 0x11e2c5bd    GTID    last_committed=7        sequence_number=10      rbr_only=yes

注意并行复制生效的前提是主库的真实负载是并行，还可以通过人为控制 fsync 调用频率延迟提交（group commit delay）提升从库复制的并行度。

具体是通过调节以下两个参数，拉长 binlog 从 write 到 fsync 的时间，既可以让主库提交变慢，又可以让从库复制变快。

binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

两个条件是或的关系，因此只要满足其中一个就会调用 fsync。

group commit delay 的缺点是可能会增加主库上语句的响应时间，当并发负载高时，有可能加剧锁竞争，降低吞吐量，导致数据库性能下降。

4.2.3 适用场景

MySQL 主从延迟的常见原因包括：

大事务，如大表 DDL、大表 delete
并行度不够
锁冲突
参数设置不合理
从库硬件差
主从之间网络差

因此，当主从延迟较大，而且性能瓶颈在 IO 上时，除了调整刷盘参数，还可以通过调整并行复制参数提高并行度。

比如通过开启并行复制，一方面可以延迟主库 SQL 的响应（binlog_group_commit_sync_delay），另一方面可以提高从库的并行度。

生产环境中遇到过一个案例，主库灌数据过程中 binlog 写入太快导致磁盘打满，从库主从延迟太大无法清理 binlog，后来通过调整组提交相关参数实现延迟的降低。

set global binlog_group_commit_sync_delay=5000;
set global binlog_group_commit_sync_no_delay_count=5000;

set global transaction_write_set_extraction=XXHASH64;
set global binlog_transaction_dependency_tracking=WRITESET;