To MGR or Not MGR? Review of MySQL Group Replication

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: This blog provides insights on basics of MGR and Galera and ponders the question of whether or not MongoDB will become the next Galera.

Galera_to_be_phased_out_MySQL_Group_Replication_officially_launched

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.

Diagram_1

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:

Diagram_2

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:

Diagram_3

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.

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
11天前
|
存储 SQL 关系型数据库
MySQL Group 字段逗号链接
【4月更文挑战第26天】
|
11天前
|
SQL 关系型数据库 MySQL
MySQL GROUP BY 语句
MySQL GROUP BY 语句
|
11天前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
|
11天前
|
SQL 存储 关系型数据库
【mysql】将逗号分割的字段内容转换为多行并group by
【mysql】将逗号分割的字段内容转换为多行并group by
|
11天前
|
SQL 关系型数据库 MySQL
923.【mysql】 only full group by 模式
923.【mysql】 only full group by 模式
28 1
|
11天前
|
监控 负载均衡 关系型数据库
MySQL技能完整学习列表13、MySQL高级特性——1、分区表(Partitioning)——2、复制(Replication)——3、集群(Clustering)
MySQL技能完整学习列表13、MySQL高级特性——1、分区表(Partitioning)——2、复制(Replication)——3、集群(Clustering)
57 0
|
11天前
|
关系型数据库 MySQL Java
MySQL group by分组后,将每组所得到的id拼接起来
MySQL group by分组后,将每组所得到的id拼接起来
38 0
|
11天前
|
关系型数据库 MySQL 数据库
Mysql-错误处理: Found option without preceding group in config file
Mysql-错误处理: Found option without preceding group in config file
202 0
|
11天前
|
SQL 算法 关系型数据库
MySQL查询优化之order by 、 group by与分页查询优化
MySQL查询优化之order by 、 group by与分页查询优化
63 0
|
5月前
|
SQL 关系型数据库 MySQL
MySQL中group by子句与having实例分析
MySQL中group by子句与having实例分析
52 0