Skip to content

Latest commit

 

History

History
245 lines (136 loc) · 20.3 KB

主从.md

File metadata and controls

245 lines (136 loc) · 20.3 KB
Raw

从库延迟解决办法

MySql数据库从库同步的延迟问题

  1. MySQL数据库主从同步延迟原理mysql主从同步原理：主库针对写操作，顺序写binlog，从库单线程去主库顺序读”写操作的binlog”，从库取到binlog在本地原样执行（随机写），来保证主从数据逻辑上一致。

mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。

DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。

有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。

  1. MySQL数据库主从同步延迟是怎么产生的？

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高次要原因：读写binlog带来的性能影响，网络传输延迟。

MySql数据库从库同步的延迟解决方案

架构方面

  1. 业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

  2. 单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。

  3. 服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。

  4. 不同业务的mysql物理上放在不同机器，分散压力。

  5. 使用比主库更好的硬件设备作为slave总结，mysql压力小，延迟自然会变小。

硬件方面

1.采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。

2.存储用ssd或者盘阵或者san，提升随机写的性能。

3.主从间保证处在同一个交换机下面，并且是万兆环境。

总结，硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。

mysql主从同步加速

  1. sync_binlog在slave端设置为0

数据一致性问题。

  1. –logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

  2. 直接禁用slave端的binlog

  3. slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

从文件系统本身属性角度优化

master端修改linux、Unix文件系统中文件的etime属性， 由于每当读文件时OS都会将读取操作发生的时间回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响I/O性能。可以通过设置文件系统的mount属性，组织操作系统写atime信息，在linux上的操作为：打开/etc/fstab，加上noatime参数/dev/sdb1 /data reiserfs noatime 1 2 然后重新mount文件系统#mount -oremount /data

同步参数调整

主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率

sync_binlog

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。

对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是2或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。

默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。

innodb_flush_log_at_trx_commit

（这个很管用）抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

MySql数据库从库同步其他问题及解决方案

1). mysql主从复制存在的问题：

  • 主库宕机后，数据可能丢失
  • 从库只有一个sql Thread，主库写压力大，复制很可能延时

2). 解决方法：

  • 半同步复制---解决数据丢失的问题
  • 并行复制----解决从库复制延迟的问题

3). 半同步复制mysql semi-sync（半同步复制）半同步复制：

  • 5.5集成到mysql，以插件的形式存在，需要单独安装
  • 确保事务提交后binlog至少传输到一个从库
  • 不保证从库应用完这个事务的binlog
  • 性能有一定的降低，响应时间会更长
  • 网络异常或从库宕机，卡主主库，直到超时或从库恢复

4). 主从复制--异步复制原理、半同步复制和并行复制原理比较

c、并行复制mysql并行复制

  • 社区版5.6中新增
  • 并行是指从库多线程apply binlog
  • 库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置set global slave_parallel_workers=10;设置sql线程数为10

原理：从库多线程apply binlog在社区5.6中新增库级别并行应用binlog，同一个库数据更改还是串行的5.7版本并行复制基于事务组

主从同步

这里有一个非常重要的一点，就是从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行 SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。

而且这里还有另外一个问题，就是如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。

所以 MySQL 实际上在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。

这个所谓半同步复制，也叫semi-sync复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。

所谓并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

一般来说，如果主从延迟较为严重，有以下解决方案：

  • 分库，将一个主库拆分为多个主库，每个主库的写并发就减少了几倍，此时主从延迟可以忽略不计。
  • 打开 MySQL 支持的并行复制，多个库并行复制。如果说某个库的写入并发就是特别高，单库写并发达到了 2000/s，并行复制还是没意义。
  • 重写代码，写代码的同学，要慎重，插入数据时立马查询可能查不到。
  • 如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库。不推荐这种方法，你这么搞导致读写分离的意义就丧失了。

有数据库中间件帮忙在一段时间范围（500ms）内查就去主库读，过了这个时间段后就去从库查。

MySQL 并行复制演进及 MySQL 8.0 中基于 WriteSet 的优化

5.7中的并行复制————基于Group Commit 的并行复制

虽然5.6中的并行复制在大多数应用场景中对回放速度的提升不大，但是该架构却成为了后来MySQL并行复制的基础——既在Slave上并行回放RelayLog，SQL线程负责判断能否并行回放，并分配给Work线程回放。

在Slave上并行回放RelayLog，SQL线程负责判断能否并行回放，并分配给Work线程回放

5.6 中引入Group Commit技术，这是为了解决事务提交的时候需要fsync导致并发性不够而引入的。简单来说，就是由于事务提交时必须将Binlog写入到磁盘上而调用fsync，这是一个代价比较高的操作，事务并发提交的情况下，每个事务各自获取日志锁并进行fsync会导致事务实际上以串行的方式写入Binlog文件，这样就大大降低了事务提交的并发程度。5.6中采用的Group Commit技术将事务的提交阶段分成了 Flush, Sync, Commit 三个阶段，每个阶段维护一个队列，并且由该队列中第一个线程负责执行该步骤，这样实际上就达到了一次可以将一批事务的Binlog fsync到磁盘的目的，这样的一批同时提交的事务称为同一个Group的事务。

Group Commit的定义

Group Commit 虽然是属于并行提交的技术，但是却意外的解决了从机上事务并行回放的一个难题————既如何判断哪些事务可以并行回放。如果一批事务是同时Commit的，那么这些事务必然不会互斥的持有锁，也不会有执行上的相互依赖，因此这些事务必然可以并行的回放。

因此MySQL 5.7 中引入了新的并行回放类型， 由参数 slave_parallel_type决定，默认值DATABASE将会采用5.6版本中的SCHEMA级别的并行回放，设置为 LOGICAL_LOCK 则会采用基于GroupCommit的并行回放，同一个Group内的事务将会在Slave上并行回放。

为了标记事务所属的组，MySQL 5.7 版本在产生 Binlog 日志时会有两个特殊的值记录在Binlog Event中， last_committed 和 sequence_number , 其中 last_committed 指的是该事务提交时，上一个事务提交的编号，sequence_number 是事务提交的序列号，在一个Binlog文件内单调递增。如果两个事务的 last_committed 值一致，这两个事务就是在一个组内提交的。

5.7 中引入的基于Logical_Lock极大的提高了在主机并发压力比较大的情况下，从机上的回放速度。基本上做到了主机上如何提交的，在从机上如何回放。

MySQL MGR 中的 WriteSet

在5.7中基于逻辑时钟 Logical_Clock 的并行复制任然有不尽人意的地方，必须是在主上并行提交的事务才能在从上并行回放，如果主上并发压力不大，那么就无法享受到并行复制带来的好处。5.7 中引入了binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 两个参数，通过让Binlog在执行 fsync 前等待一小会来提高Master上组提交的比率。但是无论如何，从上并行回放的速度还是取决于主上并行提交的情况。

必须是在主上并行提交的事务才能在从上并行回放，如果主上并发压力不大，那么就无法享受到并行复制带来的好处。让Binlog在执行 fsync 前等待一小会来提高Master上组提交的比率

MySQL 8.0中引入了一种新的机制来判断事务能否并行回放，通过检测事务在运行过程中是否存在写冲突来决定从机上的回放顺序，这使得从机上的并发程度不再依赖于主机。

事实上，该机制在 MySQL 5.7.20 版本中就已经悄悄的应用了。5.7.20版本引入了一个重要的特性： Group Replication，通过Paxso协议在多个MySQL节点间分发binlog，使得一个事务必须在集群内大多数节点(N/2+1)上提交成功才能提交。为了支持多主写入，MySQL MRG 在Binlog分发节点完成后，通过一个 Certify 阶段来决定Binlog中的事务是否写入RelayLog 中。这个过程中，Certify 阶段采用的就是WriteSet的方式验证事务之间是否存在冲突，同时，在写入RelayLog 时会将没有冲突的事务的 last_committed 值设置为相同的值。

有趣的是，在 Secondary 节点的 RelayLog 中, 这些事务有着相同的 last_committed 值，也就是说这些事务在MGR集群中，回放的时候可以以并行的方式回放。

MGR中，使用的正是 WriteSet 技术检测不同事务之间是否存在写冲突，并重规划了事务的并行回放，这一技术在8.0中被移到了Binlog生成阶段，并采用到了主从复制的架构中。

MySQL 8.0 中的并行复制

说了这么多，终于讲到 MySQL 8.0 ， 通过以上描述，读者应该对 MySQL 8.0 中并行复制的优化的原理有了一个大致的轮廓。通过基于 WriteSet 的冲突检测，在主机上产生 Binlog 的时候，不再基于组提交，而是基于事务本身的更新冲突来确定并行关系。

相关的 MySQL 参数

MySQL 8.0 中引入参数 binlog_transaction_depandency_tracking 用于控制如何决定事务的依赖关系。该值有三个选项：默认的 COMMIT_ORDERE 表示继续使用5.7中的基于组提交的方式决定事务的依赖关系；WRITESET 表示使用写集合来决定事务的依赖关系；还有一个选项 WRITESET_SESSION 表示使用 WriteSet 来决定事务的依赖关系，但是同一个Session内的事务不会有相同的 last_committed 值。

从检测条件上看，该特性依赖于 主键和唯一索引，如果事务涉及的表中没有主键且没有唯一非空索引，那么将无法从此特性中获得性能的提升。除此之外，还需要将 Binlog 格式设置为 Row 格式。

结论

从 MySQL Hight Availability 的测试中可以看到，开启了基于 WriteSet 的事务依赖后，对Slave上RelayLog回放速度提升显著。Slave上的 RelayLog 回放速度将不再依赖于 Master 上提交时的并行程度，使得Slave上可以发挥其最大的吞吐能力， 这个特性在Slave上复制停止一段时间后恢复复制时尤其有效。

这个特性使得 Slave 上可能拥有比 Master 上更大的吞吐量，同时可能在保证事务依赖关系的情况下，在 Slave 上产生 Master 上没有产生过的提交场景，事务的提交顺序可能会在 Slave 上发生改变。 虽然在5.7 的并行复制中就可能发生这种情况，不过在8.0中由于 Slave 上更高的并发能力，会使该场景更加常见。 通常情况下这不是什么大问题，不过如果在 Slave 上做基于 Binlog 的增量备份，可能就需要保证在 Slave 上与Master 上一致的提交顺序，这种情况下可以开启 slave_preserve_commit_order 这是一个 5.7 就引入的参数，可以保证 Slave 上并行回放的线程按 RelayLog 中写入的顺序 Commit。

Q: 感觉对大事务还是不能优化并行度：

1.譬如有10w个事务，每个事务更新1行，事务是顺序执行的：一个事务完了下一个事务再更新不同的行；从库开启了10个线程应用追主机，则从库可以并发更新，并发度是10

2.但假如有一个事务更新10w 行，则从库虽然配置了10个线程，但也只能一个线程追了

A: 是的，oltp系统都是对小事务友好的

mysql主从同步时，在master端进行事务操作时，是不是commit了后才会同步到slave？

Binlog在2PC中充当了事务的协调者（Transaction Coordinator）。由Binlog来通知InnoDB引擎来执行prepare，commit或者rollback的步骤。事务提交的整个过程如下：

  1. 协调者准备阶段(Prepare Phase) 告诉引擎做Prepare，InnoDB更改事务状态，并将Redo Log刷入磁盘。
  2. 协调者提交阶段(Commit Phase) 2.1 记录协调者日志，即Binlog日志。 2.2 告诉引擎做commit。

同步方式如果是采用binlog方式，如果事务已经完成了commit操作，那这个事务是会正常同步到从库的，但是如果主库写下binlog，但是在2.2 主库挂掉了，很明显innodb还没来的及做commit操作，这个事务也还是会同步到从库的，当主库重新启动后，会做recovery操作，利用binlog中的events和innodb 中事务状态，判断是做回滚还是前滚操作，很明显binlog中有的，innodb 中处于prepare状态的都应该提交，binlog中没有的，都应该回滚掉。这样才能确保主从一致性

Mysql 5.7 主从复制的多线程复制配置方式

在从库上停止复制

mysql> stop slave;

先看下现在 slave 的并发类型，通过变量slave_parallel_type的值来获得，这个变量用来决定如何使用多线程复制

mysql> show variables like 'slave_parallel_type';

默认是datebase，每个线程只能处理一个数据库

配置成基于逻辑时钟的方式

mysql> set global slave_parallel_type='logical_clock';

设置复制线程的数量

先看下当前的并发数量，通过变量slave_parallel_workers的值来获得，这个变量用来决定并发处理的线程数

mysql> show variables like 'slave_parallel_workers';

现在是 0，我们把他改成 4

mysql> set global slave_parallel_workers=4;

启动复制

mysql> start slave;

验证配置结果

mysql> show processlist;

参考链接

  • MySQL · 特性分析 · LOGICAL_CLOCK 并行复制原理及实现分析: LOGICAL CLOCK： commit-parent、 Lock-Based。无论是Commit-Parent-Based还是Lock-Based，Master端一个事务T1和其commit后才开始的事务T2在Slave端都不会被并发回放，T2一定会等T1执行结束才开始回放。能够保证Causal Consistency
    • slave-parallel-type=LOGICAL_CLOCK : Commit-Parent-Based模式(同一组的事务[last-commit相同]，没有锁冲突. 同一组，肯定没有冲突，否则没办法成为同一组)
    • slave-parallel-type=LOGICAL_CLOCK : Lock-Based模式(即便不是同一组的事务，只要事务之间没有锁冲突[prepare阶段]，就可以并发。 不在同一组，只要N个事务prepare阶段可以重叠，说明没有锁冲突)