1) According to with I read, the Semisynchronous replication guarantee that at least 1 slave will have the transaction commited, is this mean that the semisynchronous replication becomes synchronous for at least 2 servers? (the master and 1 slave)?

2) Can I configure Semisynchronous replication for MySQL Server version: 5.0.83-community MySQL Community Edition (GPL)?

Thanks a lot for this post !!!
JFBU

Mark knows what he’s talking about – he was part of the MySQL team at Google that created semisynchronous replication.

“If semisynchronous replication is enabled on the master side and there is at least one semisynchronous slave, a thread that performs a transaction commit on the master blocks after the commit is done and waits until at least one semisynchronous slave acknowledges that it has received all events for the transaction, or until a timeout occurs. ”
..
“The slave acknowledges receipt of a transaction’s events only after the events have been written to its relay log and flushed to disk. ”

“Compared to asynchronous replication, semisynchronous replication provides improved data integrity. When a commit returns successfully, it is known that the data exists in at least two places” – (cf http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html)

Of course if timeout occurs (which is by default set to 10sec), replication goes back to asynchronous.

“Basically, what semi-synchronous replication does is ensuring that a transaction/event has been written to at least one slave’s relay log and flushed to disk before doing the commit on the master node.”

Which part of the following article is wrong according to you ?

http://www.dbi-services.com/index.php/blog/entry/mysql-data-high-availability-with-semi-synchronous-replication

Thanks for your feedback, best regards

Which part of the following article is wrong according to you ?

http://www.mysqlperformanceblog.com/2012/01/19/how-does-semisynchronous-mysql-replication-work/

Thanks for your feedback, best regards

Currently analyzing different existing solutions to secure our web architecture using 3 mysql production servers and read your article which I found very interesting.
Like you said, I would really be interested to know people who put semi sync replication in production on large mysql databases and so don’t hesitate to contact us to share your throughts and experience 🙂
For the moment after evaluating semi sync replication (lots of problems with combining it to master/master circular replication and haproxy load balancing queries in front of mysql servers) and others open source solutions (tungsten / galera), i’m still wondering if there is any which is reliable and allows to have data integrity, high availability and easy failover with MySQL ?

Would be happy to have your thoughts/comments about this !

There is another way to lose transactions — performance degradation. Some implementations of sync replication will also lose transactions by not allowing them to commit because the extra latency cannot be tolerated by the application or because optimistic concurrency control leads to too many aborts. This is much more likely for workloads with hotspots. I hope that most of these problems can be resolved by redesigning applications.

I believe with Asynchronous replication (ewen with Semi synchronous) you will get the problem of nodes getting out of sync and either slave is ahead or master has data while slave does not. This goes back to Two General’s problem I believe
http://en.wikipedia.org/wiki/Two_Generals%27_Problem

The solution for this is to maintain some cluster “state” where the node has to do a suicide if it encourages the problem while applying the changes the cluster has already agreed to.

This is a theory though which sets on 100% guaranties of data consistency. While it is possible in theory due to software bugs, people mistakes, complicated faults everything can fail and data loss can occur and we just should be working with probabilities and chances. For some case MySQL replication (even without Semi Sync) is just fine.

> The race in this case is that if there is a problem on the master the slave
> has the transactions.

This should not be possible. The transaction is prepared and written to binlog
before being sent to the slave. The prepare ensures that the commit will not
fail, and the prepare + write to binlog ensures that XA recovery will commit
the transaction later if master crashes before it can commit. So slave will
never get commits that don’t exist on the master.

Or does the Google patches break this?

http://www.dbi-services.com/index.php/blog/entry/mysql-data-high-availability-with-semi-synchronous-replication
http://xkcd.com/386/

These are not guarantees but they still made things better when the inevitable failures occurred as fewer transactions would be lost. I will distinguish between protect data (old transactions) and protect recent transactions. We already have great protection for old transactions (backup, etc). Semi-sync provides better protection for recent transactions.

I like the idea of Enhanced Semi-Sync but suspect that the implementation will make commit stalls even worse as it probably increases the duration of the time for which prepare_commit_mutex is held. This needs to be integrated with the changes for group commit so that we can do:
1) get many transactions into state PREPARED on the master
2) write the PREPARED transactions into the binlog
3) wait for one slave to ACK all of them
4) commit on the master

The race in this case is that if there is a problem on the master the slave has the transactions. I would promote the slave to the master in that case. There is also a race if the master blows up and then the slave is unable to apply the transactions. I think that is unlikely and am willing to perform manual intervention to recover in that case.

Now I just need to figure out if Kristian and MariaDB think this is interesting.

MySQL Replication is great and this is what made MySQL so successful in Web world. It is relatively simple low overhead on master and works with great distances and large networks. The problems come when people start to look to build truly highly available solutions with replication which also require consistency and no data loss.

Semi Synchronous replication in fact can be enough for many applications but it just needs to be well understood it does not guaranty no data loss by design.