- 查看当前默认配置(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)
- 配置从服务器上并行复制的参数(开启 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
- 再次检查配置
[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
- 说明
情况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的碎片,提升空间和内存的使用