mysql主从延迟过高

mysql主从延迟过高

怎么发现主从有延迟的，因为架构是通过mycat做的主从读写分析，一主一从。在业务上有实时修改配置的需求，发现改了配置之后不生效，但是过一会就好了。就怀疑是主从有延迟。
我这里mysql版本用的是5.7.34

一、首先说说主从复制的大概原理

#Master
当master库发生变化，会按照事件顺序写到bin-log中，当Slave连接到Master后，Master 会为 Slave 开启bin-log dump线程，当Master的 binlog 发生变化，binlog dump会通知Slave，并将相应事务binlog发送给Slave。
#Slave
当主从同步开启的时候，Slave 上会开启两个线程:
I/O线程：该线程连接到Master，Master 的 bin-log dump线程会将binlog内容发送给I/O线程，I/O线程接受到binlog 再将内容写到本地的 relay log。
sql线程：该线程读取到I/O线程写入的relay log，根据relay log 的内容，对Slave做相应的操作。

看看大佬们讲解的
https://www.cnblogs.com/yaokaka/p/14078630.html

二、主从复制延迟原因以及解决方案

了解了大概同步原理，分析下主从延迟的原因

1、相关参数：
首先在服务器上执行show slave satus;可以看到很多同步的参数：
Master_Log_File： SLAVE中的I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos： 在当前的主服务器二进制日志中，SLAVE中的I/O线程已经读取的位置
Relay_Log_File： SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos： 在当前的中继日志中，SQL线程已读取和执行的位置
Relay_Master_Log_File： 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
Slave_IO_Running： I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running： SQL线程是否被启动
Seconds_Behind_Master： 从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。

代表从库同步有延迟情况出现的参数

show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大
show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大
mysql的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害
2、主从延迟的原因
①、大事务的执行，事务执行完主库才会写binlog，如果该事务执行了10min，那么从库也就延迟了10min；当主库DDL并发较高时，超过了从库Sql线程所能承受的范围，延时就产生了。还有原因就是从库大型query产生了锁等待。
②、主库写binlog是顺序写，从库单线程从主库中顺序读binlog，从库取到binlog后在本地执行。MySQL的主从复制都是单线程操作，主库是顺序写，效率很高，从库也是顺序读，效率也很高，但是拿到数据后变成随机操作，成本会提高，造成了延时
③、从库在同步数据的过程中，可能回跟其他查询的线程发生锁抢占的情况，需要等读查询结束后，才能进行写操作，导致不能及时同步到从库中
④、从库和主库之间进行日志传输时，如果网络不是很好，网络延时也可能造成数据同步延迟。

主要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高
次要原因：读写binlog带来的性能影响，网络传输延迟

三、解决方案

1、架构方面
①、业务的持久层采用分库架构，mysql服务能力水平扩展，分散压力
②、单个库读写分离，一主多从，主写读从，分散压力。这样从库比主库压力高，保护主库
③、服务在业务和DB之间加入memcache 和 redis 的cache层，降低读的压力
④、不同业务的mysql放在不同的物理机，降低压力
⑤、使用比主库更好的硬件设备，Mqsql压力小，延迟就减少了
2、硬件方面
①、采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。
②、存储用ssd或者盘阵或者san，提升随机写的性能。
③、主从间保证处在同一个交换机下面，并且是万兆环境。
3、Mysql主从同步加速
①、sync_binlog：在Slave端设置为0.
②、log-slave-updates： 从服务器从主服务器接受的更新日志不计入二进制日志
③、直接禁用 Slave 的binlog
④、innodb_flush_log_at_trx_commit：2，Slave端，如果存储引擎是innodb
⑤、sync_binlog：
	同步参数调整主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率
⑥、使用MySQL5.7版本，MySQL5.7版本后引入新的机制，即基于组提交的并行复制，设置参数:
	1、slave_parallel_workers>0
	2、slave_parallel_type='LOGICAL_CLOCK'

参数详解

1、sync_binlog
该参数控制着二进制日志写入磁盘的过程。

该参数的有效值为0 、1、N：

0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。

1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。

N：每写N次操作系统缓冲就执行一次刷新操作。

将这个参数设为1以上的数值会提高数据库的性能，但同时会伴随数据丢失的风险。
二进制日志文件涉及到数据的恢复，以及想在主从之间获得最大的一致性，那么应该将该参数设置为1，但同时也会造成一定的性能损耗。
2、innodb_flush_log_at_trx_commit
值为0 : 提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
值为1 : 提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
值为2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。
可以看到，只有1才能真正地保证事务的持久性，但是由于刷新操作 fsync() 是阻塞的，直到完成后才返回，我们知道写磁盘的速度是很慢的，因此 MySQL 的性能会明显地下降。

如果不在乎事务丢失，0和2能获得更高的性能。
3、slave_parallel_workers、slave_parallel_type
参数 slave_parallel_workers 设置并行线程数，由参数 slave-parallel-type 来控制并行复制策略
slave_parallel_workers ：
从单线程复制到最新版本的多线程复制，中间的演化经历了好几个版本。其实说到底，所有的多线程复制机制，都是要把只有一个线程的 sql_thread，拆成多个线程。真正更新日志的，变成了 worker 线程。而 worker 线程的个数，就是由参数 slave_parallel_workers 决定的。
slave_parallel_type：
配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；
配置为 LOGICAL_CLOCK，表示使用基于组提交的并行复制策略；

基于组提交的并行复制具体流程如下：
在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id+1；commit_id 直接写到 binlog 里面；
传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行；
这一组全部执行完成后，coordinator 再去取下一批执行。

参数3可以在5.7版本之后修改为并行复制以及worker数量。参数1、2视情况而定。