MySQL Forums
Forum List  »  InnoDB clusters

Preferred Consistency setting for Single Primary Mode
Posted by: GP Gp
Date: April 23, 2024 04:47AM

Hi

I would like to understand the tradeoffs for using 'BEFORE_ON_PRIMARY_FAILOVER' vs 'BEFORE' consistency level in Single primary mode for innodb cluster.

I want the client applications to always have consistent data.
So if the applications only connect on primary, this is solved by using 'BEFORE_ON_PRIMARY_FAILOVER' consistency level
But I have some use cases where applications only need to connect to read-only port. These are heavy read use cases. Before the cluster setup, these applications used to work on a slave. Now, I want to use secondary nodes to serve these use-cases.

To solve this applications can connect to secondary node through mysql-router. This can lead to connection to any of the secondary nodes. The problem occurs if one application connects to secondary1 at one point and to secondary2 at another point and let's say that secondary2 is lagging behind. This would result in consistency issue since now my application is seeing older data than when it was connected to secondary1.

For this we need to put in 'BEFORE' consistency level which solves my problem. But it can either be set on the session level or it can be set at the cluster Setting on application level which requires application changes which we want to avoid.

So my main question is - What are the consequences of setting the consistency level as 'BEFORE' for single primary mode.
Does is lead to degraded performance of the cluster compared to using 'BEFORE_ON_PRIMARY_FAILOVER'?

Options: ReplyQuote


Subject
Views
Written By
Posted
Preferred Consistency setting for Single Primary Mode
225
April 23, 2024 04:47AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.