We are well aware that MySQL Group Replication is one of the faster evolving clustering Technology for MySQL. Flow Control plays a key factor in Group Replication performance and data integrity . In this blog I am going to explain about the Flow Control mechanism and How it has evolved in MySQL 8 ?

What is Flow Control ?

MySQL Group Replication / Native Async replication needs binary logs to get the data flow across the servers.

What makes the difference ?

In the MySQL Group Replication we are trying to achieve the Synchronous replication with the help of a Flow Control mechanism and transaction acknowledgments ( certification ).

Without Flow Control, the MySQL Group Replication is asynchronous replication ? Yes, consistency is lost.

Lets us consider

We have three nodes ( GR1, GR2, GR3 ) . Gr1 is the master and and other two servers ( GR2, GR3 ) are the appliers . Suddenly, your master node (GR1) is getting more write loads from application. But, your applier servers are not able to catch-up the concurrent writes ( may be, the applier servers having less configurations OR network bandwidth is low between the nodes ) from master servers . So, our applier servers will keep lagging . In this case , the master server slows down the writes for the slave (GR2 / GR3) appliers to be up to date with master node (GR1) . This mechanism is called Flow Control .

MySQL dev official document,

Group Replication ensures that a transaction only commits after a majority of the members in a group have received it and agreed on the relative order between all transactions that were sent concurrently. This approach works well if the total number of writes to the group does not exceed the write capacity of any member in the group. If it does and some of the members have less write throughput than others, particularly less than the writer members, those members can start lagging behind of the writers.

How Flow Control works ?

MySQL group replication flow control mainly depends on two factors .The controlling variables are

• GR certifier queue size ( group_replication_certifier_threshold )
• GR applier queue size ( group_replication_applier_threshold )

Group replication have inbuilt health checks , the collect the stats from the group members . The collected stats will be share to other group members periodically . The monitoring mechanism will calculate the below stats from the group members .

• certifier queue size
• replication applier queue size
• total number of transactions certified
• total number of remote transactions applied in the member
• total number of local transactions

Based on the metrics collected from all the servers in the group, a throttling mechanism kicks in and decides whether to limit the rate a member is able to execute/commit new transactions.

The group capacity will be calculated based on the lowest capacity of all the members in the group .

Group Replication MySQL 5.7 vs MySQL 8

We all know, the Group Replication is introduced at MySQL 5.7 . But, there is a lot of improvements in MySQL 8 ( Particularly MySQL 8.0.2 ) . Below I have mentioned the important key features, which introduced in MYSQL 8 ( MySQL 8.0.2 ) . Also, I am adding the complete list of variables ( both MySQL 5.7 & MySQL 8 ) which involving in the MySQL Flow control mechanism .

• Can allow the cluster to cach up the backlog under flow control based on the hold percent ( group_replication_flow_control_hold_percent )
• Can define, When the group quota should be released, when flow control is no longer needed to throttle the writer members ( group_replication_flow_control_release_percent )
• Can control the lowest/highest flow control quota that can be assigned to a member ( group_replication_flow_control_max_commit_quota, group_replication_flow_control_min_quota )

MySQL 5.7

• group_replication_flow_control_applier_threshold
• group_replication_flow_control_certifier_threshold
• group_replication_flow_control_mode

MySQL 8

• group_replication_flow_control_hold_percent
• group_replication_flow_control_max_commit_quota
• group_replication_flow_control_member_quota_percent
• group_replication_flow_control_min_quota
• group_replication_flow_control_min_recovery_quota
• group_replication_flow_control_period
• group_replication_flow_control_release_percent

Understanding the Flow Control mechanism and its related variables will be helpful for effective tuning of Group Replication cluster . Hope this blog will helps someone who is learning the MySQL flow control in GR . At Mydbops, We are keep testing the new things on MySQL and related tools, will be coming back with a new blog soon.

Optimize your MySQL Group Replication setup with Mydbops' InnoDB Cluster Consulting Services. Our experts can help you streamline your database management and improve performance. Contact us today to discuss your specific needs.

{{cta}}