Shulou(Shulou.com)06/01 Report--

Concurrent replication (Parallel Replication) Series III: parallel replication comparison between MySQL 5.7and MariaDB 10

Author: Ma Pengfei, MySQL database engineer of Walk Technology.

After the introduction of the development of MySQL/MariaDB 's Binary Log Group Commit and enhanced multi-threaded slave in the last two articles, I believe you have a better understanding of the principle of MySQL's Binay Log-based replication and the enhanced multi-threaded slave function introduced to solve the problem of replication delay between master and standby data, and support concurrent playback of transactions committed by the master library from the library. At the same time, in order to better play the performance of MySQL 5.7/MariaDB 10 concurrent replication, both versions have made more in-depth optimization in the stage of the main library Binary Log Group Commit.

The purpose of both MySQL and MariaDB optimization in Binary Log Group Commit is to make transactions with high concurrency commit at the same point in time as much as possible, and then write this set of Binary log cached data to disk with a fsync () operation. When a concurrent transaction can be committed at the same time, it means that there is no lock conflict between the transactions executed by each thread (if there is a lock conflict, the concurrent transaction cannot be committed at the same time), then it means that this group of concurrent committed transactions can concurrently replay the transactions committed by the main library on the slave machine, so we only need to mark the relevant information of the group commit when the master machine Group Commit the binary log. The slave machine can safely execute transactions committed by the main library concurrently.

Let's look at an example:

Transactions T1, T2 (start transaction) start transactions, lagging behind the (start transaction) start time of transactions T3 and T4, but this set of transactions all commit transactions at C (commit) point in time, so this set of transactions (T1, T2, T3, T4) will be Binary Log group Commit on the master machine, and then this set of marked transactions can be executed concurrently when the binary log is pushed to the slave machine.

Principle:

As can be seen from the above example, as long as concurrent threads are able to commit different transactions at the same time (indicating that there is no lock conflict between threads), then the master node can mark this set of transactions and safely replay transactions committed by the main library on the slave machine. Therefore, as much as possible to enable all threads to commit at the same time can greatly increase the number of transactions executed concurrently by slave machines so that the main and standby data are synchronized.

As mentioned in the previous article: MySQL/MariaDB enables Binary Log logs to keep the write order of binary logs consistent with the order submitted by the storage engine. Binary Log Group Commit is divided into three processes:

Figure 1: three stages of Binary Log Group Commit

In Flush stage: all registered threads will be written to the binary log cache

The data cached in Sync stage: binary log will be sync to disk, and the binary log cache of all queue transactions will be permanently written to disk when sync_binlog=1 occurs.

In Commit stage: leader, the transaction is committed by calling the storage engine sequentially.

Then in order to make more concurrent thread transactions can be regarded as commit at the same time, that is, in the Sync phase (call fsync () to permanently brush the binary log file system cache log into the disk file), the information marked by the master machine that the concurrent committed transaction is the same set of transactions is written into the binary log log. We can increase the number of Binary Log Group Commit by leading more follower threads to Sync stage to perform a fsync () operation on Flush Stage.

As shown below:

Three threads running on the current MySQL/MariaDB database instance commit T1, T2 and T3 transactions respectively. The thread of T1 transaction takes the lead in committing to the first stage Flush stage queue and finds that the queue is empty so it is registered as leader. At the same time, T2 transaction enters Flush stage and becomes the follower of the queue waiting for leader deployment. The leader of transaction T1 leads T2 transaction into Sync stage for an fsync () operation, then T1 and T2 perform a group commit in binary log.

This set of transactions is marked in the binary log. The transaction of the T3 thread then enters the binary log commit process.

Figure 2: group submission process

MariaDB 10 increases the number of transactions in each set of transactions by setting the two parameters @ @ binlog_commit_wait_count and @ @ binlog_commit_wait_usec, that is, wait at least binlog_commit_wait_usec milliseconds until there is a binlog_commit_wait_count in the transaction binary log group commit phase, and you can check the current binary log group commit ratio by querying the status variables @ @ binlog_commit and @ @ binlog_group_commit.

MySQL5.7 increases the concurrent number of binary log group commits by introducing binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count parameters, that is, MySQL waits for binlog_group_commit_sync_delay milliseconds until the number of binlog_group_commit_sync_no_delay_count.

Achieve:

Binary Log Group Commit is enabled by default in MySQL 5.7and MariaDB 10 without any configuration information, and the group submission information marked in binary log depends on GTID, while the GTID composition and implementation of MySQL and MariaDB are different. Let's briefly sort out here.

In MySQL version 5.7, since Binary Log Group Commit is enabled by default, even if you do not enable gtid_mode in the configuration file, there is also GTID information in the content of binary log, except that the marked message is "ANONYMOUS".

> show binlog events in 'mysql-bin.000004'; intercepts a piece of information

1..

| | mysql-bin.000004 | 3571 | Anonymous_Gtid | 15112 | 3636 | SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS' |

2. | mysql-bin.000004 | 3636 | Query | 15112 | 3712 | BEGIN |

3. | mysql-bin.000004 | 3712 | Rows_query | 15112 | 3763 | # INSERT INTO T1 () VALUES () |

4. | mysql-bin.000004 | 3763 | Table_map | 15112 | 3807 | table_id: 108 (db2.t1) |

5. | mysql-bin.000004 | 3807 | Write_rows | 15112 | 3847 | table_id: 108 flags: STMT_END_F |

6. | mysql-bin.000004 | 3847 | Xid | 15112 | 3878 | COMMIT / * xid=33 * / |

.

> mysqlbinlog-vvv mysql-bin.00004 | less

1. # 151231 14:34:03 server id 15112 end_log_pos 2408 CRC32 0x5586fe71 Anonymous_GTID last_committed=6 sequence_number=8

2. SET @ @ SESSION.GTID_NEXT= 'ANONYMOUSbeat beat /

3. # at 2408

4. # 151231 14:34:03 server id 15112 end_log_pos 2484 CRC32 0x748efb17 Query thread_id=11 exec_time=0 error_code=0

5. SET timestamp 1451543643

6. BEGIN

7.

GTID of MariaDB is also enabled by default and GTID is composed of Domain ID, Server ID and transaction Sequence Number:

Fig. 3 composition of MariaDB GTID

> show binlog events in 'mysql-bin.000003'; intercepts a piece of information

1..

| | mysql-bin.000003 | 335 | Gtid | 15102 | 377 | BEGIN GTID 0-15102-64139 | |

2. | mysql-bin.000003 | 377 | Table_map | 15102 | 434 | table_id: 18 (test.sbtest1) |

3. | mysql-bin.000003 | 434 | Write_rows_v1 | 15102 | 657 | table_id: 18 flags: STMT_END_F |

4. | mysql-bin.000003 | 657 | Xid | 15102 | 688 | COMMIT / * xid=16 * / |

5. | mysql-bin.000003 | 688 | Gtid | 15102 | 732 | BEGIN GTID 0-15102-64140 cid=20 |

6. | mysql-bin.000003 | 732 | Table_map | 15102 | 789 | table_id: 19 (test.sbtest6) |

7. | mysql-bin.000003 | 789 | Write_rows_v1 | 15102 | 1012 | table_id: 19 flags: STMT_END_F |

8. | mysql-bin.000003 | 1012 | Xid | 15102 | 1043 | COMMIT / * xid=20 * / |

9. | mysql-bin.000003 | 1043 | Gtid | 15102 | 1087 | BEGIN GTID 0-15102-64141 cid=20 |

10. | mysql-bin.000003 | 1087 | Table_map | 15102 | 1145 | table_id: 20 (test.sbtest12) |

11. | mysql-bin.000003 | 1145 | Write_rows_v1 | 15102 | 1368 | table_id: 20 flags: STMT_END_F |

12. | mysql-bin.000003 | 1368 | Xid | 15102 | 1399 | COMMIT / * xid=21 * / |

.

> mysqlbinlog-vvv mysql-bin.00003 | less

1..

2. # at 1754

3. # 160104 15:16:46 server id 15102 end_log_pos 1798 CRC32 0x26104c0b GTID 0-15102-64143 cid=20 trans

4. / *! 100001 SET @ @ session. Session. Gtidholders seqple accounts 64143 accounts.

5. BEGIN

6. / *!

7. # at 1798

8. # 160104 15:16:46 server id 15102 end_log_pos 1856 CRC32 0x2c994f5a Table_map: `test`.`sbtest12` mapped to number 20

9. # at 1856

10. # 160104 15:16:46 server id 15102 end_log_pos 2079 CRC32 0x02b5a694 Write_rows: table id 20 flags: STMT_END_F

11.

12. BINLOG'

13..

Conclusion:

The parallel replication of MySQL 5.7 / MariaDB 10 is based on Binary Log Group Commit on the main library.

MySQL: transactions submitted concurrently by the main library group commit are written to the binary log log. When the marked last_committed=N values of the transactions are the same (the number of concurrent transactions is increased by setting the binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count parameters), the transactions committed by the master library can be played back concurrently on the slave node.

MariaDB: transactions submitted concurrently by the main library group commit are written to the binary log log. When the marked cid=N values of the transactions are the same (the number of concurrent transactions is increased by setting the binlog_commit_wait_count and binlog_commit_wait_usec parameters), the transactions committed by the master library can be played back concurrently on the slave node.

Reference: http://geek.rohitkalhans.com/2013/09/enhancedMTS-deepdive.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.