Percona XtraDB Cluster集群讲解 贺春旸 http://hcymysql.blog.51cto.com

Percona XtraDB Cluster集群介绍 官网：http://www.percona.com/doc/percona-xtradb-cluster/5.5/ Percona XtraDB Cluster provides: Synchronous replication. Transaction either commited on all nodes or none. 译：实时同步复制。事物要么在所有节点上都提交，要么就都失败。 Multi-master replication. You can write to any node. 译：多主复制。你可以在任意一个节点上去写。 Parallel applying events on slave. Real “parallel replication”. 译：实现了真正意义上的“并行复制（多线程并发同步）” Automatic node provisioning. 译：节点自动部署。 Data consistency. No more unsynchronized slaves. 译：数据不丢失，保证了数据一致性。 Percona XtraDB Cluster is fully compatible with MySQL or Percona Server in the following meaning: 译：提供了良好的数据兼容性，MySQL的数据可以直接使用无需转换。应用上也无需做调整。 Data compatibility. Percona XtraDB Cluster works with databases created in MySQL / Percona Server Application compatibility. There is no or minimal application changes required to start work with Percona XtraDB Cluster

Percona XtraDB Cluster集群介绍 The Cluster consists of Nodes. Recommended configuration is to have at least 3 nodes, but you can make it running with 2 nodes as well. 译：至少3个节点，当然2个也可以，但为了防止脑裂，我们这里推荐为3个。 Each Node contains the full copy of data. That defines XtraDB Cluster behavior in many ways. And obviously there are benefits and drawbacks. 译：每个节点都包含着一份完整的数据副本。

Percona XtraDB Cluster集群介绍 Benefits of such approach: 利： When you execute a query, it is executed locally on the node. All data is available locally, no need for remote access. No central management. You can loose any node at any point of time, and the cluster will continue to function. 译：架构简单，没有中央管理节点，不像MySQL NDB引擎那样。 一个节点挂掉，其他节点继续工作。 Good solution for scaling a read workload. You can put read queries to any of the nodes. 译：你可以在任意一个节点上查询，因为节点上的数据都是一致的，这样也分担了机器的压力。 Drawbacks: 弊： Overhead of joining new node. The new node has to copy full dataset from one of existing nodes. If it is 100GB, it copies 100GB. 译：如果节点数据很大，那么新增加的节点开销就会越大，如果节点是100G数据，那么就要复制100G数据到新增加的节点上。 This can’t be used as an effective write scaling solution. There might be some improvements in write throughput when you run write traffic to 2 nodes vs all traffic to 1 node, but you can’t expect a lot. All writes still have to go on all nodes. You have several duplicates of data, for 3 nodes – 3 duplicates. 译：对于写操作来说，有几个节点，就要写几份数据副本。所以在性能上没有主从复制这种架构好。

Percona XtraDB Cluster集群介绍 Let’s take look into the well known CAP theorem for Distributed systems. Characteristics of Distributed systems: 译：分布式数据库特点 C - Consistency (all your data is consistent on all nodes), 译：一致性（所有节点的数据都是一样的） A - Availability (your system is AVAILABLE to handle requests in case of failure of one or several nodes ), 译：可靠性（一个节点挂了，其他节点继续提供服务。） P - Partitioning tolerance (in case of inter-node connection failure, each node is still available to handle requests). CAP theorem says that each Distributed system can have only two out of these three. MySQL replication has: Availability and Partitioning tolerance. Percona XtraDB Cluster has: Consistency and Availability. That is MySQL replication does not guarantee Consistency of your data, while Percona XtraDB Cluster provides data Consistency. (And yes, Percona XtraDB Cluster looses Partitioning tolerance property). 译：MySQL复制并不能保证数据的一致性，而Percona XtraDB集群提供了数据一致性。

Percona XtraDB Cluster集群介绍 Components Percona XtraDB Cluster is based on Percona Server with XtraDB and includes Write Set Replication patches. It uses the Galera library, version 2.x, a generic Synchronous Multi-Master replication plugin for transactional applications.Galera library is developed by Codership Oy. 译：Percona XtraDB Cluster是基于XtraDB（InnoDB引擎）打的一个实时同步复制和多主复制的一个补丁，使用的Galera library类库，是由Codership Oy公司所研发。 和MySQL Cluster的区别： 1、MySQL集群是通过NDB引擎的基础上实现的，而Percona集群是在InnoDB基础上打了patch实现的。 2、MySQL集群可以设置副本数量，且数据分片，例如123存放到节点1,456存放到节点2，而Percona集群有几个节点就有几个副本，每个节点数据是一致的，不支持分片。 3、MySQL集群通过管理节点，维护着整个集群，而Percona集群没有管理节点，每个节点都是充当master角色。

Percona XtraDB Cluster集群局限性 Percona XtraDB Cluster Limitations Currently replication works only with InnoDB storage engine. Any writes to tables of other types, including system (mysql.*) tables, are not replicated. However, DDL statements are replicated in statement level, and changes to mysql.* tables will get replicated that way. So, you can safely issue: CREATE USER..., but issuing: INSERT INTO mysql.user..., will not be replicated. 译：只支持InnoDB引擎，如果使用其他引擎不能正常复制数据到其他节点。但如果你用DDL语句创建用户，是可以正常复制的，但如果insert into mysql.user表是不能复制的。 Unsupported queries: LOCK/UNLOCK TABLES cannot be supported in multi-master setups. lock functions (GET_LOCK(), RELEASE_LOCK()... ) 译：不支持LOCK/UNLOCK TABLES，以及GET_LOCK(), RELEASE_LOCK().函数。 Query log cannot be directed to table. If you enable query logging, you must forward the log to a file: log_output = FILE. Use general_log and general_log_file to choose query logging and the log file name. 译：general_log日志不能存放到表里，必须输出到文件。 Maximum allowed transaction size is defined by wsrep_max_ws_rows and wsrep_max_ws_size. LOAD DATA INFILE processing will commit every 10K rows. So large transactions due to LOAD DATA will be split to series of small transactions. 译：事务大小是由wsrep_max_ws_rows和wsrep_max_ws_size参数控制，超过了此参数设置，数据复制将失败。LOAD DATA INFILE导入的数据每次1万行，如果事务很大，就会拆分成很多小事物。 Due to cluster level optimistic concurrency control, transaction issuing COMMIT may still be aborted at that stage. There can be two transactions writing to same rows and committing in separate XtraDB Cluster nodes, and only one of the them can successfully commit. The failing one will be aborted. For cluster level aborts, XtraDB Cluster gives back deadlock error code: (Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)). 译：由于集群是乐观锁控制，针对同一行做变更，例如一个事务在节点1，执行update t1 set id=id+1 where id=6; 另一个事务在节点2，也执行update t1 set id=id+1 where id=6; 在提交的时候，另一个事务就会中断，抛出一个死锁。ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Percona XtraDB Cluster集群局限性 XA transactions can not be supported due to possible rollback on commit. 译：不支持XA事务，因为XA事务有可能在commit的时候出现异常发生rollback。 The write throughput of the whole cluster is limited by weakest node. If one node becomes slow, whole cluster is slow. If you have requirements for stable high performance, then it should be supported by corresponding hardware. 译：整个集群的吞吐量受到最弱节点的限制。如果一个节点变得缓慢，整个集群都变得缓慢。如果你有稳定的高性能要求，那么应该用硬件配置相同的服务器。 The minimal recommended size of cluster is 3 nodes. 译：至少3个节点。 Currently using the query cache isn’t supported as it was showing some issues during the cache invalidation with certain multi-master use cases. 译：不支持query_cache缓存，由于在缓存失效后会出现一些问题。

Percona XtraDB Cluster集群注意事项 State Snapshot Transfer is a full data copy from one node (donor) to the joining node (joiner). It’s used when a new node joins the cluster. In order to be synchronized with the cluster, new node has to transfer data from the node that is already part of the cluster. There are three methods of SST available in Percona XtraDB Cluster: mysqldump, rsync and xtrabackup. The downside of mysqldump and rsync is that the donor node becomes READ-ONLY while data is being copied from one node to another (SST applies FLUSH TABLES WITH READ LOCK command). Xtrabackup SST does not require READ LOCK for the entire syncing process, only for syncing the MySQL system tables and writing the information about the binlog, galera and slave information (same as the regular XtraBackup backup). State snapshot transfer method can be configured with the wsrep_sst_method variable. 译：当新加入的节点时，要把某个节点的数据全量拷贝过去。wsrep_sst_method 参数有三个变量，mysqldump，rsync，和xtrabackup，前两个会锁表（只读），这里不推荐，官方推荐xtrabackup，这样基本不会影响访问。

Percona XtraDB Cluster集群注意事项 galera.cache This file is used as a main writeset store. It’s implemented as a permanent ring-buffer file that is preallocated on disk when the node is initialized. File size can be controlled with the variable gcache.size. If this value is bigger, more writesets are cached and chances are better that the re-joining node will get IST instead of SST. Filename can be changed with the gcache.name variable. 译：galera.cache事务日志文件默认128M，循环写入类似redo log，如果在宕机时没有找到恢复的LOG点，那么就要进行一次全量拷贝，如果数据很大的话，这将是一个漫长的过程，所以提高这个参数值，使其增大，这样在恢复的时候，可以增量恢复，节省恢复时间。 wsrep_provider_options="gcache.size = 4G"

Percona XtraDB Cluster集群注意事项 译：调大这个参数将会带来更好的并行复制性能，默认为1，这个值根CPU核数有关。