MySQL深入23-备库延迟好几个小时
# MySQL深入23-备库延迟好几个小时
备库延迟的原因有:备库性能差、备库压力大、大事务。但是这些无论是偶发性的查询压力,还是备份,对备库延迟的影响一般是分钟级的,而且在备库恢复恢复正常以后能够追上来。
但是如果备库执行日志的速度持续低于主库生成日志的速度,那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说,备库很可能永远都追不上主库的节奏。
# 备库并行复制能力
主备的并行复制能力,需要关注两个点:
- 客户端写入主库。
- 备库上sql_thread执行中转日志。
对于主库而言,影响并发度的原因就是各种锁,除去所有并发事务更新同一行(热点行)这种场景外,InnoDB对业务并发度的支持还是很友好的,所以客户端写入主库这个过程,总体吞吐量高。
对于备库而言,如果sql_thread执行中转日志是用单线程的话,就会导致备库应用日志不够快,造成主备延迟。所以MySQL要从单线程复制变更到最新版本的多线程复制。
从本质来讲,我们需要将一个线程的sql_thread拆成多个线程:
上图中coordinator就是原来的sql_thread,不过现在它不再是直接更新数据了,而是只负责读取中转日志和分发事务。真正更新日志的,变成了worker线程。而work线程的个数,就是由参数slave_parallel_workers 决定的。
32核物理机的情况,这个数值设置为8-16最合适,需要把一部分CPU资源留给备库的读查询。
能否轮询分发事务
coordinator能否按照轮训的方式,把第一个事务分给worker_1,第二个事务分给worker_2呢?
其实是不行的。因为,事务被分发给 worker 以后,不同的 worker 就独立执行了。但是,由于 CPU 的调度策略,很可能第二个事务最终比第一个事务先执行。而如果这时候刚好这两个事务更新的是同一行,也就意味着,同一行上的两个事务,在主库和备库上的执行顺序相反,会导致主备不一致的问题。
能否将同一个事务的多个更新语句,分给不同的worker执行
也是不行的,一个事务更新了表 t1 和表 t2 中的各一行,如果这两条更新语句被分到不同 worker 的话,虽然最终的结果是主备一致的,但如果表 t1 执行完成的瞬间,备库上有一个查询,就会看到这个事务“更新了一半的结果”,破坏了事务逻辑的隔离性。
针对上面的两个问题,coordinator 在分发的时候,需要满足以下这两个基本要求:
- 不能造成更新覆盖。这就要求更新同一行的两个事务,必须被分发到同一个 worker 中。
- 同一个事务不能被拆开,必须放到同一个 worker 中。
# 按表分发策略
按表分发事务的基本思想: 如果两个事务更新不同的表,它们就可以并行。因为数据是存储在表里的,所以按表分发,可以保证两个worker不会更新同一行。对于跨表的事务,还是要把两张表放在一起考虑。
可以看到,每个worker线程对应一个hash表,用于保存当前正在这个worker的“执行队列”里的事务所涉及的表。hash表的key是“库名.表名”,value 是一个数字,表示队列中有多少个事务修改这个表。
在有事务分配给 worker 时,事务里面涉及的表会被加到对应的 hash 表中。worker 执行完成后,这个表会被从 hash 表中去掉。
假设在上图情况下,中转日志中读入一个新事务T,这个事务修改的行涉及到表t1和t3,下面来分析一下分配规则:
由于事务T中涉及修改表t1,而 worker_1 队列中有事务在修改表 t1,事务 T 和队列中的某个事务要修改同一个表的数据,这种情况我们说事务 T 和 worker_1 是冲突的。
按照这个逻辑,顺序判断事务 T 和每个 worker 队列的冲突关系,会发现事务 T 跟 worker_2 也冲突。
事务 T 跟多于一个 worker 冲突,coordinator 线程就进入等待。
每个 worker 继续执行,同时修改 hash_table。假设 hash_table_2 里面涉及到修改表 t3 的事务先执行完成,就会从 hash_table_2 中把 db1.t3 这一项去掉。
这样 coordinator 会发现跟事务 T 冲突的 worker 只有 worker_1 了,因此就把它分配给 worker_1。(因为更新同一个表的事务必须放在同一个worker中)
coordinator 继续读下一个中转日志,继续分配事务。
根据上面的分析,每个事务在分发的时候,跟所有worker的冲突关系如下:
- 如果跟所有 worker 都不冲突,coordinator 线程就会把这个事务分配给最空闲的 woker;
- 如果跟多于一个 worker 冲突,coordinator 线程就进入等待状态,直到和这个事务存在冲突关系的 worker 只剩下 1 个;
- 如果只跟一个 worker 冲突,coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。
按表分发的方案,在碰到热点表,比如所有的更新事务都会涉及到某一个表的时候,所有事务都会被分配到同一个 worker 中,就变成单线程复制了。
# 按行分发策略
为了解决热点表的并行复制问题,就需要一个按行复制的方案。该方案的核心思想:如果两个事务没有更新相同的行,它们在备库上可以并行执行。显然,这个模式要求 binlog 格式必须是 row。
这时候我们判断事务和worker冲突的规则就是:修改同一行而不是修改同一个表。
按行复制的数据结构和按表复制的数据结构差不多,为每个worker,分配一个 hash 表。只是要实现按行分发,这时候的 key,就必须是“库名 + 表名 + 唯一键的值”。
但是这个“唯一主键”是有可能会和“普通索引”冲突的:
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB;
insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);
如果两个session被分到不同的worker中,假设session B先执行,这时候 id=1 的行的 a 的值还是 1,就会报唯一键冲突。
因此,基于行的策略,事务 hash 表中还需要考虑唯一键,即 key 应该是“库名 + 表名 + 索引 a 的名字 +a 的值”。
比如在表t1中执行update t1 set a=1 where id=2语句,binlog里面就记录了整行的数据修改前后各个字段的值:
- key=hash_func(db1+t1+“PRIMARY”+2), value=2; 这里 value=2 是因为修改前后的行 id 值不变,出现了两次。
- key=hash_func(db1+t1+“a”+2), value=1,表示会影响到这个表 a=2 的行,修改前的数值。
- key=hash_func(db1+t1+“a”+1), value=1,表示会影响到这个表 a=1 的行,修改后的数值。
相比于按表并行分发策略,按行并行策略在决定线程分发的时候,需要消耗更多的计算资源:
- 耗费内存。比如一个语句要删除 100 万行数据,这时候 hash 表就要记录 100 万个项。
- 耗费 CPU。解析 binlog,然后计算 hash 值,对于大事务,这个成本还是很高的。
同时无论是按表分发还是按行分发都存在约束条件:
- 要能够从 binlog 里面解析出表名、主键值和唯一索引的值。也就是说,主库的 binlog 格式必须是 row;
- 表必须有主键;
- 不能有外键。表上如果有外键,级联更新的行不会记录在 binlog 中,这样冲突检测就不准确。
# 5.6版本的并行复制策略
该版本支持的粒度是按库并行,上述的按表分发和按库分发的key里面都会有库名。
相比于按表和按行分发,这个策略有两个优势:
- 构造 hash 值的时候很快,只需要库名;而且一个实例上 DB 数也不会很多,不会出现需要构造 100 万个项这种情况。
- 不要求 binlog 的格式。因为 statement 格式的 binlog 也可以很容易拿到库名。
# MariaDB的并行复制策略
MariaDB 的并行复制策略利用的就是这个特性:
能够在同一组里提交的事务,一定不会修改同一行;
主库上可以并行执行的事务,备库上也一定是可以并行执行的。
在实现上,MariaDB是这么做的:
- 在一组里面一起提交的事务,有一个相同的 commit_id,下一组就是 commit_id+1;
- commit_id 直接写到 binlog 里面;
- 传到备库应用的时候,相同 commit_id 的事务分发到多个 worker 执行;
- 这一组全部执行完成后,coordinator 再去取下一批。