A brief analysis of MySQL parallel replication

01 The concept of parallel replication

In the master-slave replication architecture of MySQL, many SQL statements are often executed concurrently on the master database. As long as these SQL statements do not generate lock waits, there is no problem with running several SQL threads concurrently at the same time.

We know that the MySQL slave database uses IO_thread to pull the binlog on the master database, then stores it locally and writes it to the disk as relay-log, and applies these relay-logs through sql_thread.

In versions prior to MySQL 5.6, when multiple threads concurrently executed SQL on the master database, there was only one sql_thread. In some scenarios with high TPS, the master database would be severely delayed. To solve this problem, MySQL evolved sql_thread into multiple workers, and applied the transactions in the relay log in parallel on the slave side, thereby increasing the application speed of the relay log and reducing replication delay. This is where parallel replication comes in.

In MySQL, replication threads are controlled by the parameter slave_parallel_workers. Usually, on a machine with 8G memory and 8-core CPU, it is appropriate to set this value to 8. If your CPU has a high number of cores, you can adjust it to a number between 8 and 16.

mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 8 |
+------------------------+-------+
1 row in set, 1 warning (0.00 sec)

02 Evolution of Parallel Replication

The essence of parallel replication is that there is no lock contention for SQL statements executed simultaneously.

In MySQL version 5.6, the granularity supported by MySQL is to execute relay logs in parallel according to databases. This method can solve some problems because SQL on different databases will definitely not modify the content of the same row in the table. This way there will be no lock contention. This parallel replication method is better in scenarios where some databases are evenly distributed and each database is used with similar frequency. If the data of your business is concentrated in a hot table, in this case, parallel replication will degenerate into single-threaded replication.

Subsequently, some improvements were made to parallel replication in MariaDB. Its approach is:

1. Transactions that can be submitted in parallel on the master database, that is, transactions that have entered the redo log commit stage, can also be submitted in parallel on the slave database. Therefore, transactions submitted in parallel on the master database are identified by a commit_id. The commit_id of the next group of parallel transactions is the commit_id+1 of this group.

2. Write the commit_id of all transactions into binlog

3. When applying binlog from the database, divide all binlogs into different workers according to commit_id

4. After all transactions of commit_id in this group are committed on the slave database, the next batch of transactions will be taken.

This method greatly increases the speed of applying relay logs from the slave, but the problem is that while the slave is applying the previous set of transactions, the next set of transactions is waiting, even if some of the workers in the previous set are idle. On the master database, data may be written all the time. As a result, the master and slave nodes do not match in terms of system throughput, and the throughput of the master database is much higher than that of the slave database.

The parallel replication of MySQL 5.7 is improved on the basis of MariaDB. We know that when the transaction enters the redo log prepare stage, due to the WAL technology, it means that the transaction has passed the conflict detection stage. In parallel replication of MySQL 5.7, all transactions in the redo log prepare phase on the master database and transactions after this phase, that is, transactions in the redo log commit phase, are executed in parallel on the slave database, thereby reducing unnecessary waiting of worker threads.

Here, it is necessary to talk about two more parameters.

• The binnlog_group_commit_sync_delay parameter indicates how many microseconds it will take to call fsync after the redo log prepare phase is completed;
• The binlog_group_commit_sync_no_delay_count parameter indicates how many times redo log prepare:write operations are accumulated before calling fsync.

These two parameters are used to intentionally prolong the time from binlog write to fsync, thereby reducing the number of binlog writes. In the parallel replication strategy of MySQL 5.7, they can be used to create more "transactions in the prepare stage at the same time". This increases the parallelism of standby database replication.

They can "intentionally" make the primary database commit more slowly and make the standby database execute faster. When dealing with standby database delays in MySQL 5.7, you can consider adjusting these two parameter values ​​to improve the concurrency of standby database replication.

The above is a brief analysis of the details of MySQL parallel replication. For more information about MySQL parallel replication, please pay attention to other related articles on 123WORDPRESS.COM!