Archive for December 2009

The new 5.5 is now available with “Semisynchronous Replication”. This comes as an addition to the built-in asynchronous replication. MySQL replication is asynchronous by default. Events written to the binary logs on the Master server being retrieved by the slave server(s). Unfortunately, the Master server has no knowledge of when the slave has retrieved or processed these events. As a result, when Master crashes, transactions committed on Master, might have not have been committed on the slave server(s). In other words, there is no guarantee that any event will ever reach any slave.

In case of “Semisynchronous Replication”, the Master server blockes the transaction commit until at least one of the slave servers acknowledges the it has received all the events for that transaction. Obviously, this is great for consistency, however it brings up questions about replication over the WWW which isn’t an uncommon practice. In case of hitting the timeout limit (in case of no response from any of the slave servers), the Master server reverts back to asynchronous replication.

From MySQL support:

To understand what the “semi” in “semisynchronous replication” means, compare it with asynchronous and fully synchronous replication:

* 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.