Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL Semi-Synchronous vs Synchronous replicaton when slave fails

Posted on 2016-07-19
2
Medium Priority
?
92 Views
Last Modified: 2016-07-20
I understand that in MySQL Semi-Synchronous replication, the transaction is not seen as committed before  a receipt is received back from the slave to the master given a certain time out period. Thus if the connection times out to the slave, the master still replies to the client and then replicates to the slave later when it is online?

Is that the same scenario with full synchronous replication, or does the slave have to be online all the time in order for the master to acknowledge the transaction made by the client when the slave has committed the transaction?
0
Comment
Question by:itnifl
2 Comments
 
LVL 51

Accepted Solution

by:
Steve Bink earned 2000 total points
ID: 41719926
MySQL defaults to asynchronous replication.  That means a client orders a master to commit a transaction.  The master writes the transaction to the log, and responds with "OK, done!"  The client can now go on about its business.  The slaves may/may not receive that information in a timely manner, but their involvement is not up for consideration by the master.  It is possible, then, to have a failover to a slave with incomplete knowledge of past transaction.  This system is great for high availability systems, but not so much for guaranteed data integrity.

In synchronous replication, the slaves are given greater weight.  All active, synchronous slaves must report success on a transaction before the master returns to the client.  Different systems may have different definitions of what an "active" slave is, but the configured slaves must generally be available for every transaction.  This is the gold standard for data integrity, since transactions are guaranteed to be at all slaves 100% of the time.  However, it sucks for availability, since waiting for confirmations can result in delays.  How a system handles excessive delays is a very important question, and can vary widely.

MySQL's semi-synchronous replication is a lot like <winkwink>synchronous<winkwink> replication.  In this case, the master receives and resolves the commit, then replicates the transaction to all semi-sync'd slaves.  But, instead of waiting for all to confirm, it only waits for one.  Once it receives at least one confirmation, it returns to the client and calls it a day....except when it doesn't.  There is a timeout value for how long the master will wait for a confirmation.  If that timeout expires, the whole system drops back to asynchronous replication until the slaves catch up.  When all the slaves report being up-to-date, the system will switch itself back over to semi-sync.  

MySQL's strategy with semi-sync is to provide a "best of both worlds" kind of approach.  After all, data integrity is incredibly important in many applications.  But, sometimes you just need to get stuff done.  MySQL's approach is basically saying, "This server will try its best to be synchronous.  If that becomes a problem (and YOU tell me how long before it does), we'll switch over to high availability and prayer."
0
 
LVL 2

Author Closing Comment

by:itnifl
ID: 41720999
Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question