Semi synchronous replication
About MySQL Semi-Synchronous Replication
MySQL replication by default is asynchronous. The master writes events to its binary log but does not know whether or when a slave has retrieved and processed them. With asynchronous replication, if the master crashes, transactions that it has committed might not have been transmitted to any slave. Consequently, failover from master to slave in this case may result in failover to a server that is missing transactions relative to the master.
Semi synchronous replication can be used as an alternative to asynchronous replication:
• A slave indicates whether it is semi synchronous-capable when it connects to the master.
• If semi synchronous replication is enabled on the master side and there is at least one semi synchronous slave, a thread that performs a transaction commit on the master blocks after the commit is done and waits until at least one semi synchronous 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.
• If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semi synchronous slave catches up, the master returns to semi synchronous replication.
• Semi synchronous replication must be enabled on both the master and slave sides. If semi synchronous replication is disabled on the master, or enabled on the master but on no slaves, the master uses asynchronous replication.
Scenario:
Here we have one master (Master) and two slaves (Slave1 and Slave2). Semi sync replication plug-ins installed and configured on both slaves in semi synchronous replication mode.
Created a database 'sakila' and a table 'aa' under it with few rows of data
Install Plug-ins:
On Master
We need to first install the plug-in to enable Semi Synchronous Replication.
mysql> show plugins;
+-----------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-----------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-----------------------+----------+--------------------+---------+---------+
20 rows in set (0.00 sec)
Install semi sync plug-in
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
mysql> show plugins;
+-----------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+-----------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+-----------------------+----------+--------------------+--------------------+---------+
21 rows in set (0.00 sec)
On Slave - 1
On every Slave we need to install the Semi Synchronous Replication Plug-in
mysql> show plugins;
+-----------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-----------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-----------------------+----------+--------------------+---------+---------+
20 rows in set (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)
mysql> show plugins;
+-----------------------+----------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+-----------------------+----------+--------------------+-------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+-----------------------+----------+--------------------+-------------------+---------+
On Slave - 2
Same as mentioned in Slave - 1
Enable SS Replication
After we install the plug-in we need to enable it on both Master and Slave(s).
On Master
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5 rows in set (0.00 sec)
mysql> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| 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 | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
On Slave [both slaves]
On every slave we should install the Semi Synchronous plug-in. After the plug-in is installed we need to enable the SS replication on the slave.
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'rpl%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
mysql> show global status like 'rpl%';
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | ON |
| Rpl_status | AUTH_MASTER |
+----------------------------+-------------+
Testing of SS Replication
Let’s assume the slaves are already installed with Semi Synchronous Replication and already enabled. How we will insert the record on Master.
On Master
mysql> use sakila
Database changed
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
+------+------+
7 rows in set (0.00 sec)
Here you can notice that the record is inserted immediately in the Master and the SS replication is enabled between Master and Slaves and they are performing as expected.
mysql> insert into aa values(8,'test'),(9,'foo');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
+------+------+
9 rows in set (0.00 sec)
On Slave1
mysql> use sakila
Database changed
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
+------+------+
9 rows in set (0.00 sec)
On Slave2
mysql> use sakila
Database changed
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
+------+------+
9 rows in set (0.00 sec)
Semi-Synchronous Failure Testing
To test the failure of semi-synchronous we need to stop the Slave thread on at least one Slave. Here we will stop it on Slave 1
On Slave1
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
So, once slave is stopped its semi sync slave status also disabled automatically.
mysql> show global status like 'rpl%';
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | OFF |
| Rpl_status | AUTH_MASTER |
+----------------------------+-------------+
2 rows in set (0.00 sec)
On Master
Now we need to insert few rows on Master to test the replication behavior.
mysql> insert into aa values(10,'tedd'),(11,'food');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
We can see the rows are inserted to database immediately as the Slave – 2 is still in semi synchronous mode.
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
| 10 | ted |
| 11 | food |
+------+------+
11 rows in set (0.00 sec)
mysql> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 515 |
| Rpl_semi_sync_master_net_wait_time | 515 |
| Rpl_semi_sync_master_net_waits | 1 |
| 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 | 510 |
| Rpl_semi_sync_master_tx_wait_time | 510 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
At this point, the transaction has committed on the master side, and receipt of its events has been acknowledged(Rpl_semi_sync_master_yes_tx) by at least one slave. If we notice the above results, insert is completed without any delay on master. So it means master has received ack from one of the slaves (here the slave is slave2)
Now I am stopping slave threads on Slave2 also to simulate all slaves’ failure.
On Slave2
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show global status like 'rpl%';
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | OFF |
| Rpl_status | AUTH_MASTER |
+----------------------------+-------------+
2 rows in set (0.00 sec)
Now i have inserted few rows on Master to test the replication behavior
On Master
mysql> insert into aa values(12,'bar'),(13,'bar1');
Query OK, 2 rows affected (10.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
| 10 | ted |
| 11 | food |
| 12 | bar |
| 13 | bar1 |
+------+------+
13 rows in set (0.00 sec)
If we notice the above insert,it took 10 sec to complete. This is due to our all slaves are down and there is no ack received from any of the slaves.
So it waited until the timeout(rpl_semi_sync_master_timeout) and fall back to async mode automatically.
mysql> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5 rows in set (0.00 sec)
mysql> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1969 |
| Rpl_semi_sync_master_net_wait_time | 7878 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 400 |
| Rpl_semi_sync_master_tx_wait_time | 400 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
Now the subsequent transactions will not wait for acknowledgement as the semi sync master status(Rpl_semi_sync_master_status) is OFF.
The Master will resume the Rpl_semi_sync_master_status to ON if anyone its semi sync enabled slave reconnect (typically recover from failure).
Conclusion:
From this test, it is very obvious that we have to consider Performance Vs Redundancy before implementing semi synchronous replication as the master have to wait until it receives the acknowledgement from one of the semi sync enabled slave.
Considering there is no network and IO latency between master and slave, semi synchronous replication can be used to make sure at least one of the slaves is current with the master and it guarantees that the data exists on master and slave is consistent.
The url is as follows:
http://blogs.oracle.com/mysql/entry/mysql_55_whats_new_in_replication
About MySQL Semi-Synchronous Replication
MySQL replication by default is asynchronous. The master writes events to its binary log but does not know whether or when a slave has retrieved and processed them. With asynchronous replication, if the master crashes, transactions that it has committed might not have been transmitted to any slave. Consequently, failover from master to slave in this case may result in failover to a server that is missing transactions relative to the master.
Semi synchronous replication can be used as an alternative to asynchronous replication:
• A slave indicates whether it is semi synchronous-capable when it connects to the master.
• If semi synchronous replication is enabled on the master side and there is at least one semi synchronous slave, a thread that performs a transaction commit on the master blocks after the commit is done and waits until at least one semi synchronous 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.
• If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semi synchronous slave catches up, the master returns to semi synchronous replication.
• Semi synchronous replication must be enabled on both the master and slave sides. If semi synchronous replication is disabled on the master, or enabled on the master but on no slaves, the master uses asynchronous replication.
Scenario:
Here we have one master (Master) and two slaves (Slave1 and Slave2). Semi sync replication plug-ins installed and configured on both slaves in semi synchronous replication mode.
Created a database 'sakila' and a table 'aa' under it with few rows of data
Install Plug-ins:
On Master
We need to first install the plug-in to enable Semi Synchronous Replication.
mysql> show plugins;
+-----------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-----------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-----------------------+----------+--------------------+---------+---------+
20 rows in set (0.00 sec)
Install semi sync plug-in
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
mysql> show plugins;
+-----------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+-----------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+-----------------------+----------+--------------------+--------------------+---------+
21 rows in set (0.00 sec)
On Slave - 1
On every Slave we need to install the Semi Synchronous Replication Plug-in
mysql> show plugins;
+-----------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-----------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-----------------------+----------+--------------------+---------+---------+
20 rows in set (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)
mysql> show plugins;
+-----------------------+----------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+-----------------------+----------+--------------------+-------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+-----------------------+----------+--------------------+-------------------+---------+
On Slave - 2
Same as mentioned in Slave - 1
Enable SS Replication
After we install the plug-in we need to enable it on both Master and Slave(s).
On Master
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5 rows in set (0.00 sec)
mysql> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| 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 | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
On Slave [both slaves]
On every slave we should install the Semi Synchronous plug-in. After the plug-in is installed we need to enable the SS replication on the slave.
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'rpl%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
mysql> show global status like 'rpl%';
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | ON |
| Rpl_status | AUTH_MASTER |
+----------------------------+-------------+
Testing of SS Replication
Let’s assume the slaves are already installed with Semi Synchronous Replication and already enabled. How we will insert the record on Master.
On Master
mysql> use sakila
Database changed
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
+------+------+
7 rows in set (0.00 sec)
Here you can notice that the record is inserted immediately in the Master and the SS replication is enabled between Master and Slaves and they are performing as expected.
mysql> insert into aa values(8,'test'),(9,'foo');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
+------+------+
9 rows in set (0.00 sec)
On Slave1
mysql> use sakila
Database changed
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
+------+------+
9 rows in set (0.00 sec)
On Slave2
mysql> use sakila
Database changed
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
+------+------+
9 rows in set (0.00 sec)
Semi-Synchronous Failure Testing
To test the failure of semi-synchronous we need to stop the Slave thread on at least one Slave. Here we will stop it on Slave 1
On Slave1
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
So, once slave is stopped its semi sync slave status also disabled automatically.
mysql> show global status like 'rpl%';
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | OFF |
| Rpl_status | AUTH_MASTER |
+----------------------------+-------------+
2 rows in set (0.00 sec)
On Master
Now we need to insert few rows on Master to test the replication behavior.
mysql> insert into aa values(10,'tedd'),(11,'food');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
We can see the rows are inserted to database immediately as the Slave – 2 is still in semi synchronous mode.
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
| 10 | ted |
| 11 | food |
+------+------+
11 rows in set (0.00 sec)
mysql> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 515 |
| Rpl_semi_sync_master_net_wait_time | 515 |
| Rpl_semi_sync_master_net_waits | 1 |
| 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 | 510 |
| Rpl_semi_sync_master_tx_wait_time | 510 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
At this point, the transaction has committed on the master side, and receipt of its events has been acknowledged(Rpl_semi_sync_master_yes_tx) by at least one slave. If we notice the above results, insert is completed without any delay on master. So it means master has received ack from one of the slaves (here the slave is slave2)
Now I am stopping slave threads on Slave2 also to simulate all slaves’ failure.
On Slave2
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show global status like 'rpl%';
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | OFF |
| Rpl_status | AUTH_MASTER |
+----------------------------+-------------+
2 rows in set (0.00 sec)
Now i have inserted few rows on Master to test the replication behavior
On Master
mysql> insert into aa values(12,'bar'),(13,'bar1');
Query OK, 2 rows affected (10.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | asa |
| 2 | eter |
| 3 | dfr |
| 4 | eee |
| 5 | eee |
| 6 | deee |
| 7 | eefe |
| 8 | test |
| 9 | foo |
| 10 | ted |
| 11 | food |
| 12 | bar |
| 13 | bar1 |
+------+------+
13 rows in set (0.00 sec)
If we notice the above insert,it took 10 sec to complete. This is due to our all slaves are down and there is no ack received from any of the slaves.
So it waited until the timeout(rpl_semi_sync_master_timeout) and fall back to async mode automatically.
mysql> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5 rows in set (0.00 sec)
mysql> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1969 |
| Rpl_semi_sync_master_net_wait_time | 7878 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 400 |
| Rpl_semi_sync_master_tx_wait_time | 400 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
Now the subsequent transactions will not wait for acknowledgement as the semi sync master status(Rpl_semi_sync_master_status) is OFF.
The Master will resume the Rpl_semi_sync_master_status to ON if anyone its semi sync enabled slave reconnect (typically recover from failure).
Conclusion:
From this test, it is very obvious that we have to consider Performance Vs Redundancy before implementing semi synchronous replication as the master have to wait until it receives the acknowledgement from one of the semi sync enabled slave.
Considering there is no network and IO latency between master and slave, semi synchronous replication can be used to make sure at least one of the slaves is current with the master and it guarantees that the data exists on master and slave is consistent.
The url is as follows:
http://blogs.oracle.com/mysql/entry/mysql_55_whats_new_in_replication
No comments:
Post a Comment