Percona XtraDB Cluster

Overview

Percona XtraDB Cluster is a version of Percona Server implementing the Galera replication protocol from Codeship.

Percona XtraDB Cluster provides:

  • Synchronous replication. Transaction either committed 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.

There is no failover solution, Percona recommends haproxy on every client node.


Limitations

  • Currently replication works only with InnoDB storage engine.

  • Unsupported queries:

LOCK/UNLOCK TABLES cannot be supported in multi-master setups.

lock functions (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.

  • 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.

  • 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)).

  • XA transactions can not be supported due to possible rollback on commit.

  • 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.

  • Currently using the query cache isn’t supported as it was showing some issues during the cache invalidation with certain multi-master use cases.

  • No manual failover mode

Summary

This solution can be considered as worth testing for large MySQL setup. Major downsides are:

  • lack of query cache, which can cause potential performance problems for not well written applications (which don't use cache and generates a lot of the same queries)

  • at least 3 nodes minimal setup ( but one can be a lightweight arbitrator)