PXC vs Galera Cluster vs MySQL Group Replication

1 .Group Replication 

2 . Us Ramesh Sivaraman Kenny Gryp QA Engineer MySQL Practice Manager 2 / 60 

3 . Table of Contents 1. Overview 2. Similarities 3. Differences GR & Galera 4. Differences PXC & Galera 5. Limitations 6. Known Issues 7. QA 8. Performance Tests 3 / 60 

4 .Different Technologies Overview 

5 . Galera Cluster Developed by Codership http://galeracluster.com Included in MariaDB Galera Cluster is a synchronous multi-master database cluster, based on synchronous replication and Oracle’s MySQL/InnoDB. When Galera Cluster is in use, you can direct reads and writes to any node, and you can lose any individual node without interruption in operations and without the need to handle complex failover procedures. Replication is synchronous, Applying is asynchronous 5 / 60 

6 . Percona XtraDB Cluster Patched Galera Cluster, developed by Percona https://www.percona.com/software/mysql- database/percona-xtradb-cluster Generally Available Since April 2012 With additional features Extended PFS support SST/XtraBackup Changes Bug-Fixes PXC Strict mode * ProxySQL integration * Performance Enhancements * 6 / 60 

7 . MySQL Group Replication Developed by Oracle Generally Available in MySQL 5.7.17 on December 2016 MySQL InnoDB Cluster as Solution MySQL Group Replication is a MySQL Server plugin that provides distributed state machine replication with strong coordination between servers. Servers coordinate themselves automatically, when they are part of the same replication group. Any server in the group can process updates. Con icts are detected and handled automatically. There is a built-in membership service that keeps the view of the group consistent and available for all servers at any given point in time. Servers can leave and join the group and the view will be updated accordingly. 7 / 60 

8 .MySQL InnoDB Cluster 8 / 60 

9 .They have a lot in common Similarities 

10 . Similarities MySQL/MariaDB Replication Method Data centric - All nodes have all data Reads happen on the local node only All require InnoDB/XtraDB as Storage Engine Active-active multi-master Topology Write to multiple nodes No complex/external failover necessary Node membership: join/leave automatically Execute writes in Global Total Order Data Consistency! Optimistic Locking / First Committer Wins Quorum - split brain prevention 10 / 60 

11 . Similar - Use Cases Environments with strict durability requirements Write to multiple nodes simultaneously while keeping data consistent Reduce failover time 11 / 60 

12 . Similar Limitations Large & Long running transactions Higher chance on failures non-e cient replication of large transations Workload hotspots (updating same set of rows in parallel) Often writing to 1 node is the best solution 12 / 60 

13 .Group Replication vs. Galera Based Differences 

14 . Differences Group Communication System Binlogs & Gcache Node Provisioning GTID vs. Seqno Partition Handling Full Solution or Plugin Flow Control WAN Support OS Support Schema Changes 14 / 60 

15 . Group Communication Galera: Totem Single-ring Ordering All nodes have to ACK message Group Replication: Xcom, similar to Paxos Mencius Paxos only requires majority of nodes to ACK the message 15 / 60 

16 . Binlogs & GCache Galera Cluster/PXC: uses binlog row events but does not require binary logging writes events to Gcache (con gurable size) Group Replication: requires binary logging 16 / 60 

17 . Node Provisioning Galera Cluster/PXC: has State Snapshot Transfer (SST): Percona XtraBackup (Recommended) rsync mysqldump incremental State Transfer (IST) using GCache MySQL Group Replication: currently no automatic provisioning restoring a backup is required (unless you keep all binary logs) asynchronous replication channel for syncing 17 / 60 

18 . GTID vs. Seqno MySQL Group Replication: built around MySQL GTID. writes to a cluster create GTID events on the GR Cluster UUID Galera Cluster/PXC: has a seqno which is a incrementing number 18 / 60 

19 . Partition Handling Galera Cluster/PXC: A partitioned node will refuse reads/writes (con gurable) A partitioned node will automatically recover and rejoin Group Replication: A partitioned node will accept reads A partitioned node will accept write requests, but will hang forever A partitioned node needs to be manually rejoined to the cluster 19 / 60 

20 . Full Solution or Plugin Plugin: Group Replication is a 'Replication Plugin' several split brain bugs in current code ( xes pending!) Solution: Galera Cluster, handling application connections is not included strong split brain prevention compared to current GR MySQL InnoDB Cluster (w. MySQLRouter) Full Solution: Percona XtraDB Cluster (w. ProxySQL) integrated ProxySQL strict mode prevents limitations from being used 20 / 60 

21 . Flow Control Prevent a slower node from getting too far behind Galera Cluster/PXC: block all writes in cluster when a node reaches a limit ow control message is sent low defaults; Galera: 16(*), PXC: 100 Tell others to stop writes MySQL Group Replication: every node has statistics about every member each individual node decides to throttle writes high default: 25000 Slow down your own writes if other nodes are struggling 21 / 60 

22 . WAN Support MySQL Group Replication: not recommended for WAN Galera Based Systems have WAN features: Weighted Quorum Tunable network communication settings Reduce network tra c with segments Arbitrator 22 / 60 

23 . Operating System Support Galera: FreeBSD & Linux Percona XtraDB Cluster: Linux Group Replication: Linux, Windows, Solaris, OSX, FreeBSD 23 / 60 

24 . Schema Changes - DDL Galera Cluster/PXC: Total Order Isolation: All writes will be blocked during Writes on other nodes will be terminated Workarounds: pt-online-schema-change wsrep_osu_method=RSU More operational work Not for all DDL's Group Replication: DDL does not block all writes, like regular InnoDB Only recommended in single-primary mode 24 / 60 

25 .Percona XtraDB Cluster vs. Galera Differences 

26 . Percona XtraDB Cluster vs Galera Cluster PXC has additional features: Extended PFS support SST/XtraBackup Changes Bug-Fixes PXC Strict mode * ProxySQL integration * Performance Enhancements * 26 / 60 

27 . PXC Strict Mode Prevent experimental/unsupported features: Only Allow InnoDB Operations Prevent Changing binlog_format!=ROW Require Primary Key on tables Disable Unsupported Features: GET_LOCK, LOCK TABLES, CTAS FLUSH TABLES <tables> WITH READ LOCK tx_isolation=SERIALIZABLE 27 / 60 

28 . ProxySQL Integration PXC includes ProxySQL as load balancer: proxysql-admin con guration tool ProxySQL schedulers : Health Checks Recon gures Nodes PXC Maintenance Mode Tell load balancer to rebalance load 28 / 60 

29 . Performance Enhancements Several Scalability Fixes in PXC 5.7.17 New Defaults: gcs.fc_limit=100 evs.send_window=10 evs.user_send_window=4 29 / 60