We are looking to launch a 9 node percona xtradb cluster on Amazon where each ec2 node uses 20k iops disks.
I was afraid if a 9 node cluster will work and mostly what I see is 3 node clusters.
We are write heavy on some tables and also read heavy.
Any suggestions.
PXC and Galera Cluster do not write scale very well.
I mentioned this in my answer to the old post Transaction speed benchmarks for mySQL v5.6 replication - seems very slow where I said the following:
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.
What this means for you is this: The more nodes in the Cluster, the more network communication between nodes is required to certify that all 9 nodes can commit together or rollback together. This increased communication, which makes n(n-1) calls between nodes (where n is the number of nodes), will definitely become the bottleneck for a single INSERT, UPDATE, or DELETE. It becomes worse with multi-statement transactions. This is why most examples of PXC/Galera stress using three nodes. You could probably get away with 5 nodes but you should expect worse write performance than 3 nodes.
You could probably setup 3 different 3-node clusters and use one node from each cluster in Circular Replication (See my post MySQL 5.5 Replication to Galera/Percona XtraDB Cluster). This could easily create split brain scenarios if MySQL Replication breaks between clusters.
Please stick with 3-node cluster. Should you go with 9 nodes, you must live with the network communication across zones. If so, try to make nodes reside on the East Coast in different zones, or on the West Coast in different zones.
Another topology would be a 3-node Galera cluster, plus 2 ordinary readonly slaves hanging off each Galera node. Send non-critical-read requests to any of the 6 slaves; send writes to any of the 3 cluster nodes.
The reason for failure to scale writes is that all writes must be written to all nodes. Row Based Replication (required by Galera) helps lighten the load.
The 'real' fix for write scaling is Sharding. However that does not work for arbitrary workloads.
Would you care to discuss your queries (write & read); perhaps we can find some techniques to help you handle the load.
In this scenario I'd suggest sharding the clusters by functionality. If you have some tables with heavy load, maybe these tables diserve to have its own cluster. You could have a 3/5 nodes Galera cluster for this table, another 3 nodes Galera cluster for the rest of tables and the 1/3 rest of nodes to scale reads if needed.
Of course, you could also shard your tables in 3 clusters of 3 nodes, stimating the use they are going to have and balancing them between the clusters.
This way each cluster only receives the writes (and reads) of its tables, well balanced it'd be as having number-of-clusters * 20K iops.
The counterpart of this approach is that you need some logic in your application or a proxy to know what cluster has each tables, but I think it worths it to be able to scale.
