Guide to MySQL group_replication_consistency

This article explores the vital role of the group_replication_consistency parameter in configuring and managing MySQL InnoDB Clusters. We will examine how this system variable controls transaction consistency across database nodes, detail the different consistency levels available, and explain how to choose the correct setting to balance data integrity and cluster performance.

What is group_replication_consistency?

In a MySQL Group Replication cluster, multiple database servers (nodes) work together to provide high availability. By default, group replication operates with an “eventual consistency” model for read operations. This means that when data is written to the primary node, there can be a small delay before that write is applied to the secondary nodes. During this window, a client reading from a secondary node might see stale data.

The group_replication_consistency parameter allows database administrators to control this behavior. It defines the synchronization guarantees for read and write transactions across the cluster, allowing you to prevent stale reads and ensure strict data consistency where required.

The Five Consistency Levels

MySQL provides five distinct values for the group_replication_consistency parameter. Each level offers a different balance between performance and data guarantees.

1. EVENTUAL

This is the default setting. Transactions do not wait for preceding transactions to be applied on other nodes before executing. * Behavior: Reads can be served from any node immediately, even if that node has not yet applied the latest updates. * Pros: Offers the lowest latency and highest throughput. * Cons: Allows the possibility of stale reads on secondary nodes.

2. BEFORE_ON_PRIMARY_FAILOVER

This level is specifically designed to prevent stale reads on a new primary node during a failover event in a single-primary cluster. * Behavior: When a new primary is elected, incoming transactions are buffered on the new primary until it has finished applying any backlog of transactions from the old primary. * Pros: Ensures clients do not read outdated data or experience write-skew immediately after a failover. * Cons: Temporarily increases latency for new transactions during the brief failover window.

3. BEFORE

With this setting, a transaction waits until all preceding transactions in the replication queue are applied before it begins executing on a node. * Behavior: If you query a node, the query pauses until that node is fully caught up with all changes committed across the cluster up to that point. * Pros: Guarantees that read operations always return the most up-to-date data (prevents stale reads). * Cons: Increases read latency, as read transactions must wait for the node’s replication queue to clear.

4. AFTER

This level ensures that once a transaction commits on the originating node, the change is guaranteed to be applied on all other cluster nodes before the client receives a success acknowledgment. * Behavior: The writing transaction blocks until all other nodes have fully written and committed the changes. * Pros: Guarantees that any subsequent read on any node in the cluster will immediately see the updated data. * Cons: Significantly increases write latency, as every write must wait for network roundtrips and execution on all nodes.

5. BEFORE_AND_AFTER

This is the most stringent consistency level, combining the guarantees of both BEFORE and AFTER. * Behavior: A transaction waits for all preceding transactions to be applied before it begins, and then blocks until its own changes are applied to all other nodes before committing. * Pros: Offers total consistency and eliminates any possibility of stale reads or write conflicts across the entire cluster. * Cons: Introduces the highest latency overhead for both reads and writes.

Choosing the Right Consistency Level

Selecting the correct value for group_replication_consistency depends entirely on your application’s requirements:

• Use EVENTUAL for applications where read-after-write consistency is not critical, such as logging systems, analytics, or content management systems where a delay of a few milliseconds in updating a view is acceptable.
• Use BEFORE when you have a read-intensive application that absolutely requires up-to-date reads (e.g., checking a user’s current account balance before permitting a transaction).
• Use AFTER when you have a write-intensive workload where you must guarantee that once a write is acknowledged, any subsequent read on any server will instantly see the change.
• Use BEFORE_ON_PRIMARY_FAILOVER as a safe baseline for single-primary clusters to ensure consistency during unexpected hardware transitions without penalizing day-to-day performance.

The parameter can be set globally to protect the entire cluster, or configured at the session level. Session-level configuration is highly recommended, as it allows you to enforce strict consistency (like BEFORE or AFTER) only on specific, critical transactions while keeping the rest of the cluster running at optimal speed under the default EVENTUAL setting.