Traditional single-threaded replication instructions

As we all know, before version 5.6 of MySQL, there were two threads on the slave node of master/slave replication, namely I/O thread and SQL thread.

• The I/O thread responsible for receiving binary logs writes the Event to the Relay Log.
• The SQL thread reads the Relay Log and plays it back in the database.

The above approach occasionally causes latency. What are the possible scenarios for primary and secondary node latency?

• 1. Master database performs large transactions (e.g. large table structure change operations).
• 2. Master library changes in large quantities (such as mass insertion, update and deletion operations).
• 3. In ROW synchronization mode, no primary key is frequently updated in the large table of the primary database.
• 4. The database parameters are incorrectly configured, causing performance bottlenecks on secondary nodes. For example, the transaction log Settings on secondary nodes are too small, leading to frequent disk flushing.
• 5. The network environment is unstable, and the binlog reading from the I/O thread of the node is delayed or reconnected.
• 6. The hardware configurations of the primary node and secondary node are different. The hardware resource usage of the secondary node reaches the upper limit. (For example, SSDS on primary nodes and SAS disks on secondary nodes)

The reasons for the above delays can be broadly categorized.

• 1. Hardware problems (including disk I/O and network I/O)
• 2. Configuration problems.
• 3. Database design problems.
• 4. The master library changed in large quantities, and the SQL single-thread processing of the slave node was not timely enough.

conclusion

By analyzing the above reasons, it can be seen that in order to reduce master-slave delay, apart from improving hardware conditions, DBA needs to pay attention to database design and configuration, and finally, it needs to improve the concurrent processing capacity of slave nodes. It is a good method to change single-thread playback to multi-thread parallel playback. The key point is how to solve the data conflict and confirm the fault recovery location under the premise of multi-thread recovery.

MySQL5.6 library-level parallel replication

In cases where there are multiple databases in the instance, you can start multiple threads, one for each database. Multiple threads are started from the slave node in this mode. Threads fall into two types: Coordinator and WorkThread.

• Threads divide their work to execute logic

A Coordinator thread is responsible for determining whether a transaction can be executed in parallel. If a transaction can be executed in parallel, it is distributed to a WorkThread for execution. If a transaction cannot be executed, such as DDL or cross-library operations, it is executed by a Coordinator after all worker threads are completed.

• Key Configuration Information

slave-parallel-type=DATABASE
Copy the code

• Scheme deficiency

In this parallel replication mode, a high degree of parallelism can be achieved only when the instance has multiple DB and the TRANSACTIONS of DB are relatively busy. However, in daily maintenance, the transactions of a single instance are relatively concentrated on one DB. By observing the delay, it can be found that the majority of the cases are based on the hotspot table. It is a good idea to provide table-based parallelism.

MySQL5.7 Group commit based parallel replication

Group Submission Instructions

In simple terms, under the double 1 setting, the operation of flush after transaction submission is changed to merge multiple transactions into a group of transactions and then flush disk in a unified manner. In this way, the disk IO pressure is reduced. Details refer to Lao ye teahouse instructions regarding group submitted tweets https://mp.weixin.qq.com/s/rcPkrutiLc93aTblEZ7sFg

When a group of transactions are committed at the same time, the value of last_COMMITTED and sequence_number can be used to determine whether a transaction is in the same group

• How do you determine if a transaction is in a group?

The value of last_COMMITTED and sequence_number is displayed in binlog. The last_COMMITTED and sequence_number parameters are duplicated.

• sequence_number# This value refers to the number of transactions committed, monotonically increasing.
• last_committed# This value has two meanings, 1. The same value means that the transactions are in the same group, 2. This value is also the maximum number representing the previous set of transactions.

[root@mgr2 GreatSQL]# mysqlbinlog mysql-bin.0000002 | grep last_committed
GTID last_committed=0 sequence_number=1
GTID last_committed=0 sequence_number=2
GTID last_committed=2 sequence_number=3
GTID last_committed=2 sequence_number=4
GTID last_committed=2 sequence_number=5
GTID last_committed=2 sequence_number=6
GTID last_committed=6 sequence_number=7
GTID last_committed=6 sequence_number=8
Copy the code

• Database Configuration

slave-parallel-type=LOGICAL_CLOCK
Copy the code

• Scheme deficiency

The disadvantage of logical_clock-based synchronization is that when the transaction busyness of the master node is low, the number of fsync flusher transactions submitted by the group within a period of time is low, and the parallelism of playback from the library is not high, and even there may be only one transaction in a group, so that the multithreading of the slave node is basically useless. You can set the following two parameters to allow the master node to delay the commit.

• Binlog_group_commit_sync_delay # Specifies the time to wait for a delayed commit_commit_SYNc_delay. Make each group have more transactions, artificially increasing parallelism.

• Binlog_group_commit_sync_no_delay_count # Maximum number of transactions to be committed. If the wait time is not up but the number of transactions is reached, fsync immediately. Commit immediately after the desired degree of parallelism is achieved, minimizing the wait delay.

MySQL8.0 writeset-based parallel replication

Writeset Determines whether a transaction can be played back in parallel based on transaction result conflicts. It is controlled by the binlog-transaction-dependency-tracking parameter. Writeset is used by default.

Viewing Key Parameters

Parameter Configuration Item Description

• COMMIT_ORDERUse 5.7 Group Commit to determine transaction dependencies.
• WRITESETUse write collections to determine transaction dependencies.
• WRITESET_SESSION# Use write set, but transactions in the same session do not have the same last_committed.

Writeset is an array of HASH type, which records transaction update information. Transaction_write_set_extraction is used to determine whether there is a conflict between the current transaction update record and the historical transaction update record, and then the corresponding processing method is adopted. The maximum stored value for writeset is controlled by binlog-transaction-dependency-history-size.

Note that transaction commits are unordered when set to WRITESET or WRITESET_SESSION. Slave_preserve_commit_order =1 is mandatory.

• binlog_transaction_dependency_history_size

Sets the upper limit on the number of row hashes kept in memory to cache row information modified by previous transactions. Once this hash number is reached, the history is cleared.

• transaction_write_set_extraction

This mode supports three algorithms. By default, XXHASH64 is used. When writeset replication is configured on the secondary node, the configuration cannot be set to OFF. This parameter has been deprecated in MySQL 8.0.26 and will be removed later.

Database Configuration

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = 1
Copy the code

References:

• Ali Kernel Monthly Report:

http://mysql.taobao.org/monthly/2018/06/04/

• Official documents:

https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html

Enjoy GreatSQL 🙂

Article recommendation:

GreatSQL Quarterly Report (2021.12.26)

Mp.weixin.qq.com/s/FZ_zSBHfl…

Technology sharing | sysbench pressure measuring tool usage

Mp.weixin.qq.com/s/m16LwXWy9…

Failure analysis | Linux disk IO utilization rate is high, the analysis of the correct position

Mp.weixin.qq.com/s/7cu_36jfs…

Technology sharing | flashbacks in MySQL implementation and improvement

Mp.weixin.qq.com/s/6jepwEE0D…

Wanta #20, how does index push down perform data filtering

Mp.weixin.qq.com/s/pt6mr3Ge1…

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli Database, which focuses on improving the reliability and performance of MGR. It supports InnoDB parallel query feature and is a MySQL branch version suitable for financial applications.

Gitee:

Gitee.com/GreatSQL/Gr…

GitHub:

Github.com/GreatSQL/Gr…

Bilibili:

Space.bilibili.com/1363850082/…

Wechat &QQ Group:

You can search to add GreatSQL Community Assistant wechat friends, and send the verification message “Add group” to join the GreatSQL/MGR communication wechat group

QQ group: 533341697

Wechat assistant: Wanlidbc

This article is published by OpenWrite!