- 25 Jan, 2012
- Majid Fatemian
When it comes to choose your MySQL replication setup you have the choice between Asynchronous replication or Semi-Synchronous replication. At the time of writing there is no fully-synchronous solution for MySQL replications.
The way these two differ is interesting and would be very useful when you are choosing your architecture.
In MySQL Manual for semi-synchronous replication it is said very well :
With asynchronous replication, the master writes events to its binary log and slaves request them when they are ready. There is no guarantee that any event will ever reach any slave.
With fully synchronous replication, when a master commits a transaction, all slaves also will have committed the transaction before the master returns to the session that performed the transaction. The drawback of this is that there might be a lot of delay to complete a transaction.
Semisynchronous replication falls between asynchronous and fully synchronous replication. The master waits after commit only until at least one slave has received and logged the events. It does not wait for all slaves to acknowledge receipt, and it requires only receipt, not that the events have been fully executed and committed on the slave side.
So as you can see the ideal situation in terms of data consistency and no data-loss would be the fully-synchronous solution. Which on the other hand may result in a lot of delay in the performance of the system and will make the responses slower, as you are dealing with (at least) two nested levels of transactions. (Although fully-synchronous is not available)
On a Asynchronous solution, Master writes the events in the binary log and it may happen that no Slave would pick it up after Master has crashed or any other reason.
Semi-Synchronous seems to be good and practical solution for many cases where High Availability and No Data-loss is important, but you should consider that semi-synchronous “does not provide strong guarantees against data loss“. article
You may ask why ? It is as simple as this, imagine Master has sent out the event and one slave has received it, then Master will commit. But on the other hand the slave could have possibly crashed or timed-out or an error happens. In this way you have received the commit on the client, while in reality data has not been committed on the Slave. article
Just wanted to point out the differences and ask you to be careful when you are choosing you solution for replication. Semi-Synchronous Does NOT guarantee no data-loss.