MySQL Concurrent replication series three: MySQL and mariadb implementation comparison

http://blog.itpub.net/28218939/viewspace-1975856/

Concurrent Replication ( Parallel Replication ) Series three:MySQL 5.7 and MariaDB parallel replication Contrast

Ma Pengfei Technology MySQL database engineer

After two mysql/mariadb on the development of binary log Group commit and enhanced multi-threaded slave introduction, I believe you are based on the MySQL Binay log The principle of replication and the enhanced multi-threaded Slave feature introduced in order to solve the problem of primary and standby data replication delay, which supports a deeper understanding of transactions submitted from the Library multi-threaded concurrent playback main Library. At the same time, in order to better play the MySQL 5.7/mariadb 10 concurrent replication performance, two versions in the main library binary Log Group commit phase has been more in-depth optimization.

Both MySQL and mariadb in binary Log group commit optimizations are made to commit transactions under high concurrency as much as possible at the same point in time, and then use the fsync () operation once to set the Binary Log cached data is written to disk . When a concurrent transaction can be committed at the same time, stating 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 set of concurrent commits transactions can concurrently replay the transaction committed by the main library on the slave machine. So we just need to tag the group commit information on the binary log when the Master machine commits it, and the slave machine can safely execute the transaction committed by the main library concurrently.

Let's look at an example:

Transaction T1, T2 (start transaction) begins the transaction, lags behind the transaction T3, T4 (Start transaction) Start time, but this set of transactions commits the transaction at the C (commit) point in time, so this set of transactions (T1, T2, T3, T4) will perform a binary log group Commit on the master machine, and then the binary log can execute this set of marked transactions concurrently when pushed to the slave machine.

Principle:

As can be seen from the above example, concurrent threads perform different transactions as long as they can commit at the same time (stating that there is no lock conflict between threads), then the master node can mark this set of transactions and safely perform concurrent replay of the transaction committed by the main library on the slave machine. So as much as possible to enable all threads to commit at the same time can greatly increase the number of slave machines concurrently executing transactions to synchronize the primary and standby data.

Mentioned in the previous article: MYSQL/MARIADB open Binary log log to make the binary log write order is consistent with the order of the storage engine submission,Binary Log Group Commit consists of three processes:

Figure 1:binary Log Group Commit three stages

On Flush Stage: All registered threads are written to the binary log cache

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

The commit Stage:leader invokes the storage engine to commit the transaction sequentially.

So in order for more concurrent threading transactions to be considered at the same time commit at the Sync Stage (Fsync () permanently flush the binary log file system cache log into the disk file) the Master machine tag concurrently commits the transaction to write binary information for the same set of transactions Log logs. We can increase the number of binary Log Group commits by bringing more follower threads to sync stage to perform a fsync () operation on the flush stage, which will register the thread as leader.

Such as:

Currently running three threads on the MYSQL/MARIADB DB instance commits the T1, T2, T3 transactions, the thread of the T1 transaction first commits to enter the first stage flush stage queue, discovers that the queue is empty queue so registered as leader, while the T2 transaction enters flush Stage becomes the queue's follower waits for leader leveling, the leader of the transaction T1 leads the T2 transaction into sync stage for a fsync () operation then T1, T2 a group commit at binary log.
This set of transactions is marked within the binary log. The transaction of the T3 thread then enters the binary log commit process.

Figure 2: Group Submission Process

MariaDB 10 is set by the @ @binlog_commit_wait_count and @ @binlog_commit_wait_usec two parameters, at least when the transaction commit phase is Binlog_commit_wait_ USEC milliseconds to increase the number of transactions in each set of transactions by a group commit until there is a binlog_commit_wait_count number, and can be queried by the state variable @ @binlog_commit and @ @binlog_group_ Commit to check the parameters to see the current binary log group commit scale.

MySQL5.7 increases the number of binary log group submissions by introducing the Binlog_group_commit_sync_delay and Binlog_group_commit_sync_no_delay_count parameters. Both MySQL waits Binlog_group_commit_sync_delay milliseconds until the number of Binlog_group_commit_sync_no_delay_count to commit a group at a time.

Realize:

Binary Log group commit is enabled by default on MySQL 5.7 and mariadb 10 and does not require any information to be configured, and group submission information marked in binary log is dependent on Gtid. and MySQL and mariadb gtid composition and implementation of different ways, here we simply comb under.

In MySQL version 5.7 because binary log Group commit is turned on by default, even if you do not turn on Gtid_mode in the configuration file, binary log content also has Gtid information only the message labeled "ANONYMOUS"

> Show Binlog events in ' mysql-bin.000004 '; intercept 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= ' ANONYMOUS '/*!*/;

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 ...

MARIADB Gtid is also enabled by default and Gtid is comprised of domain ID, Server ID, and transaction Sequence number:

   Figure 3 MariaDB Gtid composition

> Show Binlog events in ' mysql-bin.000003 '; intercept 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. gtid_seq_no=64143*//*!*/;

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

#160104 15:16:46 server ID 15102 end_log_pos 2079 CRC32 0x02b5a694 write_rows:table ID: flags:stmt_end_f

11.

BINLOG '

13 .....

Conclusion:

MySQL 5.7/mariadb 10 Parallel replication is based on the binary Log Group Commit on the main library.

MySQL: The main library concurrent commit TRANSACTION group commit is written to the binary log log, when the transaction is marked with the value of the last_committed=n (by binlog_group_commit_sync_ Delay, binlog_group_commit_sync_no_delay_count parameter setting increases the number of concurrent transactions), you can replay transactions submitted by the main library concurrently on the slave node.

MariaDB: The main library concurrently commits the transaction group commit to the binary log log, when the transaction is marked with the value of the cid=n (through Binlog_commit_wait_count, Binlog_commit The _WAIT_USEC parameter setting increases the number of concurrent transactions, and the transactions submitted by the main library can be replayed concurrently on the slave node.

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

MySQL Concurrent replication series three: MySQL and mariadb implementation comparison