Galera (for MySQL, Percona XtraDB Cluster or MariaDB Cluster, they are essentially the same but from different vendors and base mysql version) can work perfectly with 2 machines, and it is a very common setup for substituting standard MySQL replication.
Requiring 3 nodes is not a requirement of Galera, but of any cluster that cares about data consistency over availability. In other words, it is needed in order to provide availability and data integrity in the case of only one node suffering from network partition (causing a "split brain" on the cluster). We need an odd number of nodes and 1 node is not a cluster :-). You probably knew that, but I wanted to clarify this for any people reading this answer.
Given that the only reason to have 3 nodes is for availability in case of a node failure/network failure, the recommended way to setup a 2 node cluster with network split protection is to setup a Galera Arbitrator which is essentially an emulation of a MySQL node, but without storing any local data. You can install that anywhere, although you must be careful because if still may influence the performance of the cluster if it has network latency problems.
Running several instances on the same physical node is useless (docker is not needed, by the way, you can do that by just changing the local configuration), as if you absolutely can only run things on 2 machines, you better disable the automatic shutdown on one of the nodes and manage the failover manually. The 3 limitation is just for data consistency, there is nothing physical or on the protocol requiring a certain number of nodes.
You can read more information about it on the official documentation.
You should put everything on a level playing field. How ?
Without proper tuning, it is possible for older versions of MySQL to outrun and outgun new versions.
Before running SysBench on the three environments
- Make sure all InnoDB settings are identical for all DB Servers
- For the Master/Slave, run
STOP SLAVE;
on the Slave
- For PXC (Percona XtraDB Cluster), shutdown two Masters
Compare the speeds of just standalone MySQL, Percona, and MariaDB.
ANALYSIS
If MySQL is best (Percona people, please don't throw rotten vegetables at me just yet. This is just conjecture), run START SLAVE;
. Run SysBench on the Master/Slave. If the performance is significant slower, you may have to implement semisynchronous replication.
If PXC is best, you may need to tune the wsrep settings or the network itself.
If MariaDB is best, you could switch to MariaDB Cluster (if you have the Money) or setup Master/Slave with MariaDB. Run Sysbench. If the performance is significant slower, you may need to tune the wsrep settings or the network itself.
Why tune wsrep settings ? Keep in mind that Galera wsrep (WriteSet Replication) uses virtually synchronous commits and rollbacks. In other words, either all nodes commit or all nodes rollback. In this instance, the weakest link would have to be
- how fast the communication between Nodes happens (especially true if the Nodes are in different data centers)
- if any one node has underconfigured hardware settings
- if any one node communicates slower than other node
Side Note : You should also make sure tune MySQL for multiple CPUs
UPDATE 2014-11-04 21:06 EST
Please keep in mind that Percona XtraDB Cluster does not write scale very well to begin with. Note what the Documentation says under its drawbacks (Second Drawback):
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.
SUGGESTION #1
For PXC, turn off one node. Run SysBench against a two node cluster. If the write performance is better than a three node cluster, then it is obvious that the communication between the nodes is the bottleneck.
SUGGESTION #2
I noticed you have a 42GB Buffer Pool, which is more than half the server's RAM. You need to partition the buffer pool by setting innodb_buffer_pool_instances to 2 or more. Otherwise, you can expect some swapping.
SUGGESTION #3
Your innodb_log_buffer_size is 8M by default. Try making it 256M to increase log write performance.
SUGGESTION #4
Your innodb_log_file_size is 512M. Try making it 2G to increase log write performance. If you apply this setting, then set innodb_log_buffer_size to 512M.
Best Answer
Galera-based clusters like Percona XtraDB Cluster support true active/active multi-master, so failover is seamless because you can actually be writing on any node at any time (tho it's recommended to write only on a single node to avoid performance issues due to lock-conflicts that can happen due to the optimistic locking strategy used by Galera).
This type of cluster can also do distributed fully-synchronous reads so you can scale heavy read loads without problems; One shining example is Magento, which is read-heavy and has many reads that are critical -think money- and traditionally needed to be done on master to guarantee consistency, and here they can be distributed.
Keep in mind that as with any distributed CP system, Galera-cluster will add latency to the writes as it has to verify the write is valid on the other nodes, and hence it's not designed to scale writes; That said: it can usually withstand same write load than a regular master, as long as you keep transactions small (in terms of amounts of rows affected) and short (in regards to time spent by active transaction holding locks).
Also make sure to read list of limitations here: https://www.percona.com/doc/percona-xtradb-cluster/LATEST/limitation.html
To decrease downtime you can migrate by setting up the GCP cluster as an asynchronous slave of the current cluster (yes, Galera cluster nodes can act as a master and/or slave in traditional, binlog based, asynchronous replication). To prime the slave cluster you can use XtraBackup, which can take fully lock-less backups from current cluster (make sure to use latest 2.3 XtraBackup) which you can then restore on one of the nodes and allow the other two nodes to perform Snapshot State Transfer. Then simply designate one of the nodes to become the slave, and start replication using binlog coordinates from xtrabackup_binlog_info.
Overview of steps would be:
Enable log-bin, log-slave-updates, server_id on one of the nodes from the current cluster
Take a backup of that node using xtrabackup (make sure to desync the node before: https://www.percona.com/blog/2013/10/08/taking-backups-percona-xtradb-cluster-without-stalls-flow-control/).
Bring up one of the nodes in GCP cluster and restore the backup into it, and bootstrap the cluster with this node. Most likely use cloud storage to feed the backup to the cluster.
Bring up the other two nodes in GCP, one at a time and allow them to perform SST
Setup one of the GCP cluster nodes as async slave to the current cluster (this node also requires log-bin, log-slave-updates, server-id); this node will feed the other nodes with updates
Failover to new cluster once the slave node is caught up
Hope that helps! :)
Full disclosure: am a member of Percona Support team.