Emphasis on clustering solutions comes up quite a lot when talking to customers about High Availability. The reason is because clustering is supposed to provide an easier solution for maintaining high availability and so that you do not have to rely on other tools and techniques outside of the database server.
I thought it would be good to share the gist of many of my discussions around clustering, in the form of a blog post.
People usually tend to compare MySQL NDB Cluster and Percona XtraDB Cluster but both of them really are very different solutions.
For one NDB Cluster would mean a complete rethink of how data is accessed by the application. You also get to have to deal with a storage engine that works and behaves differently from InnoDB storage engine. The key point with NDB Cluster is data partitioning between different nodes. Not all applications are built with partitioning in mind specifically. And such you would have to adapt the application to make sure that it is aware of that and can distribute the workload effectively. This may mean partitioning in such a way that a single application request does not imply having to go to each partition to fetch data. I have typically seen a lot of effort put into applications when moving from traditional InnoDB based solution to NDB based solution. Moving to NDB Cluster is a major major change and as such implies that you would probably need to rewrite parts of the application to make it work effectively.
To summarize: MySQL NDB Cluster is not a drop-in-place clustering solution.
However, Percona XtraDB Cluster, which is Galera-based clustering solution, is a drop-in-place clustering solution and it truly is. You might have to do minute changes in the application, but you can even live with not having to do any. If you are already using InnoDB storage engine, then you do not need to make any changes to the application, except may be for the fact that deadlocks happen a bit differently. Percona XtraDB Cluster provides a clustering layer over traditional MySQL server with InnoDB storage engine, such that everything is exactly the same except how replication is performed. XtraDB Cluster is a synchronous replication cluster, meaning that every transaction COMMIT implies that the transaction is replicated to every node in the cluster where a process known as certification is performed to validate the transaction that is committed and perform conflict resolution. And you get other benefits with it:
- High Availability: You can read/write to any node, when a node goes down, you can start reading/writing from a different node
- Data Consistency: You do not have to worry about data consistency in the same way as you do with regular MySQL master-slave pair
- Parallel Replication: You get true parallel replication without the restrictions that are present in MySQL 5.6
- Partitioning Protection: Partitioning protection is built inside this clustering solution
What I really like about this solution, other then the fact that this is a true drop-in-place solution, is that you do not have to rely on other pieces of software to get high availability or partitioning protection. These things are already taken care of and as such this greatly reduces the complexity of this clustering solution. Otherwise with a typical MySQL master-slave setup you have to rely on other solutions such as Pacemaker to get high availability and other related things. The other difference of course when compared to MySQL NDB Cluster is that data is not partitioned in any way, all the data is present on every node in a XtraDB Cluster.
To summarize: Percona XtraDB Cluster is a drop-in-place clustering solution.
I would suggest that you read more about Percona XtraDB Cluster here and give it a try when the next time you want to move to a clustering solution.