MYSQL篇-主从复制的延迟问题

同步延迟原因：

Slave相对master出现延迟大，根本原因是master上提交事务后是多线程并发写入数据的，而在slave上，SQL线程是以单线程方式对rely-log中的sql进行重放。也就是说，master上是多线程写入数据，slave上是单线程写入，在高并发高负载时，slave就容易出现一定延迟，或落后于master。

常见的延迟场景：

场景1：slave库所在的机器资源有限或不足

因为slave从master同步binlog过来，是由一个IO线程和一个SQL线程来完成，这两个线程会消耗IO资源，如果在高负载情况下，资源不足就容易会导致数据延迟，所以slave用到的机器配置规格最好是大于等于master机器的配置规格。

场景2：主实例的TPS（Transaction Per Second）过高

TPS：Transaction Per Second，平均每秒事务数，指的是每秒完成的事务数，并不是正在处理的事务数。事务是要靠虚拟用户做出来的，假如1个虚拟用户在1秒内完成1笔事务，那么TPS明显就是1。如果某笔业务响应时间是1 ms，那么1个用户在1s内能完成1000笔事务，TPS就是1000了。如果某笔业务响应时间是1s，那么1个用户在1s内只能完成1笔事务，要想达到1000 TPS，至少需要1000个用户。因此可以说1个用户可以产生1000 TPS，1000个用户也可以产生1000 TPS，无非是看响应时间快慢。

解决思路：

如果TPS过高，则需要对业务进行优化或拆分，保证master实例的TPS不会导致slave实例出现延迟。

场景3：master执行了很多大事务

如果是由于出现大事务导致延迟时，可查看slave status确认。

确认Seconds_Behind_Master不断变化，而Exec_Master_Log_Pos却保持不变，说明slave的SQL线程在执行一个大事务或DDL操作。如果master在执行一个涉及数据量非常大的事务操作时，会生成大量的Binlog数据并同步到slave，slave在进行重放操作时就会花很长时间，导致出现延迟。

可执行 show processlist 命令来定位具体的SQL语句。看看是否存在大事务（update、delete、insert…select、replace…select等），或 DDL（alter，repair，create等）语句执行时间很长。

解决思路：

1、将大事务拆分为小事务分别执行。比如在delete语句中增加where条件，限制每次删除的数据量，将一次删除操作拆分为多次数据量较小的删除操作进行。

2、业务低峰期执行引起延迟的DDL语句。

排查：

1、查看延迟情况

比如最近公司上有一台slave延迟很大，看了下Seconds_Behind_Master值为10000多，如下：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.80.50.86
                  Master_User: replicator
                  Master_Port: 3002
                Connect_Retry: 60
              Master_Log_File: mysql-bin.154032
          Read_Master_Log_Pos: 122466613
               Relay_Log_File: slave-relay.169239
                Relay_Log_Pos: 121924037
        Relay_Master_Log_File: mysql-bin.154032
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
						 .....
          Exec_Master_Log_Pos: 121923824
					     .....
        Seconds_Behind_Master: 10948
						 .....
      Slave_SQL_Running_State: Waiting for dependent transaction to commit

过了差不多几十秒后，再次查询slave状态， Exec_Master_Log_Pos值始终保持是121923824。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.80.50.86
                  Master_User: replicator
                  Master_Port: 3002
                Connect_Retry: 60
              Master_Log_File: mysql-bin.154032
          Read_Master_Log_Pos: 122495939
               Relay_Log_File: slave-relay.169239
                Relay_Log_Pos: 121924037
        Relay_Master_Log_File: mysql-bin.154032
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
						 .....
          Exec_Master_Log_Pos: 121923824
						 .....
        Seconds_Behind_Master: 11178
						 .....
      Slave_SQL_Running_State: Waiting for dependent transaction to commit

2、查看正在使用的表和正在执行的SQL语句

发现有一条alter语句执行了很长时间，正在对表创建索引。

mysql> show open tables where in_use>=1;
+----------+--------------------------+--------+-------------+
| Database | Table                    | In_use | Name_locked |
+----------+--------------------------+--------+-------------+
| oab      | comm_label_majd_info_tap |      1 |           0 |
+----------+--------------------------+--------+-------------+

mysql> show full processlist;
+----------+-------------+---------------------+------+---------+---------+---------------------------------------------+-----------------------------------------------------------------------------+
| Id       | User        | Host                | db   | Command | Time    | State                                       | Info                                                                        |
+----------+-------------+---------------------+------+---------+---------+---------------------------------------------+-----------------------------------------------------------------------------+
|  5112918 | root        | 127.0.0.1:42405     | NULL | Query   |       0 | starting                                    | show full processlist                                                       |
| 14404122 | system user |                     | NULL | Connect | 6750302 | Waiting for master to send event            | NULL                                                                        |
| 14404123 | system user |                     | NULL | Connect |    5226 | Waiting for dependent transaction to commit | NULL                                                                        |
| 14404124 | system user |                     | oab  | Connect |   11166 | altering table                              | alter table comm_label_majd_info_tap add index oneid_ind(oneid) using BTREE |
| 14404125 | system user |                     | NULL | Connect |    5280 | Waiting for an event from Coordinator       | NULL                                                                        |
| 14404126 | system user |                     | NULL | Connect |    9581 | Waiting for an event from Coordinator       | NULL                                                                        |
+----------+-------------+---------------------+------+---------+---------+---------------------------------------------+-----------------------------------------------------------------------------+

场景4：主实例的DDL语句执行时间较长

如果DDL操作在master上执行时间很长，那同步数据到slave后，同样也要消耗相同的时间来完成重放操作，导致延迟，比如create index、repair table、alter table add column等常见的DDL操作。

还有一种可能是，slave上正在执行的查询或未完成的事务，阻塞了从master上同步过来的DDL语句执行。

解决思路：

1、对于DDL直接引起的延迟，建议在业务低峰期执行这些DDL，如创建索引、删除索引等

2、对于来自master的DDL语句在slave上被阻塞的情况。执行show processlist命令，找到状态是waiting for table metadata lock的SQL语句（即MDL锁），然后kill掉，恢复slave与master之间的数据同步。

3、开启事务自动提交autocommit。

4、将lock_wait_timeout参数设置为较小值。

MDL锁（Metadata Lock）：

MDL锁用于解决DDL操作与DML操作的一致性。

容易导致出现阻塞的情形：当前对表执行了一些DML语句，比如update、insert、长时间select等，同时还执行了其他一些DDL操作，比如create index、drop index、alter table、表维护操作（optimize table，repair table），或其他一些删除表、获取表级写锁等操作。

执行 show processlist 命令，如果看到State列信息是 Waiting for table metadata lock的SQL语句，就说明出现阻塞现象。