Breaking Semisynchronous Replication in MySQL 5.5

Question

I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7.

My application is running and updating the database of the master server and same is being updated in the slave database server.

But due to some unknown reasons sometimes, Replication breaks.

On running the command:

SHOW STATUS LIKE 'Rpl_semi_sync%';

It gives this status:

'Rpl_semi_sync_master_no_times', '0'
'Rpl_semi_sync_master_no_tx', '0'
'Rpl_semi_sync_master_status', 'ON'     <<-------------
'Rpl_semi_sync_master_timefunc_failures', '0'
'Rpl_semi_sync_master_tx_avg_wait_time', '338846'
'Rpl_semi_sync_master_tx_wait_time', '29479685'
'Rpl_semi_sync_master_tx_waits', '87'
'Rpl_semi_sync_master_wait_pos_backtraverse', '0'
'Rpl_semi_sync_master_wait_sessions', '0'
'Rpl_semi_sync_master_yes_tx', '3106'

Ideally, in semi synchronization, when the sync breaks the status should come as OFF since master is not able to receive any acknowledgement from the slave. Please help us in this regard.

Answer 1

The one thing you can probably do is to increase the sensitivity of the acknowledgement

Please look for this variables in /etc/my.cnf

[mysqld]
rpl_semi_sync_master_timeout=5000

When rpl_semi_sync_master_timeout (Default is 10000 or 10 seconds) is set to 5000, Replication should switch a Master from SemiSync to Async if no acknowledgement is received from the Slave in 5 seconds (5000 milliseconds). You may want to lower this even less than 5000.

You may need to check your network performance. As long as you not doing geographic distance replication, you should the following: On a Separate NIC, use a crossover cable over 192.168.xx.xx so as not have faster replication response. Also, check the switch for dropped packets.

You should not have to restart the server every time. As a quick-and-dirty band-aid, make up this SQL script:

STOP SLAVE IO_THREAD;
SELECT SLEEP(5);
START SLAVE IO_THREAD;

Simply write a cronjob to execute these commands every 5 minutes. Again, this is a band-aid. Otherwise, due diligence on the networking side is in order.

Answer 2

Your question make have left some confusion about the nature of the problem you're seeing, but one of your comments provides a hint as to what's really happening.

We are currently explicitly restarting the server and set up the sync again from scratch

What I think you meant is, you're setting up replication from scratch...

In other words, it's not semi-synchronous replication that's breaking, it's all replication that's breaking. Like this, on the slave, right?

         Slave_IO_Running: Yes
        Slave_SQL_Running: No

If that is the case, it is quite correct for the master to still report Rpl_semi_sync_master_status = ON. Here's why:

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.

^{http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html (emphasis added~sqlbot)}

Semi-synchronous replication adds protection against transactions being lost by ensuring that they exist in the relay log on at least one semi-sync-capable slave server -- not that they have actually been executed by the slave SQL thread on that server.

If your SQL thread is stopping, this is a sign that you are not starting out with precisely-identical data sets on both machines, that you're misconfiguring replicate-*-db or replicate-*-table variables, or that you're have something poorly-designed, such as nondeterministic queries against a table without a primary key.

But the status of the SQL thread on the replica server does not matter with semi-synchronous replication, since it is only concerned with the responsiveness of the IO thread, which is the thread that receives the binlog from the master, writes the relay logs on the slave.