MySQL主从复制(Replication)架构从库延迟复制故障分析及处理

一.主从复制延迟的监控方法

1.什么是主从延迟

    所谓的主从延迟，指的是主库发生了操作，从库"很久"时间才跟上来。

    这个"很久"时间是我们自定义的时间阈值，取决于公司对数据延迟的容忍度，比如有的公司认为大于10秒就觉得延迟很大了。

2.主从复制延迟怎么监控

    如下所示，我们可以基于"Seconds_Behind_Master"参数来大致判断主从复制有或者没有延迟的情况:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master"
                Seconds_Behind_Master: 0
        [root@docker201.oldboyedu.com ~]# 

    为什么说"Seconds_Behind_Master"参数只能用来大致判断主从复制有或者没有延迟的情况呢?
        (1)"Seconds_Behind_Master"是对比二进制日志从主库到从库之后的一个时间戳差值来计算的秒数，换句话说，就是从库的IO线程从主库拉取二进制日志并落地到从库的中继日志的间隔时间;
        (2)实际上主从是否有延迟，是要看主库做的操作，从库的SQL线程是何时应用的中继日志，如果是同步写入则说明是没有延迟的;
        (3)综上所述，"Seconds_Behind_Master"只能判断IO线程拉取主库的二进制日志是否有延迟，而真正是否有延迟的还得看SQL线程应用中继日志是否实时，因此我们说尽管"Seconds_Behind_Master"参数为"0"，也不一定代表就没有延迟。

    主从复制延迟怎么监控?
        (1)先观察"Seconds_Behind_Master"是否为0，如果为0说明从库的IO线程是实时拉取主库日志的，若大于0，则说明从库的IO线程在拉取日志时就已经延迟了;
        (2)评估主从延迟更加精确的指标是延迟了多少"日志量"。换句话说，我们要监控主库执行的日志量位置点和从库执行的中继日志量的位置点对比结果。本质上还是SQL线程回放中继日志的速度是否和主库的记录的二进制日志位置点所对应。

    温馨提示:
        我们监控主从复制延迟的目的是为了找到到底是谁导致的延迟。而后再去有针对性的去解决延迟。

3.如何计算延时日志量

    如下图所示，分别查看主库的二进制日志位置信息和从库的中继日志执行的位置信息，在主库"File"对应的二进制日志名称和从库"Master_Log_File"记录的主库的二进制名称相同的情况下，我们可以得到以下计算延迟日志量的公式:
        延迟日志量 = 从库的"Read_Master_Log_Pos" - 主库的"Position"

    我们可以使用下面的命令来分别查看主库的二进制日志信息，以及从库拉取主库的二进制日志信息:
        主库查看当前二进制日志的执行位置点:
            [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock
            Welcome to the MySQL monitor.  Commands end with ; or \g.
            Your MySQL connection id is 15
            Server version: 5.7.31-log MySQL Community Server (GPL)

            Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

            Oracle is a registered trademark of Oracle Corporation and/or its
            affiliates. Other names may be trademarks of their respective
            owners.

            Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

            mysql> 
            mysql> SHOW MASTER STATUS;
            +------------------+----------+--------------+------------------+-------------------+
            | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
            +------------------+----------+--------------+------------------+-------------------+
            | mysql-bin.000002 |      694 |              |                  |                   |
            +------------------+----------+--------------+------------------+-------------------+
            1 row in set (0.00 sec)

            mysql> 

        从库查看已经执行的中继日志对应主库的二进制日志位置点:
            [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | head 
            *************************** 1. row ***************************
                           Slave_IO_State: Waiting for master to send event
                              Master_Host: 172.200.1.201
                              Master_User: copy
                              Master_Port: 3307
                            Connect_Retry: 10
                          Master_Log_File: mysql-bin.000002
                      Read_Master_Log_Pos: 694
                           Relay_Log_File: docker201-relay-bin.000003
                            Relay_Log_Pos: 320
            [root@docker201.oldboyedu.com ~]# 
            [root@docker201.oldboyedu.com ~]# 
            [root@docker201.oldboyedu.com ~]# cat /oldboyedu/data/mysql3308/relay-log.info 
            7
            ./docker201-relay-bin.000003
            320
            mysql-bin.000002
            694
            0
            0
            1

            [root@docker201.oldboyedu.com ~]# 

二.主从复制中延迟常见的故障原因分析

1.主库延迟

    外部因素:
        (1)网络因素:
            解决方案: 可以加带宽。
        (2)硬件配置:
            解决方案: 提升硬件配置。
        (3)主库业务繁忙:
            解决方案: 
                1)可以考虑拆分业务进行(分布式)组件分离，基于垂直拆分(可以基于数据库区分业务，垂直拆分可以理解为把各个数据库进行拆分)，水平拆分(指的是将一张表拆分成多个部分);
                2)大事务的拆分，比如某个事务要更新1000w行数据，我们得思考是否可以将该事物拆分成20个小事务，比如每个事务只更新50w行数据，因为一个事务越大，其占用的数据锁定时间就越长，会影响到其他事务并发;
        (4)从库数量过多:  
            解决方案: 通常情况下，主库的从库数量应该合理控制在3个左右，如果非要做个多从库，可以考虑多级主从关系(一个主库搭建2-3个从库，而后在基于这三个从库搭建主从复制关系);

    内部因素:
        (1)主库发生已提交的事物后二进制日志更新不及时:
            解决方案: 将"sync_binlog"的参数设置为1。
        (2)在MySQL5.7之前默认是没有开启GTID，主库可以并发事务，但是dump线程传输时串行的，所以在事务量大，大事务场景的情况下会出现比较严重延时;
            解决方案: 
                1)在MySQL 5.6及以上版本出现了GTID特性，事务在主从的全局范围内就有了唯一性标志，在MySQL 5.6版本需要手工开启GTID特性但并不成熟;
                2)在MySQL 5.7+无需手工开启，MySQL内部也会自动生成匿名的GTID，当然，我们也可以手动开启，这不过就会使用MySQL实例的UUID作为GTID的命名规则的一部分(有关GTID的格式在之前的章节有介绍我就不啰嗦了);
            综上所述，有了GTID之后，就可以实现并行传输二进制日志，因此在MySQL 8.0版本已经我很流行使用基于GTID来实现主从复制。

    温馨提示:
        即使MySQL 5.6后引入了GTID等优秀特性，但我们依然需要尽可能的减少大事务，以及锁的影响。
        我这里举一个不太恰当的例子哈，大家笑一笑就好： 比如我们去"望京SOHO"上班需要坐电梯，本来一个电梯能做20个人，但只上去了三个大胖纸，电梯就不能承载其他人了。这种好比我们MySQL数据库中的大事务，正是由于这些大事务的存在导致"运输"效率较慢!

2.从库延迟

    外部因素:
        (1)经常网络抖动;
            解决方案: 增大网络带宽。
        (2)从库硬件配置底:
            解决方案: 硬件最好将配置和主库的配置一致。
        (3)参数配置不合适:
            解决方案: 尽量预留出足够的内存和IO资源，关于CPU资源MySQL从库使用的相对较少，可适当调低。

    内部因素:
        (1)IO线程:
            可能存在的瓶颈: 
                写"中继日志时可能存在I/O性能。
            解决方案:
                建议选择15000r/s的机械硬盘，如果有必要也可以更换为固态硬盘以提升I/O性能。
        (2)SQL线程
            可能存在的瓶颈:
                SQL线程在"回放"中继日志时是串行执行，因此效率较低。
            解决方案:
                (1)在开启GTID的前提下，我们可以让SQL线程有串行改为并行"回放"中继日志以提升效率。
                (2)在MySQL5.6版本中的GTID是基于database级别实现SQL线程并发，而MySQL 5.7版本中的GTID是基于逻辑适中(LOGICAL_CLOCK)保证了同库级别下的事务顺序问题，所以可以理解为基于事务级别的并发"回放"。感兴趣的小伙伴可自行参考"MTS"。关于并发度设置的问题加以等效于MySQL物理CPU的核心数。有关并发度通常会和CPU的核心数有关，而MySQL核心通常不会超过64core，因为MySQL默认使用的就是64core，由于MySQL并不是特别吃CPU资源，因此生产环境有关MySQL的CPU核心配置在32core即可。
                (3)当然，有关GTID性能在MySQL 8.0可能会进一步的完善，生产环境建议还是选择MySQL 5.7+版本。

    温馨提示:
        即使MySQL有以上的自带优化机制，我们还是要注意对于大事务处理的问题，以及锁的问题。换句话说，就算你把MySQL数据库优化到极致，如果人为写的SQL比较LOW照样体现不出来性能优势，因此我们说优化数据库的根本是有优化人。