MGR 删除数据时报错 3100

问题描述

用户执行大批量数据删除操作时，MGR 组复制报错：

delete from mytable where create_time < '2026-01-01';

错误日志

3100 Error on observer while runing replication hook 'before_commit'

原因分析

该错误表明事务大小超过了 MGR 的 group_replication_transaction_size_limit 限制。

问题发生机制

1. 事务过大：DELETE 操作涉及的数据量过大，生成的事务日志超过了 MGR 的事务大小限制
2. MGR 限制：默认 group_replication_transaction_size_limit 为 150MB（150000000 字节）
3. 复制阻塞：当事务超过限制时，MGR 会阻止事务提交，导致错误

查看当前限制

SHOW VARIABLES LIKE 'group_replication_transaction_size_limit';

+------------------------------------------+-----------+
| Variable_name                            | Value     |
+------------------------------------------+-----------+
| group_replication_transaction_size_limit | 150000000 |
+------------------------------------------+-----------+

解决方案

方案一：分批删除（推荐）

将大事务拆分为多个小事务执行：

-- 方式1：按时间分批删除
SET @batch_size = 10000;
SET @deleted = 1;

WHILE @deleted > 0 DO
    DELETE FROM mytable 
    WHERE create_time < '2026-01-01' 
    LIMIT @batch_size;
    SET @deleted = ROW_COUNT();
    SELECT SLEEP(0.1);  -- 短暂休眠，减轻压力
END WHILE;

-- 方式2：按 ID 分批删除
SET @batch_size = 10000;

REPEAT
    DELETE FROM mytable 
    WHERE create_time < '2026-01-01' 
    LIMIT @batch_size;
    SELECT ROW_COUNT() INTO @deleted;
    SELECT @deleted;
UNTIL @deleted = 0 END REPEAT;

方案二：临时调整事务大小限制

如确需执行大事务，可临时调整限制（注意：需要重启 MGR 组复制）：

-- 查看当前限制
SHOW VARIABLES LIKE 'group_replication_transaction_size_limit';

-- 临时调整限制（单位：字节，例如 1GB = 1073741824）
SET GLOBAL group_replication_transaction_size_limit = 1073741824;

-- 或者在 my.cnf 中配置永久生效
-- group_replication_transaction_size_limit = 1073741824

⚠️ 注意：增大事务限制可能影响组复制性能，建议仅在必要时使用。

方案三：使用 pt-archiver 工具

Percona Toolkit 的 pt-archiver 工具可以安全地分批归档/删除数据：

# 安装 percona-toolkit
# 分批删除数据
pt-archiver \
    --source h=localhost,D=database,t=mytable \
    --purge \
    --where "create_time < '2026-01-01'" \
    --limit 10000 \
    --commit-each \
    --progress 10000 \
    --statistics

参数说明：

验证检查

检查当前事务大小

-- 查看即将删除的数据量
SELECT COUNT(*) FROM mytable WHERE create_time < '2026-01-01';

-- 估算事务大小（大致）
SELECT 
    COUNT(*) as row_count,
    SUM(data_length + index_length) / COUNT(*) as avg_row_size
FROM mytable 
WHERE create_time < '2026-01-01';

监控 MGR 状态

-- 查看组成员状态
SELECT * FROM performance_schema.replication_group_members;

-- 查看组复制统计
SELECT * FROM performance_schema.replication_group_member_stats;

-- 查看事务大小限制
SHOW VARIABLES LIKE 'group_replication_transaction_size_limit';

预防措施

1. 定期清理策略

• 建立定期数据归档/清理机制
• 避免数据量累积到需要大批量删除

2. 监控告警

-- 监控大表数据量
SELECT 
    table_name,
    table_rows,
    data_length / 1024 / 1024 / 1024 as data_size_gb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY data_length DESC;

3. 设计优化

• 按时间分区表，便于快速清理历史数据
• 使用归档表存储历史数据

-- 按时间分区示例
CREATE TABLE mytable (
    id BIGINT PRIMARY KEY,
    create_time DATETIME,
    ...
) PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 清理旧分区（瞬间完成）
ALTER TABLE mytable DROP PARTITION p2024;

总结

MGR 的事务大小限制是为了保证组复制性能和稳定性。遇到 before_commit 错误时：

1. 首选分批删除，将大事务拆分为小事务
2. 次选调整限制，临时增大事务大小限制
3. 长期优化，建立分区表和定期清理机制

遵循"小事务、多批次"原则，可以有效避免此类问题。