Hi Team,
We are currently utilizing Percona MySQL 8.0.34-26 for our MySQL group replication setup, with a configuration of 3 nodes(single_primary_mode) - one primary and two replicas. We have some concerns and uncertainties regarding this MySQL group replication. Could you assist us with the following queries?
1> In a setup with a single primary and 2 replica MySQL GR configuration, with group_replication_consistency set to EVENTUAL (default value), is there a risk of data loss during a primary failover scenario? Our understanding is that even in the event of a primary failover, the data should have been transmitted to both replicas and queued for certification and application, ensuring that data loss is prevented.
Is this interpretation accurate, or are there potential scenarios where data loss could still occur?
2> Regarding the MySQL GR variables group_replication_flow_control_applier_threshold and group_replication_flow_control_certifier_threshold (default value 25K), could you provide further insights into whether these thresholds apply to the number of transactions(both Read & Write) or the number of rows?
3> In our benchmarking experiments, where we varied the values of the certifier and applier thresholds, we noticed that only one of the replicas is experiencing lag (ranging from 10+ seconds to 120+ seconds), despite identical variables and hardware configurations. Is there a specific reason why only one replica is lagging behind? Could this be related to quorum?
Kindly elaborate on the potential reasons for only one of the replicas lagging.
4> In a MySQL Group Replication cluster, is it possible to identify the nodes that are part of the quorum? Are there any metrics in PMM or any details in the performance schema that can provide additional information about the quorum in a Group Replication cluster?
Please inform us if you require any additional information.
You won’t have data loss, but you may run into data inconsistencies. Consider a scenario where you commit UPDATE SET A=4 WHERE A=3 on node1. Then node1 fails and node2 becomes primary. If you SELECT WHERE A=4 on node2, this might return 0 rows. Then you wait 5s or so, and then the UPDATE processes and now A=4 is good.
This is why we recommend BEFORE_ON_PRIMARY_FAILOVER. When node1 fails, node2 will first process through all its backlog before allowing writes. This forces node2 to a consistency state just like node1 before it failed.
From the manual: group_replication_flow_control_applier_threshold specifies the number of waiting transactions in the applier queue that trigger flow control.
Flow control is calculated locally on each node, and applies locally. You could have different values on different nodes. Changing the values on node1 does not affect node2’s behavior.
You will need to configure PMM and collect some low-level data to understand why one node is lagging.
Quorum is a simple majority in GR. It is fluid and can change from trx to trx. Servers are not “members of quorum”. Everyone participates in the group; but qurorum is just a majority of responders. might be 1/2/3 for trx1, and then change to 2/5/4 for trx2.
Thanks @matthewb for your clarifications.
Regarding Question 3, ideally, there should not be a situation where a single member is consistently lagging behind. In an optimal setup, since the FC operates at a node level, either node could experience lag due to various reasons. There shouldn’t be a scenario where one node is consistently chosen to lag behind the other, correct?
Regarding Question 4, as far as I know, quorum typically denotes the minimum number of operational or available members(OR majority) within a group necessary for its proper functioning. However, based on your explanation, it seems that it operates not at a node or server level but rather at a transaction level, is that correct?
ideally, there should not be a situation where a single member is consistently lagging behind.
Correct, which means that if you do have 1 server consistently lagging, you need to look at every metric/statistic you can at the lowest level to understand why it is.
as far as I know, quorum typically denotes the minimum number of operational or available members
Well, it seems you can have quorum, and consensus. In reading some docs, quorum does seem to be at the server-member level. But there’s also consensus/majority by transaction:
https://dev.mysql.com/doc/refman/8.0/en/group-replication-flow-control.html
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.
See if this addition to sys schema helps with your membership view.
addition_to_sys_GR.sql · GitHub
Ack, thanks for the details.