mysql之并行复制
#####################################
注意：开启并行复制后，如果想要Xtrabackup进行全量备份的话，那就必须还要开启gtid复制而不是传统的位点复制
问题描述：
随着业务的规模越来越大，数据库的读写压力也会越来越大，一般地，mysql的架构为一主多从，实现读写分离，读的压力往往可以通过添加从库实例来解决，然而写压力就只有主库独自承担，主库可以并行写入数据。
而从库在默认情况下，只能单线程重放主库的binlog，从库通过一个io线程去拉取主库的binlog并写到从库的relay log，然后再通过从库的一个sql线程将relay log重放一次，通常地主库和从库均部署在同一个机房，因此io线程不会是性能瓶颈，sql线程往往才是性能的瓶颈。
这个时候，主库写数据压力大，这就导致从库的sql线程来不及消化relay log，也就是主库写数据量很大，但是从库重放relay log的速度太慢，这就导致一个严重的问题，主从延迟越来越大，那么从从库读取的数据就很久之前的过期数据了。
如何解决：在线开启从库的并行复制，同时将开启双0：
- MySQL5.6的并行复制是基于数据库级别的，不同数据库的事务可以同时进行binlog重放。如果用户的MySQL数据库实例中存在多个schema，对于从机复制的速度的确可以有比较大的帮助；如果在MySQL 5.6版本开启并行复制功能，那么SQL线程就变为了coordinator线程，如果用户实例仅有一个库，那么就无法实现并行回放，甚至性能会比原来的单线程更差
- MySQL5.7中兼容了MySQL5.6的并行复制方案，用参数slave_parallel_type进行兼容，如果设置为database，则使用5.6版本的数据库级别的并行复制，如果设置为logical_clock，则是全新的并行复制方案。而slave_parallel_workers的值代表并行复制sql_thread的worker线程个数。一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。若将slave_parallel_workers设置为0，则MySQL 5.7退化为原单线程复制，但将slave_parallel_workers设置为1，则SQL线程功能转化为coordinator线程，但是只有1个worker线程进行回放，也是单线程复制。然而，这两种性能却又有一些的区别，因为多了一次coordinator线程的转发，因此slave_parallel_workers=1的性能反而比0还要差。MySQL5.7中，对于并行复制进行了大刀阔斧的改革，它的思路是所有处于redo log prepare阶段的事务，都可以并行提交，原因是这些事务都已经经过了锁资源争用的阶段，都是没有冲突的。这个结论我们可以进行反证，如果这些事务之间有冲突，则后来的事务会等待前面的事务释放锁之后才能执行，因此，这些事务就不会进入prepare阶段。MySQL5.7 版本的基于逻辑时钟的并行复制，是基于组提交的原理来实现的，首先在主库满足能组提交的事务，都是可以并行回放，因为这些事务都已进入到事务的 prepare 阶段，则说明事务之间没有任何冲突（否则就不可能提交）。但他没有完全模拟主库上的并发执行，所以在从库上的回放力度依然没有主库高，但比 MySQL5.6 鸡肋的基于 database 的并行复制强多了，这个时候已经能消灭大多数复制延迟了。MySQL5.7.22 开始官方推出了基于 writeset 的并行复制，他能把并行复制推到了全新的高度，怎么说呢？就是主库原本不是并行的 SQL，只要不冲突在从库上都能并行，极限情况下，从库回放速度甚至比主库还高！这基本上是消灭了这个原因下的复制延迟。
coordinator就是原来的sql_thread，不过现在它不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了worker线程。而worker线程的个数就是由参数slave_parallel_workers决定的 coordinator在分发的时候，需要满足以下两个基本要求：
- 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中
- 同一个事务不能被拆开，必须放到同一个worker中
其实只要能够达到redo log prepare阶段就表示事务已经通过锁冲突的检验了 因此，MySQL5.7并行复制策略的思想是：
- 同时处于prepare状态的事务，在备库执行时是可以并行的
- 处于prepare状态的事务，与处于commit状态的事务之间，在备库执行时也是可以并行的
MySQL5.7.22增加了一个新的并行复制策略，基于WRITESET的并行复制，新增了一个参数binlog-transaction-dependency-tracking用来控制是否启用这个新策略。这个参数的可选值有以下三种：
- COMMIT_ORDER，根据同时进入prepare和commit来判断是否可以并行的策略
- WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的hash值，组成集合writeset。如果两个事务没有操作相同的行，也就是说它们的writeset没有交集，就可以并行
- WRITESET_SESSION，是在WRITESET的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序
为了唯一标识，hash值是通过库名+表名+索引名+值计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert语句对应的writeset就要多增加一个hash值
- writeset是在主库生成后直接写入到binlog里面的，这样在备库执行的时候不需要解析binlog内容
- 不需要把整个事务的binlog都扫一遍才能决定分发到哪个worker，更省内存
- 由于备库的分发策略不依赖于binlog内容，索引binlog是statement格式也是可以的 对于表上没主键和外键约束的场景，WRITESET策略也是没法并行的，会暂时退化为单线程模型
并行复制相关参数配置：
skip_slave_start =0
relay_log =/home/work/mysql_3306/relaylog/relay-bin
relay_log_recovery =1
master_info_repository =table
relay_log_info_repository =table
slave_parallel_type =logical_clock
slave_parallel_workers =4
loose-rpl_semi_sync_master_enabled =1
loose-rpl_semi_sync_slave_enabled =1
loose-rpl_semi_sync_master_timeout =1000
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_group_commit_sync_delay =500
binlog_group_commit_sync_no_delay_count = 13
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
1) 开启双0，提高性能，降低安全性，从库实例较多，挂掉一个从库实例没啥影响
mysql> set global sync_binlog=0; mysql> set global innodb_flush_log_at_trx_commit=0;
2)查看当前并行复制配置：
mysql> show variables like 'slave_parallel_%'; +------------------------+---------------+ | Variable_name | Value | +------------------------+---------------+ | slave_parallel_type | database | | slave_parallel_workers | 0 | +------------------------+---------------+ 2 rows in set (0.00 sec) # slave-parallel-type 默认值为database，但是针对只有一个数据库的集群而言，配置为database就没意义了，建议配置为logical_clock # slave-parallel-workers 默认值为0，建议至少配置为4
3)关闭sql线程：
在线开启： mysql> stop slave sql_thread; Query OK, 0 rows affected (0.07 sec)
4)开启基于组提交的并行复制
mysql> set global slave_parallel_type='LOGICAL_CLOCK'; Query OK, 0 rows affected (0.00 sec)
5)配置从库worker线程数，不要配置为0和1，建议至少配置为4
mysql> set global slave_parallel_workers=16; Query OK, 0 rows affected (0.00 sec)
6)开启sql线程：
mysql> start slave sql_thread; Query OK, 0 rows affected (0.06 sec)
7)
root@10.10.10.10 ((none)) > show slave status\G *************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay log Master_Host: 10.10.10.11 Master_User: mysqlsync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.037977 Read_Master_Log_Pos: 766436384 Relay_Log_File: relay-bin.111125 Relay_Log_Pos: 766243914 Relay_Master_Log_File: mysql-bin.037977 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 766243709 Relay_Log_Space: 766436825 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 113123925 Master_UUID: 465c12f1-bbc2-11ea-9fcb-e4434be4b111 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.04 sec) Mon Aug 30 10:41:29 2021
优化选项： 启用table模式是因为如果在多线程模式下，会频繁更新master.info文件，消耗代价过高，并且此值也不是非常准确 master_info_repository=table 对应的表为mysql.slave_master_info relay_log_recovery=on relay_log_info_repository=table 对应的表为mysql.slave_relay_log_info
my.cnf对应的配置如下：
# slave slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON innodb_flush_log_at_trx_commit=0 sync_binlog=0
#####################################
igoodful@qq.com
