MySQL Semi-Synchronous Replication. See the Magic. Try the Magic.


MySQL Replication is one of the most used and valued features of the MySQL Server.

Unlike some other products on the market, it's out-of-the-box, easy to configure, non-paid and smart features.

Most of our medium/large/super-large installation base are using replication to achieve "scale-out" scaling. Some will use it for backup purposes (not as HA though) and some will use it for geographic redundancy.
In this article I won't discuss replication as there are hundreds of posts out there on how and what replication can do for you.

Rather, I would like to discuss our new extension to the good old replication mechanism: Semi-Synchronous Replication.

Before I go on, I think I should define "Semi-Synchronous Replication". What is "Synchronous" and why it's only "Semi".

Asynchronous VS. Synchronous

  • Synchronous: Every write transaction (insert, update, delete) is replicated to the slaves immediately, and the transaction is not considered complete, until all slaves reply they have received the data (and maybe even flushed it to disk).
    If even one slave replies with failure, the transaction will rolled back from all the servers and the failure will be communicated to the application.
    Usually, the master is well aware of all its slaves.
    • Pros: Redundancy; we're sure the data safely resides in more than one server.
    • Cons: Performance; each transaction takes time till all slaves and the master acknowledge the transaction is replicated and complete.
  • Asynchronous: The write transactions will be written to the master disk and to some kind of replicating log (in MySQL it's the "binary log"), the application gets acknowledgement that the transaction is complete even though the slaves have not received the data yet.
    The slaves, on their own time, access the master and retrieve data they need to replicate, since the last time they accessed the master bin log.
    That means the master doesn't "know" about the slaves nor how far behind they are, or even if they are still replicating.
    • Pros: Performance; the transaction waits only for the master to flush and acknowledge.
    • Cons: Redundancy; we have no idea whether the data is on all the slaves, some of the slaves or maybe on none of the slaves.

Get the picture?

This is one of the basic trade-offs of "Performance VS. Redundancy". You cannot get both. Never.

That is, until now… Our development guys came up with a great idea combining both.

Async Replication you get via MySQL out-of-the-box replication.

Sync Replication you can get with DRBD and Linux-HA.

SemiSync you get if you install MySQL server 5.5 and later.

So what Semi stand for?

In SemiSync replication, the master holds the transaction the same way it does with Sync replication, but it waits only for one of the slaves to acknowledge.

The acknowledgement from the slave means, it has received the transaction and applied it to the relay log and flushed it to disk. It doesn't mean the data has actually gone into the database.

Just to make this statement clearer, it means the transaction was written to the relay log, and the relay log was flushed to disk.
There is still the need for the SQL thread to pick it up and apply it to the database. It's very unlikely not to complete this task, but that way we're getting a bit more performance.

As soon as the master receives the acknowledgement it will complete the transaction and reply to the application.

This article will show you how it works. As I try to keep my posts as hands-on as possible, this one will include lots of code blocks as well.

detail here

Popular posts from this blog

Dunia Aplikasi OpenSource

Mengenal Fungsi Dan Komponen Panel Listrik

Membangun Ruang Server (1)