mysql主从复制中，提高从库IO的几个问题

　　主库能影响从库IO的几个参数：

　　sync_binlog = 1000

　　binlog_group_commit_sync_delay = 3000

　　binlog_group_commit_sync_no_delay_count = 0

　　从库设置（8.0后为replica_开头，之前为 slave_ 开头）：

　　transaction_write_set_extraction  = XXHASH64

　　replica_parallel_type = LOGICAL_CLOCK

　　replica_parallel_workers = 8

　　replica_preserve_commit_order = ON

　　binlog_transaction_dependency_tracking = WRITESET 还是 commit_order?

　　binlog_transaction_dependency_history_size = 500000

　　关于 binlog_transaction_dependency_tracking

　　binlog_transaction_dependency_tracking 是设在主库，可以取commit_order,writeset,writeset_session，主要是控制binlog文件中last_committed的计算方式：

　　commit_order：即group commit，同在prepare阶段的事务，在binlog中last_committed数值一样，传到从库之后可以并行执行；

　　writeset：会对事务处理的行数据哈希出一个writeset值，放到一个哈希表里，如果两个事务先后提交，但是处理的行数据没有冲突，即wirteset不一样，就可以有同样的last_committed，在从库可以并行执行；

　　writeset_session：比writeset多了一个约束，同一个session的事务，在binlog里保留先后顺序，也就是last_committed按先后顺序递增。

　　slave_preserve_commit_order/replica_preserve_commit_order 是设在从库，控制从库并行reply时事务提交的顺序。

　　感觉对于writeset，会有问题。先后事务处理不同的行虽然是没有冲突，但是如果后面的事务是基于前面事务修改后的数据呢？必须要是row格式的binlog。

　　SYS_BINLOG ，

　　binlog_group_commit_sync_delay，

　　binlog_group_commit_sync_no_delay_count

　　三者的关系：大概意思是先达到 sync_binlog 的数量，再应用 binlog_group_commit_sync_delay 的延迟。

　　binlog的参数：

　　sync_binlog：

　　Scope：Global、Dynamic：Yes、Default Value (>= 5.7.7) 1、Default Value (<= 5.7.6) 0、Minimum Value 0、Maximum Value 4294967295

　　sync_binlog=0: Disables synchronization of the binary log to disk by the MySQL server.Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log.

　　sync_binlog=1: Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash,transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log.

　　sync_binlog=N, where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected. In the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been flushed to the binary log. This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data loss.

　　binlog_group_commit_sync_delay：

　　Scope：Global、Dynamic：Yes、Default Value 0、Minimum Value 0、Maximum Value 1000000

　　Controls how many microseconds the binary log commit waits before synchronizing the binary log file to disk. By default binlog_group_commit_sync_delay is set to 0, meaning that there is no delay. 

　　Setting binlog_group_commit_sync_delay to a microsecond delay enables more transactions to be synchronized together to disk at once, reducing the overall time to commit a group of transactions because the larger groups require fewer time units per group.

　　When sync_binlog=0 or sync_binlog=1 is set, the delay specified by binlog_group_commit_sync_delay is applied for every binary log commit group before synchronization (or in the case of sync_binlog=0, before proceeding). 

　　When sync_binlog is set to a value n greater than 1, the delay is applied after every n binary log commit groups.

　　binlog_group_commit_sync_no_delay_count：

　　Scope：Global、Dynamic：Yes、Default Value 0、Minimum Value 0、Maximum Value 1000000

　　The maximum number of transactions to wait for before aborting the current delay as specified by binlog_group_commit_sync_delay

　　4.3 WRITESET 方案的相关参数

　　下面看看 WRITESET 方案的三个参数。

　　binlog_transaction_dependency_tracking

　　指定基于何种方案决定事务的依赖关系。对于同一个事务，不同的方案可生成不同的 last_committed。

　　该参数有以下取值：

　　COMMIT_ORDER：基于 Lock-Based 方案决定事务的依赖关系。默认值。

　　WRITESET：基于 WRITESET 方案决定事务的依赖关系。

　　WRITESET_SESSION：同 WRITESET 类似，只不过同一个会话中的事务不能并行执行。

　　transaction_write_set_extraction

　　指定事务写集合的哈希算法，可设置的值有：OFF，MURMUR32，XXHASH64（默认值）。

　　对于 Group Replication，该参数必须设置为 XXHASH64。

　　注意，若要将 binlog_transaction_dependency_tracking 设置为 WRITESET 或 WRITESET_SESSION，则该参数不能设置为 OFF。

　　binlog_transaction_dependency_history_size

　　m_writeset_history 的上限，默认 25000。

　　一般来说，binlog_transaction_dependency_history_size 越大，m_writeset_history 能存储的行的信息就越多。在不出现行冲突的情况下，m_writeset_history_start 也会越小。相应地，新事务的 last_committed 也会越小，在从库重放的并发度也会越高。

　　————————————————

　　参考文章：

　　https://www.modb.pro/db/42147

　　https://codelife.xin/article/41.html

　　https://blog.csdn.net/u014710633/article/details/90641489

　　https://zhuanlan.zhihu.com/p/611745341