Percona XtraDB Cluster vs Galera Cluster vs MySQL Group Replication
Overview
Galera Cluster
由 Codership 开发 官网
包含在MariaDB，在Percona、MySQL 都可以使用
Galera Cluster 是一个基于 InnoDB 多主的同步复制，可以读写任何节点，即使失去任何一个节点也不影响业务中断，而且无需复杂的 failover 操作。
Percona XtraDB Cluster
由 Percona 开发，在 Galera 基础打 Patch [官网](https://www.percona.com/software/mysql- database/percona-xtradb-cluster)
自 2012 年 4 月可用
附加的特性
PFS 扩展支持
SST/XtraBackup 方式的改变
PXC Strict mode *
ProxySQL 集成 *
提升性能 *
MySQL Group Replication
由 Oracle 官方开发
2016 年 12 月 MySQL 5.7.17 发布 GA
MySQL InnoDB Cluster 整体解决方案
MySQL Group Replication 是一个 MySQL Server Plugin，提供分布式状态机复制与 Server 强大协调，当在一个 Group Replication 时，Server 将自动协调，每个节点都可以自动处理更新，自动检测，有一个 membership service 维护一个 view，记录组内 记录可见成员在某个时间点一致性和高可用性的，当任何一个成加入或离开，view 就会相应的更新
MySQL InnoDB Cluster
-w610
Similarities
MySQL/MariaDB
复制方法
所有节点都有全部的数据 - 读仅发生在当前本地节点
都需要 InnoDB/XtraDB 引擎
都支持 多主的拓扑 (Active-active multi-master Topology)
写多主节点
不需要复杂的 Failover 操作
节点管理，加入和离开都是完全自动处理的
执行和写都是按全局的顺序进行的
数据一致性
乐观锁 / 最早提交的先赢
Quorum - 防止脑裂
Similar - Use Cases
严格的持久化环境
同时定入多个节点，保证数据一致性
减少故障转移时间 (Reduce failover time)
Similar Limitation
大事务或长事务
很大的可能失败
大事务导致在有效事务
热点，多个节点更新同一行数据
只有一个节点写，是最好的解决方案
Differences GR & Galera
Group Communication System 组通信系统
Binlogs & Gcache
Node Provisioning 节点配置
GTID vs Seqno
Partition Handling 分区控制
Full Solution or Plugin
Flow Control 流控制
WAN Support
OS Support
Schema Changes DDL
Group Communication System 组通信系统
Galera
标志的单环顺序 Totem Single-ring Ordering
所有节点都必须有 ACK 消息
MySQL Group Replication
基于 Paxos
Paxos 只要求大多数 ACK 消息
Binlogs & Gcache
Galera Cluster/PXC
uses binlog row events
but does not require binary logging
writes events to Gcache (con gurable size)
MySQL Group Replication
requires binary logging
Node Provisioning 节点配置
Galera Cluster/PXC
has State Snapshot Transfer (SST) 全量同步
Percona XtraBackup (Recommended)
rsync
mysqldump
incremental State Transfer (IST) using GCache 增量同步
MySQL Group Replication
没有自动全量同步，除非保留所有 Binlog
用于同步的异步复制通道
GTID vs. Seqno
Galera Cluster/PXC
has a seqno which is a incrementing number
MySQL Group Replication
依赖 MySQL GTID
writes to a cluster create GTID events on the GR Cluster UUID
Partition Handling
Galera Cluster/PXC
A partitioned node will refuse reads/writes (con gurable)
A partitioned node will automatically recover and rejoin
MySQL 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
Full Solution or Plugin
Plugin
Group Replication is a 'Replication Plugin'
several split brain bugs in current code ( fixes pending!)
Solution
Galera Cluster 不处理应用连接
与 GR 相比有更强的防止脑裂
MySQL InnoDB Cluster (w. MySQLRouter)
Full Solution
Percona XtraDB Cluster (w. ProxySQL)
集成 ProxySQL
严格的 mode，防止使用限制
Flow Control 流控制
防止太慢的节点，被落下太远
Galera Cluster/PXC
当一个节点慢到一个限制，阻止所有节点写
Flow Control 的消息已经发送
low defaults; Galera: 16(*), PXC: 100
Tell others to stop writes
MySQL Group Replication
每个节点都有每个成员的统计信息
每个独立的节点决定写的阈值
high default: 25000
如果有节点太慢，其他节点放慢写速度
WAN Support
Galera Cluster/PXC
投票权重
调节网络通信设置
减少分段网络流量
仲裁人
MySQL Group Replication
不建议在 WAN 上使用
OS Support
Galera
FreeBSD & Linux
Galera Cluster/PXC
Linux
MySQL Group Replication
Linux, Windows, Solaris, OSX, FreeBSD
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
更多的操作工作
并不是所有 DDL 可行
MySQL Group Replication
DDL 并不会阻塞写，像平常使用一样
仅建议在单主模式下使用 (因为 DDL 并没有冲突检测)
Differences PXC & Galera
扩展 PFS 支持
SST/XtraBackup 方式的改变
Bug-Fixes
PXC Strict Mode - PXC 严格的 Mode
ProxySQL 集成
性能提升
PXC Strict Mode
预防实验性或不支持的特性
仅支持 InnoDB 的操作
预防修改 binlog_format!=ROW
需要 Table 有主键
禁用不支持的特性
GET_LOCK,LOCK TABLES,CTAS
FLUSH TABLES <tables> WITH READ LOCK
tx_isolation=SERIALIZABLE
ProxySQL 集成
ProxySQL 在 PXC 当负载均衡的角色
proxysql-admin 配置工具
ProxySQL schedulers
健康检查
重新配置节点
PXC 维护模式
tell load balancer to rebalance load
性能提升
可伸缩性 fixs PXC 5.7.17
新的默认值
gcs.fc_limit=100
evs.send_window=10
evs.user_send_window=4
[图片上传失败...(image-b39cb1-1513951864372)]
Limitations
Galera Cluster/PXC
InnoDB/XtraDB Only
tx_isolation=SERIALIZABLE
GET_LOCK()
LOCK TABLES
SELECT ... FOR UPDATE
Careful with ALTER TABLE ... IMPORT/EXPORT
Capped maximum transaction size 最大事务
XA transactions
Group Replication
InnoDB/XtraDB Only
tx_isolation=SERIALIZABLE
GET_LOCK()
LOCK TABLES
SELECT ... FOR UPDATE
CarefulwithALTER TABLE ... IMPORT/EXPORT.
Careful with large transactions
no support for tables with multi-level foreign key dependencies, can create inconsistencies
Known Issues
Galera Cluster/PXC Issues
Crashes due to background thread handling trx
processing
mysql-wsrep#306: stored procedure aborts
mysql-wsrep#305: event scheduler
mysql-wsrep#304: local scope functions such as CURRENT_USER()
Various crashes related to DDL
mysql-wsrep#301:runningSHOW CREATE TABLE in multiple nodes with DDL can cause crash.
mysql-wsrep#275: Aborting trx leaves behind open tables in cache can cause crash
Concurrent DDLs using wsrep_OSU_method=RSU crash/inconsistency issues
mysql-wsrep#283 & mysql-wsrep#282
Shutdown issues
mysql-wsrep#303: cleanup during shutdown fails to clear the EXPLICT MDL locks (FTWRL)
mysql-wsrep#273: Not getting clean shutting down message if we start the server with unknown variable
mysql-wsrep#279: Trying to access stale binlog handler leads to crash
Group Replication Issues
分区容忍问题，不能防止脑裂
* #84727: partitioned nodes still accept writes: queries hang (~Fixed in 5.7.20)
* #84728: GR failure at start still starts MySQL (~Fixed in 8.0.2)
* #84729: block reads on partitioned nodes
* #84733: not possible to start with super_read_only=1 (Fixed in 8.0.2)
* #84784: Nodes Do Not Reconnect
* #84795: STOP GROUP_REPLICATION sets super_read_only=off
减少冲突
* #84731: mysql client connections get stuck during GR start
稳定性
* #84785: Prevent Large Transactions in Group Replication (~Fixed in 5.7.19)
* #84792: Member using 100% CPU in idle cluster (*)
* #84796: GR Member status is wrong
可用性
* #84674: unresolved hostnames block GR from starting (~Fixed in 5.7.18)
* #84794: cannot kill query that is stuck inside GR
* #84798: Group Replication can use some verbosity in the error log
Q & A
这个看原 PDF 吧。
