Blog - comments

Hi Christopher, It's there. I't not an option that you check at install. Just use it by setting inme...
Hello, Thanks for the nice blog. I tried the latest 12c download available in Oracle's website and I...
Christopher Bernard
-- Here is a quick script to display which objects are locked in Share. Parameters: owner tablename....
Hey...I think you forgot that Hotspot have a JIT compiler too. The difference is in the time wherer ...
Anderson

Thanks for the content..

vani
Blog Gregory Steulet MySQL Data High Availability with semi-synchronous replication

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

MySQL Data High Availability with semi-synchronous replication

Starting with MySQL 5.5 it is possible to configure semi-synchronous replication. As you probably know, MySQL replication is asynchronous by default. Asynchronous replication means that events/transactions applied on one server are not immediately applied on the other one.

In the specific context of MySQL such behavior means that the MySQL master writes transactions/events to the local binary logs. However, it has no idea when the slave(s) will get and process them. Of course, in case of a failover, such configuration can lead to some transaction loss since there is no guaranty that the transactions have been transmitted to the standby server(s).

If you want to avoid such a problem, semi-synchronous replication can help you. 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 (Investigation pending). However, it doesn't mean that the transaction has been applied on the slave side, the application can be slightly delayed or may in the worst case fail. Of course, such semi-synchronous replication might impact performances. But this impact can be reduced by improving network performances.

The requirements in order to be able to configure semi-synchronous replication is to have a working replication and, as stated in the introduction, to have a MySQL version equal or higher than 5.5 installed. Before implementing semi-synchronous replication, two plugins have to be installed: One plugin on the slave server (semsiync_slave.so) and one plugin on the master (semisync_master.so). In case of a Master/Master Active/Slave setup, both plugins have to be installed on each server. It is best practice to install both since any slave server can be promoted to master and vice versa.

Installing plugins is a straight forward process as demonstrated below:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

Once the plugins are installed, it is mandatory to configure the replication by editing the option/configuration file (my.cnf or my.ini). The following lines need to be appended to the option/configuration file on both slave and master servers.

rpl_semi_sync_slave_enabled =1
rpl_semi_sync_master_enabled =1

Once the modifications are done, MySQL has to be restarted.

mysql@mysql-qual1: ~/ [mysqld1] mysqld_multi stop 1
mysql@mysql-qual1: ~/ [mysqld1] mysqld_multi start 1

The « SHOW STATUS » command can help check that the modifications have been taken into consideration and the replication is working correctly.

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               |     |
| 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_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

Parameters such as “Rpl_semi_sync_master_clients », « Rpl_semi_sync_master_status” and “Rpl_semi_sync_slave_status” indicate that the semi-synchronous replication is working.

If a timeout occurs (higher than 10 seconds by default) without any slave having acknowledged the transaction, the master reverts to asynchronous replication without any message excepting changing the value of “rpl_semi_sync_master_status” from “ON” to “OFF”. In the same way, as soon as at least one semi-synchronous slave catches up, the master then returns to semi-synchronous replication. This behavior is acceptable for service continuity, but is a real weakness from an operating point of view, since this has to be carefully monitored.

Despite the fact that the timeout value can be changed by editing the variable “rpl_semi_sync_master_timeout”, monitoring a disaster situation is really tricky. Moreover, it is difficult to know what was the “last status before crash”. Was the semi-synchronous replication working or not? Are there any lost transactions? What was the last status of variable “rpl_semi_sync_master_status” before the crash ?

I have no definitive answers to these questions. One piece of solution is implementing a rigorous monitoring solution on your server to double check variables such as “rpl_semi_sync_master_no_times”  and “rpl_semi_sync_master_status ».

Continuous and cautious monitoring, as well as following best practice should be part of every high availability project anyway.

Rate this blog entry:
1

Grégory Steulet  is Chief Financial Officer (CFO) and Region Manager at dbi services. He has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies and high availability solutions (Oracle DataGuard, Data Replication Block Device). His expertise also includes Avaloq banking applications, as well as the open source field (MySQL, Unix/Linux, etc.). Grégory Steulet is "Oracle Certified Professional 10g", "MySQL Cluster 5.1 Certified", and "Avaloq Certified Professional 2.6". Prior to joining dbi services, Grégory Steulet was Senior Consultant at Trivadis in Lausanne. He also worked as IT Administrator at Box Telecom in Miami Beach, Florida (USA). Grégory Steulet has an Executive MBA from the International Institute of Management in Technology, Fribourg (CH). He also holds a Bachelor's Degree in Business Administration and Computer Science from the University of Applied Sciences Western Switzerland. His branch-related experience covers Telecommunications, Financial Services / Banking, Logistics, Pharma etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Saturday, 26 July 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter