To MGR or Not MGR? Review of MySQL Group Replication

简介: This blog provides insights on basics of MGR and Galera and ponders the question of whether or not MongoDB will become the next Galera.


MySQL Group Replication GA

On December 12, 2016, Oracle released exciting news to the MySQL circle. It officially launched version 5.7.17 of MySQL, which includes the long-awaited MySQL Group Replication (MGR). This article provides insights on the basics of MGR and Galera and ponders the question of whether or not MongoDB will become the next Galera.

What is MGR?

MGR is a highly available solution officially launched by MySQL. It is based on the native replication technology and is available in the plug-in form. For those of you not in the know, below are some of the main features of MGR:

●Backup and Change Master for replication is no longer viable. With MGR, the replication management operations are more automated.

●MGR uses the Paxos protocol and displays high data consistency among the database cluster node.

●All nodes among clusters can be replicated, which is the dream function of many developers. The inconvenience of data-writing performance of a single group is solved as MGR allows all nodes to be replicated.

●The split cluster issue caused by network partitioning is resolved which in turn upgrades the reliability of data replication.

Although MGR is also known as the MySQL version of Oracle Real Application Clusters (RAC), there are architectural differences between MGR and RAC. MGR shares nothing while Oracle RAC shares everything.


What is Galera? Will be Phased Out

Galera is a multi-master cluster based on synchronous replication. It is an easy-to-use solution that offers scalability and data security.

Those familiar with Galera may see similarities between MGR and Galera. Some companies and banks have tried Galera's high availability solution for their production environments and have encountered severe problems. Compared with Galera, MGR's advantages are:
●MGR is an official product by MySQL with guaranteed product control and subsequent technical support.
●MGR works on the popular Paxos algorithm. This enables it to achieve optimal network performance and stability. The distributed peer-to-peer system of MGR also reduces overall latency. MGR supports nine nodes per group currently as compared to Galera which supports only three nodes.
●MGR supports multiple operating system platforms, while Galera only supports the Linux system.

First, let's look at MGR's performance improvement in a multi-master node environment, namely carrying out read/write tests at multiple nodes at the same time:


When the flow-control-mode parameter is disabled, i.e. when delay among cluster nodes is allowed, the MGR cluster performance can be elevated with the increase in the number of nodes. If it is configured to ensure reading consistency, the performance of an MGR cluster with five nodes is almost equivalent to that of a single MySQL instance.

Comparison Between MGR and Galera

The following figure compares MGR and Galera:


Group Replication delivers more than 80 percent of the throughput of asynchronous replication when durability is off. The impact of the use of durability on Galera results in a larger overhead. It only achieves 30 percent asynchronous replication throughput.

Limitations of MGR

Although MySQL Group Replication provides high availability and a multi-master update everywhere in the replication solution, it still has few limitations.
●MGR only supports InnoDB tables, and each table must have a primary key for conflict detection of write sets.
●The global transaction identifier (GTID) feature must be enabled, and the binary log format must be set to ROW to select the master and write set.
●COMMIT may lead to a failure, which is similar to a snapshot failure scenario at the transaction isolation level.
●Currently, MGR supports a maximum of nine nodes.
●The foreign key and save point features are not supported and global constraint detection and partial rollback cannot be carried out.
●The binary log is not compatible with binlog event checksum.

Will MongoDB Become the Next Galera?

According to Oracle's current development roadmap, a distributed document database cluster will be created in the future. InsideMySQL is hoping for the GA of InnoDB Cluster in the future. The development of MGR brings it one step closer. The more powerful MongoDB is a worthy rival for competition. In fact, it's worth waiting for a distributed document database based on MySQL, the most popular and stable option on the internet, which supports transactions, row level locks, MultiVersion Concurrency Control (MVCC), and high data consistency.

1.Zooming-in on Group Replication performance
2.Performance Evaluation: MySQL 5.7 Group Replication (GA Release)
3.InnoDB Cluster User Guide
Original article: Galera to be phased out - MySQL Group Replication officially launched

阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情:
SQL 算法 关系型数据库
34 0
关系型数据库 MySQL
MySQL 基于 GTID 的组复制(MGR)
MySQL 基于 GTID 的组复制(MGR)
85 0
SQL 存储 运维
MySQL MGR 介绍 | 学习笔记
快速学习 MySQL MGR 介绍,介绍了 MySQL MGR 介绍系统机制, 以及在实际应用过程中如何使用。
189 0
MySQL MGR 介绍 | 学习笔记
关系型数据库 MySQL
MySQL Group Replication
MySQL Group Replication
51 0
关系型数据库 MySQL
MySQL Group Commit
MySQL Group Commit
81 0
运维 负载均衡 算法
MGR是Mysql Group Replication(组复制)的缩写,Mysql5.7之后是以一个Mysql插件的形式集成在Mysql中,用于创建可伸缩、高可用、可容错的复制架构,是Mysql集群的一种形式
888 0
关系型数据库 MySQL
存储 关系型数据库 MySQL
MySQL 5.7 MGR部署
实现MySQL数据库主从强同步复制,保证主从库数据的强一致性,MySQL 5.7推出了MGR, 我以实验的方式给大家分享部署MGR。
MySQL 关系型数据库 测试技术
ProxySQL MySQL8.0.12 MGR
3954 0
JavaScript 关系型数据库 MySQL
MySQL GR(group replication) Single Primary
4878 0