Solved

MySQL Semi-Synchronous vs Synchronous replicaton when slave fails

Posted on 2016-07-19
2
61 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 50

Accepted Solution

by:
Steve Bink earned 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Warning Statements when you have a LIMIT clause. 6 41
Merging spreadsheets 8 60
Complex MySQL Query 2 33
SQL querys that gives me from one table into another. 2 25
This article describes some very basic things about SQL Server filegroups.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

837 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