MySQL主从延迟10秒：WRITESET+并行复制优化三步实战

MySQL 复制延迟从毫秒级涨到 10 秒，最危险的处理方式是看到 Seconds_Behind_Source 变大，就立刻把 replica_parallel_workers 改成一个很大的数。并行复制确实能提升副本 SQL 应用吞吐，但它不能拆分一条超长事务，不能绕过表级热点带来的依赖，也不能修复 I/O 线程断连、磁盘抖动、DDL 阻塞、源库写入突增或网络链路问题。盲目加 worker 的结果可能是副本内存增大、提交顺序更难解释，延迟却没有改善。

本文面向 MySQL 8.0 的 GTID 与 ROW 格式复制场景，使用 Source 和 Replica 术语。旧版本或旧脚本中可能仍显示 Master/Slave、SHOW SLAVE STATUS、slave_parallel_workers 等名称，不能把两套变量无差别混用。示例中的 <源库登录路径>、<副本库登录路径>、<复制通道名>、<配置文件>、<备份目录>、<worker数量> 都是占位符，应替换为实际值。登录方式使用 mysql_config_editor 创建的 login-path，避免把用户名、密码或 TLS 参数直接写进 shell 历史。 文中其余尖括号变量按中文含义替换为实际实例、文件、路径、角色或参数值，并通过变更单和受控配置保存，不在命令行传递明文凭据。

所谓“3 个杀手锏”不是三个独立魔法开关，而是一条完整链路：

1. 源库用 WRITESET 生成更可并行的事务依赖信息；
2. 副本以 LOGICAL_CLOCK 和合适数量的 applier worker 并行应用；
3. 在需要复制提交顺序时启用 replica_preserve_commit_order，并用状态、性能模式和业务指标验证收益。

它们成立的前提是：复制健康、事务足够细、写入没有被同一热点串行化、磁盘与网络有余量、变更可回滚。下面先用证据确定延迟类型，再逐步进入配置。

先确认“10 秒”代表什么

Seconds_Behind_Source 是副本根据事件时间戳计算的近似指标，不能单独作为数据新鲜度 SLA。网络中断、I/O 线程停止、长事务、时钟偏差、复制过滤、并行 worker 堵塞和人为停止 SQL 线程都可能让它不可靠或为 NULL。排障要同时看 I/O、SQL/applier、GTID 集合、relay log、worker 状态和错误字段。

先确认 MySQL 版本、二进制日志格式、GTID 与并行复制相关变量。所有查询都应在目标 Source 或 Replica 上执行，并将角色写进工单；最常见的人为事故是把副本调优 SQL 误执行到源库，或在错误复制通道上操作。

# 代码 1：使用受控 login-path 连接并记录版本与角色相关变量
mysql --login-path=<副本库登录路径> --batch --raw -e "
SELECT @@version AS mysql_version,
       @@server_uuid AS server_uuid,
       @@read_only AS read_only,
       @@super_read_only AS super_read_only,
       @@gtid_mode AS gtid_mode,
       @@binlog_format AS binlog_format;
"

副本通常应满足 read_only 和 super_read_only 的既定策略，但这不替代复制状态检查。某些复制管理账户或拥有特殊权限的会话仍可能写入；因此“副本只读”不是延迟诊断的结论。GTID_MODE=ON 与 ROW 格式是并行复制和故障切换的常见基础，但是否可修改要按现网复制拓扑、客户端能力和变更窗口评估。

-- 代码 2：在副本交互式 mysql 客户端中查看复制通道状态
SHOW REPLICA STATUS FOR CHANNEL '<复制通道名>'\G

重点记录 Replica_IO_Running、Replica_SQL_Running 或对应 applier 状态、Seconds_Behind_Source、Last_IO_Error、Last_SQL_Error、Relay_Log_Space、Retrieved_Gtid_Set 与 Executed_GtidSet。字段名称会随 MySQL 小版本和多源复制场景变化，若未使用命名通道，按当前版本支持的 SHOW REPLICA STATUS 形式执行。任何 Last*_Error 非空都应先处理错误，不能先增加并行度。

-- 代码 3：从 Performance Schema 查看各 applier worker 是否有错误或长期工作
SELECT
  CHANNEL_NAME,
  WORKER_ID,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LEFT(LAST_ERROR_MESSAGE, 200) AS last_error_message,
  APPLYING_TRANSACTION,
  LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker
ORDER BY CHANNEL_NAME, WORKER_ID;

这张表用于观察 worker，而不是直接证明并行度已经充分。一个 worker 长时间 APPLYING_TRANSACTION 可能是长事务、锁等待、磁盘慢或依赖关系造成；多个 worker 空闲也可能说明 source 没有提供足够可并行事务。查询前确认 performance_schema 已启用，且当前账号有读取该表的权限。

-- 代码 4：检查协调器与连接状态，区分 I/O 和 SQL 应用问题
SELECT * FROM performance_schema.replication_applier_status_by_coordinator;
SELECT * FROM performance_schema.replication_connection_status;

如果连接状态异常或 I/O 线程反复重连，优化 SQL applier 不能解决网络、认证、TLS 或源库 binlog 保留问题。若协调器正常但 worker 有 SQL 错误，则要根据错误号、事务和业务约束处理；绝不能为了让延迟归零就直接跳过事务。

第一关：排除并行复制不该背锅的根因

并行复制处理的是“副本能否并行应用多个独立事务”。它无法把一条 30 秒的大 DDL、一次批量 UPDATE、长时间持锁事务或单行热点拆成多个 worker。SQL 线程显示落后时，先看源库的写入特征、正在运行的事务、事务大小与副本磁盘情况。

-- 代码 5：在源库查看当前长事务和锁等待线索
SELECT
  trx_id,
  trx_started,
  trx_state,
  trx_rows_modified,
  trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 20;

information_schema.innodb_trx 只能显示此刻仍在运行的事务，不能证明已经提交的历史批量事务。若看到长事务，应进一步从应用发布、慢日志、审计和性能模式确定发起方；不要直接 KILL 业务会话。中止事务可能造成回滚长时间占用资源，反而加重复制延迟。

-- 代码 6：在源库找出累计耗时高的语句摘要
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_ROWS_AFFECTED,
  SUM_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

这是累积摘要，适合定位长期热点，但不等于当前事故窗口。应记录采样时间，必要时和慢查询日志、应用发布批次、binlog 事件量及业务写入曲线交叉验证。对写入热点，优化索引、缩短事务、批量节流、分片或减少同一行更新，通常比增加 worker 更有效。

# 代码 7：检查副本磁盘、文件系统与 mysqld 资源压力
df -hT <MySQL数据目录>
df -i <MySQL数据目录>
iostat -xz 1 10
pidof mysqld
ps -o pid,ppid,stat,etime,%cpu,%mem,cmd -p "$(pidof mysqld)"

iostat 来自 sysstat 工具，未安装时不应在事故中临时加包；可改用既有主机监控。高 await、接近饱和的 util、空间不足或 inode 不足都可能让 relay log、binlog、InnoDB 刷盘和复制应用受阻。磁盘指标必须结合设备类型、写入量和基线判断，不能只凭一次较高的 await 结论。

-- 代码 8：检查 Source 是否满足 ROW、GTID 与写集依赖跟踪的基础条件
SELECT
  @@global.binlog_format AS binlog_format,
  @@global.gtid_mode AS gtid_mode,
  @@global.enforce_gtid_consistency AS enforce_gtid_consistency,
  @@global.transaction_write_set_extraction AS write_set_extraction,
  @@global.binlog_transaction_dependency_tracking AS dependency_tracking;

对于 MySQL 8.0，WRITESET 依赖 transaction_write_set_extraction，常见算法是 XXHASH64。若源库不是 ROW、GTID 策略未完成或复制包含旧版本副本，先完成兼容性评估，不能只设置一个变量。WRITESET 也不是万无一失的“更多并行”：具有真实数据依赖、缺少主键、事务过大或同一热点行更新频繁时，仍会串行。

杀手锏一：让 Source 写出有价值的依赖信息

LOGICAL_CLOCK 并行复制利用 binlog 中的依赖信息判断哪些事务可并行应用。源库使用 WRITESET 或 WRITESET_SESSION 能比单纯提交顺序暴露更多可并行空间。WRITESET 使用事务写集判断冲突；WRITESET_SESSION 会进一步保守地保持同一会话事务的顺序。选择哪个取决于应用是否依赖同一会话的提交关系、事务隔离假设和一致性要求，不能以吞吐为唯一目标。

先保存现有变量和配置文件。SET PERSIST 会写入 MySQL 的持久化配置文件并立即改变全局值，因此也是生产写操作；必须先在与生产结构相同或足够接近的环境验证，再按一台 Source 灰度、观察、推广的节奏执行。

# 代码 9：在 Source 上备份相关变量与配置文件
#!/usr/bin/env bash
set -euo pipefail

login_path="<源库登录路径>"
config_file="<配置文件>"
backup_dir="<备份目录>"

mkdir -p "$backup_dir"
mysql --login-path="$login_path" --batch --raw -e "
SHOW GLOBAL VARIABLES WHERE Variable_name IN
('binlog_format','gtid_mode','enforce_gtid_consistency',
 'transaction_write_set_extraction',
 'binlog_transaction_dependency_tracking');
" > "$backup_dir/source-replication-vars.before.txt"
sudo cp -a "$config_file" "$backup_dir/mysqld.cnf.before"

备份文件不能包含明文密码，但变量、文件路径和复制拓扑仍属于内部信息，应限制访问。若 <配置文件> 是 include 目录中的片段，备份实际生效的文件而不是仅备份主配置。不同 Ubuntu 安装方式的 MySQL 配置层级不同，先使用 mysqld --verbose --help 或现有运维文档确认读取顺序。

# 代码 10：Source 的 MySQL 8.0 配置片段示例
# 放入现有 [mysqld] 配置层级，不要复制覆盖完整 my.cnf。
[mysqld]
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_tracking = WRITESET

配置片段展示的是目标状态，不代表应通过重启直接切换。binlog_format、GTID 与复制一致性参数可能影响全拓扑和客户端，任何从 STATEMENT/MIXED 或 GTID 关闭状态迁移都应是独立项目。已有 ROW+GTID 环境里，才评估 WRITESET 相关项；不要借一次延迟事故同时改动五类基础复制设置。

-- 代码 11：经审批后在 Source 持久化写集与依赖跟踪设置
SET PERSIST transaction_write_set_extraction = 'XXHASH64';
SET PERSIST binlog_transaction_dependency_tracking = 'WRITESET';

SELECT
  @@global.transaction_write_set_extraction AS write_set_extraction,
  @@global.binlog_transaction_dependency_tracking AS dependency_tracking;

执行这段 SQL 需要具备系统变量管理权限，并应在低风险窗口进行。变更只影响之后生成的 binlog 事件，旧 relay log 中的事务不会突然获得更多并行性；因此验证窗口必须覆盖新写入。若业务存在同会话顺序依赖，评估 WRITESET_SESSION 的保守性，不要只因基准压测更高就忽略语义。

-- 代码 12：确认 Source 上 binlog 的新事件与副本 GTID 进度
SHOW MASTER STATUS;
SELECT
  @@global.gtid_executed AS source_executed_gtid,
  @@global.binlog_transaction_dependency_tracking AS dependency_tracking;

SHOW MASTER STATUS 仍是 MySQL 8.0 的有效命令名称。它用于记录当前 binlog 文件与位置，不能单独决定是否可故障切换；GTID 集合、所有副本状态、binlog 保留和业务写入冻结策略都要同时评估。采集这些信息应避免在工单中泄露内部库名和 binlog 文件细节。

杀手锏二：让 Replica 真正并行应用

Source 已经产生可并行信息后，Replica 仍需要有多个 applier worker，并将 replica_parallel_type 设置为 LOGICAL_CLOCK。worker 数量不是越多越好：每个 worker 有内存、事务上下文和 I/O 压力，超过磁盘、CPU 或依赖图能提供的并行度后，只会增加调度开销。合适数量应从较小的值开始，在同一事故模型或压测模型中递增观察。

在执行任何 STOP REPLICA 前，先确认实例角色、复制通道、业务读流量、故障切换状态和变更窗口。停止 SQL_THREAD 会暂停该通道的应用，短时间内 Seconds_Behind_Source 可能增加；这不是失败，但必须预先说明影响和恢复阈值。

-- 代码 13：在 Replica 上记录当前并行复制设置
SELECT
  @@global.replica_parallel_type AS parallel_type,
  @@global.replica_parallel_workers AS parallel_workers,
  @@global.replica_preserve_commit_order AS preserve_commit_order,
  @@global.replica_pending_jobs_size_max AS pending_jobs_size_max;

变量名以 MySQL 8.0 为准。老版本可能仍使用 slaveparallel* 兼容名称，但不要在脚本里混写。replica_pending_jobs_size_max 是 coordinator 可缓存待执行任务的内存上限之一，调大它必须与 worker 数、事务大小和节点内存一起评估，不能仅为消除某个等待就无上限增加。

# 代码 14：在 Replica 变更前保存状态与变量
#!/usr/bin/env bash
set -euo pipefail

login_path="<副本库登录路径>"
backup_dir="<备份目录>"

mkdir -p "$backup_dir"
mysql --login-path="$login_path" --batch --raw -e "
SHOW REPLICA STATUS FOR CHANNEL '<复制通道名>';
" > "$backup_dir/replica-status.before.txt"
mysql --login-path="$login_path" --batch --raw -e "
SHOW GLOBAL VARIABLES WHERE Variable_name IN
('replica_parallel_type','replica_parallel_workers',
 'replica_preserve_commit_order','replica_pending_jobs_size_max');
" > "$backup_dir/replica-parallel-vars.before.txt"

非交互式 SHOW REPLICA STATUS 的格式不如 \G 易读，但适合保存原始证据。多源复制必须在每个语句中明确通道；不要依赖默认通道，因为误停另一个通道会扩大影响。若实例上存在异步和半同步混合拓扑，也要确认这台副本在故障切换链路中的角色。

-- 代码 15：在灰度 Replica 上切换到 LOGICAL_CLOCK 并设置初始 worker 数
STOP REPLICA SQL_THREAD FOR CHANNEL '<复制通道名>';

SET PERSIST replica_parallel_type = 'LOGICAL_CLOCK';
SET PERSIST replica_parallel_workers = <worker数量>;
SET PERSIST replica_preserve_commit_order = ON;

START REPLICA SQL_THREAD FOR CHANNEL '<复制通道名>';
SHOW REPLICA STATUS FOR CHANNEL '<复制通道名>'\G

这是高风险复制变更。<worker数量> 应是经过容量评估的小步初始值，例如按 vCPU、磁盘、并发写入和压测结果选择，而不是直接等于 CPU 核数。STOP/START 只针对 SQL_THREAD，I/O 线程仍可能继续接收 relay log；执行后立即检查 Last_SQL_Error、SQL 线程状态和延迟曲线。若当前版本要求先停止整个通道或变量不支持 SET PERSIST，应以本版本手册和 --help/SHOW VARIABLES 结果为准。

-- 代码 16：检查 worker 是否启动并有可解释的状态
SELECT
  CHANNEL_NAME,
  WORKER_ID,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LAST_ERROR_TIMESTAMP,
  APPLYING_TRANSACTION,
  LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker
WHERE CHANNEL_NAME = '<复制通道名>'
ORDER BY WORKER_ID;

worker 数量增加后，表中应出现相应 worker，但“出现很多 worker”不等于吞吐提升。要看它们是否在同一窗口内承担事务、是否有错误、延迟是否下降、磁盘和 CPU 是否仍有余量。持续只有一个 worker 活跃可能是真实依赖、长事务或 source 依赖信息不足，而不是单纯配置漏项。

-- 代码 17：从协调器视角检查等待与错误
SELECT
  CHANNEL_NAME,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LEFT(LAST_ERROR_MESSAGE, 200) AS last_error_message
FROM performance_schema.replication_applier_status_by_coordinator
WHERE CHANNEL_NAME = '<复制通道名>';

协调器负责读取 relay log 并分派事务。若 worker 都空闲而协调器状态异常，应该回到 relay log、I/O、磁盘和错误日志；若协调器正常但提交被顺序约束，必须区分这是 preserve commit order 的预期成本，还是业务事务依赖严重。

杀手锏三：保持提交顺序，并接受它的代价

replica_preserve_commit_order=ON 让并行 worker 按 Source 的提交顺序提交事务。这对于需要副本与 Source 提交可见顺序保持一致的场景很重要，也能减少某些读到“后提交事务已可见、前提交事务尚未可见”的困惑。代价是 worker 可能在提交阶段等待前序事务，实际可并行收益受工作负载依赖影响。

它不是复制一致性的全部保证，也不替代 GTID、binlog、崩溃恢复、半同步或读写路由设计。是否需要 ON 要由业务读取副本的语义决定；若关闭它只为追求更低 Seconds_Behind_Source，应先评估是否会破坏读后写、事务顺序或故障切换约定。

-- 代码 18：明确检查提交顺序与并行类型的最终有效值
SELECT
  @@global.replica_parallel_type AS parallel_type,
  @@global.replica_parallel_workers AS parallel_workers,
  @@global.replica_preserve_commit_order AS preserve_commit_order;
SHOW REPLICA STATUS FOR CHANNEL '<复制通道名>'\G

验证要跨越至少一个正常业务窗口，并与变更前基线对比。除了 Seconds_Behind_Source，还应观察 relay log 增长、worker 错误、提交等待、磁盘延迟、CPU、业务查询错误和数据新鲜度探针。若延迟短时下降但副本 I/O 饱和或业务读延迟上升，说明只是把瓶颈转移了。

-- 代码 19：查看 Replica 上正在执行的事务与锁等待
SELECT
  trx_id,
  trx_started,
  trx_state,
  trx_rows_modified,
  trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 20;

副本上的长事务可能来自复制 worker，也可能来自只读业务查询、运维会话或报表。要结合 processlist、Performance Schema 和应用连接标识判断。禁止在没有证据时批量 kill 线程；中断长读可能伤害业务，中断复制 worker 可能造成恢复和重新应用成本。

-- 代码 20：识别没有主键的 InnoDB 表，作为并行能力审查线索
SELECT
  t.table_schema,
  t.table_name
FROM information_schema.tables AS t
LEFT JOIN information_schema.table_constraints AS c
ON c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND c.constraint_type = 'PRIMARY KEY'
WHERE t.engine = 'InnoDB'
AND t.table_type = 'BASE TABLE'
AND t.table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
AND c.constraint_name IS NULL
ORDER BY t.table_schema, t.table_name;

缺少主键不一定直接导致复制停滞，但会降低写集提取、行定位和故障修复的可预测性。给大表补主键是高风险 DDL 项目，涉及锁、复制、存储和业务兼容性，不能在延迟事故中临时执行。先形成表清单、写入模式、容量和在线 DDL 评估，再单独规划。

用可重复的监控判断是否真的变快

复制优化需要把“延迟”拆开：I/O 是否及时接收、Relay Log 是否积压、SQL/applier 是否推进、每个 worker 是否有错误、Source 写入是否增大、Replica 磁盘是否成为瓶颈。单个仪表盘的绿灯不够；应在一个相同业务周期内对比变更前后。

# 代码 21：持续采样复制健康状态，供变更窗口观察
#!/usr/bin/env bash
set -euo pipefail

login_path="<副本库登录路径>"
channel="<复制通道名>"
interval_seconds=10

while true; do
  date --iso-8601=seconds
  mysql --login-path="$login_path" --batch --raw -e "
    SHOW REPLICA STATUS FOR CHANNEL '$channel';
  "
  sleep "$interval_seconds"
done

该脚本会持续输出，适合在受控终端或日志采集任务中运行；不要把它放在无人管理的 shell 中无限执行。SHOW REPLICA STATUS 的列宽和字段随版本变化，生产监控应使用解析稳定的 exporter 或明确的 SQL 视图，而不是依赖人工肉眼扫描长表格。

# 代码 22：复制延迟告警表达式示例，指标以实际 mysqld exporter 暴露为准
max by (instance, channel_name) (
  mysql_slave_status_seconds_behind_master
)

一些 exporter 仍沿用 slave_status 指标名，即使 MySQL 命令已使用 replica 术语。指标名称、channel 标签和 NULL 的表达方式必须以实际 exporter 暴露的指标为准。告警应配合 SQL/IO 线程状态、持续时间和业务新鲜度指标，避免在短暂写入洪峰时产生无意义告警。

# 代码 23：复制 SQL 线程非运行状态示例，指标以实际 exporter 暴露为准
max by (instance, channel_name) (
  mysql_slave_status_sql_running == 0
)

线程状态告警与延迟告警的处理级别不同：SQL 线程停止通常应优先处理错误和数据一致性，不能等待延迟阈值触发。若 exporter 使用不同指标或将布尔状态导出为字符串标签，应按实际暴露格式改写，并在测试环境验证规则。

# 代码 24：从受控备份回滚 Replica 的并行复制设置
#!/usr/bin/env bash
set -euo pipefail

login_path="<副本库登录路径>"
channel="<复制通道名>"
old_workers="<原worker数量>"
old_parallel_type="<原并行类型>"
old_commit_order="<原提交顺序>"

mysql --login-path="$login_path" --execute "
  STOP REPLICA SQL_THREAD FOR CHANNEL '$channel';
  SET PERSIST replica_parallel_workers = $old_workers;
  SET PERSIST replica_parallel_type = '$old_parallel_type';
  SET PERSIST replica_preserve_commit_order = $old_commit_order;
  START REPLICA SQL_THREAD FOR CHANNEL '$channel';
  SHOW REPLICA STATUS FOR CHANNEL '$channel';
"

回滚前必须从代码 14 的备份确认三个原值，不要凭记忆填写。该脚本同样暂停 SQL_THREAD，影响数据新鲜度；若当前已经出现 Last_SQL_Error、GTID 不一致或 relay log 异常，先停止自动化回滚并按复制故障流程处理。回滚后验证 worker 状态、延迟、错误日志和业务读路径，确认不是由其它资源瓶颈造成的持续落后。

并行复制最有效的场景，是 Source 产生大量彼此独立、足够细的 ROW 事务，而 Replica 具备可用的 CPU、磁盘和内存。它最无力的场景，是超长事务、热点更新、阻塞 DDL、错误停止、磁盘饱和和网络中断。先用证据把两类情况分开，再按“WRITESET → LOGICAL_CLOCK + workers → commit order”的顺序小步验证，才能让延迟优化既有收益，也保留可解释性和可回退性。