Centos7 Mysql 集群--之三(主从复制延迟解决方案)

  1. 查看当前默认配置（MYSQL5.7）
[root@MYSQLB bin]# mysql -uroot -p123456

mysql> show global variables like 'slave_parallel_workers';   #默认是0，即单线程
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+


mysql> show global variables like '%slave_parallel_type%';#默认是多线程机制是一个线程处理一个库
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+


mysql> show full processlist;
+----+-------------+--------------------+------+-------------+---------+---------------------------------------------------------------+-----------------------+
| Id | User        | Host               | db   | Command     | Time    | State                                                         | Info                  |
+----+-------------+--------------------+------+-------------+---------+---------------------------------------------------------------+-----------------------+
|  9 | repl        | MYSQLA:61254       | NULL | Binlog Dump | 1229510 | Master has sent all binlog to slave; waiting for more updates | NULL                  |
| 10 | system user |                    | NULL | Connect     | 1229504 | Waiting for master to send event                              | NULL                  |
| 11 | system user |                    | NULL | Connect     |     315 | Slave has read all relay log; waiting for more updates        | NULL                  |
| 13 | root        | localhost          | NULL | Sleep       | 1222364 |                                                               | NULL                  |
| 14 | root        | MYSQLA:1652        | test | Sleep       |      15 |                                                               | NULL                  |
| 15 | root        | MYSQLA:1658        | test | Sleep       |      25 |                                                               | NULL                  |
| 16 | root        | MYSQLA:1676        | test | Sleep       |       5 |                                                               | NULL                  |
| 17 | root        | MYSQLB:27432       | test | Sleep       |      27 |                                                               | NULL                  |
| 18 | root        | MYSQLB:27434       | test | Sleep       |       7 |                                                               | NULL                  |
| 19 | root        | MYSQLB:27436       | test | Sleep       |      17 |                                                               | NULL                  |
| 22 | root        | 192.168.40.1:55485 | test | Sleep       |     287 |                                                               | NULL                  |
| 23 | root        | 192.168.40.1:55495 | test | Sleep       |     284 |                                                               | NULL                  |
| 24 | root        | localhost          | NULL | Query       |       0 | starting                                                      | show full processlist |
+----+-------------+--------------------+------+-------------+---------+---------------------------------------------------------------+-----------------------+
13 rows in set (0.00 sec)
  1. 配置从服务器上并行复制的参数（开启 Enhanced Multi-Threaded Slave），双主则两台都配置
[root@MYSQLA ~]# vim /etc/my.cnf
#MySQL 5.7开启Enhanced Multi-Threaded 在Slave的my.cnf配置：
# slave
#变量slave-parallel-type可以有两个值：DATABASE 默认值，基于库的并行复制方式；LOGICAL_CLOCK：基于组提交的并行复制方式
slave-parallel-type=LOGICAL_CLOCK
#提交的事务都是可以并行回放(配合binary log group commit);
slave-parallel-workers=16
#将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用  
#开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。这是因为并 行复制开启后对于元master.info这个文件的更新将会大幅提升
master_info_repository=TABLE
relay_log_info_repository=TABLE
#启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
relay_log_recovery=ON
relay_log_purge=ON
  1. 再次检查配置
[root@MYSQL ~]# service mysqld restart

root@localhost : (none)【10:53:41】 2 SQL->show global variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 16    |
+------------------------+-------+
1 row in set (0.00 sec)

root@localhost : (none)【10:53:46】 3 SQL->show global variables like '%slave_parallel_type%';
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
+---------------------+---------------+
1 row in set (0.01 sec)

root@localhost : (none)【10:54:02】 4 SQL->show full processlist;
+----+-------------+--------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
| Id | User        | Host         | db   | Command     | Time | State                                                         | Info                  |
+----+-------------+--------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
|  1 | system user |              | NULL | Connect     |   59 | Waiting for master to send event                              | NULL                  |
|  2 | system user |              | NULL | Connect     |   59 | Slave has read all relay log; waiting for more updates        | NULL                  |
|  3 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
|  4 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
|  5 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
|  6 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
|  7 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
|  9 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 10 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 11 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 12 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 13 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 14 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 15 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 16 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 17 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 18 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 19 | system user |              | NULL | Connect     |   59 | Waiting for an event from Coordinator                         | NULL                  |
| 20 | root        | MYSQLB:29734 | test | Sleep       |    4 |                                                               | NULL                  |
| 21 | root        | localhost    | NULL | Query       |    0 | starting                                                      | show full processlist |
| 22 | root        | MYSQLA:5604  | test | Sleep       |    2 |                                                               | NULL                  |
| 23 | repl        | MYSQLA:5678  | NULL | Binlog Dump |    5 | Master has sent all binlog to slave; waiting for more updates | NULL                  |
+----+-------------+--------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
22 rows in set (0.01 sec)

#查看
mysql> show slave status\G
  1. 说明
情况3：
主库的DDL(alter、drop、repair、create)导致只读节点延迟
   可能1：只读节点与主库的DDL同步是串行进行的，如果DDL操作在主库执行时间很长，那么同样在备库也会消耗同样的时间，比如在主库对一张500W的表添加一个字段耗费了10分钟，那么在只读节点上也同样会耗费10分钟，所以只读节点会延迟600S，其他常见操作比如：

mysql> alter table test add column nn varchar(10);
mysql> alter table test add index(jj);

   可能2：只读节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，读节点表被锁，直到查询结束为止，进而导致了只读节点的数据延迟。在只读节点上可以通过执行show processlist命令查看连接的状态处于: Waiting for table metadata lock

解决办法：
对于可能1，只能说执行操作之前对可能带来的影响要有考量，对于情况2，可以kill掉只读节点上的大查询进行，就可以恢复只读节点与主节点的数据同步

情况4：

主库执行大事务导致延迟
    主库执行了一条insert … select非常大的插入操作，该操作产生了近几百G的binlog文件传输到只读节点，进而导致了只读节点出现应用binlog延迟。

解决办法：
    将大事务拆分成为小事务进行排量提交，这样只读节点就可以迅速的完成事务的执行，不会造成数据的延迟。

情况5：

无主键的表进行DML操作导致延迟
mysql> update test set kk='fafa01';

由于表中没有主键，所以导致了每一个事务条目的更新都是全表扫描，如果表中很很多的数据，则备库执行该更新的事务条目的时候，就会出现很多的全表扫描更新；进一步说明就是，由于表中没有主键，在ROW模式下，每删一条数据都会做全表扫，也就是说一条delete，如果删了10条，会做10次全表扫，所以slave会一直卡住；

拓展：
      主键对于innodb来说，是非常重要的，每张表的设计的时候，都应该把主键默认的加上，不管你需不需要他，而且主键的设计最好选择自增型的主键，这里也可以略提一下自增主键的好处：

a.自增型主键以利于插入性能的提高；
b.自增型主键设计(int,bigint)可以降低二级索引的空间，提升二级索引的内存命中率；
c.自增型的主键可以减小page的碎片，提升空间和内存的使用
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容