I've been looking to see if it was possible to use ExpressionEngine with an Percona XtraDB cluster, I have yet to find a verdict, the closest thing was the post below but there does not seem to be consensus as to whether or not it worked. Does anyone have experience using EE with any galera based replication tools. Further Does anyone have experience setting up EE in a redundant DB environment? We are deploying on AWS and would like to have a copy of the database in each availability zone. Thank you!
A few things to make note of when switching to Percona, MyISAM replication is experimental and disabled by default. I would be surprised if it ever becomes fully supported.
MyISAM replication is done at the statement level and as such, causes data inconsistencies when you have a query that utilizes a non deterministic function.
The CI MySQL drivers included in EE are capable of handling InnoDB, and I have not seen any issues with converting the engine across the board. EE isn't set up with connection pools in mind, so if you intend to be clustering your database you will need some sort of Master-Master replication. Also with the connection pools in mind, if you want to utilize all of your databases make sure you put them behind a load balancer to make it easy for EE to understand.
We had a slightly painful release process as a result of Percona due to the fact that some extensions decide MyISAM should be the storage engine rather than just falling to the default. I hooked in to the database classes to log DDL statements to later inspect and see if they created a MyISAM table rather than InnoDB.
I've used EE on a Percona cluster for over a year now. I went down the avenue of writing a database driver implementation for split connection pools, but it wasn't worth my time then when we could just throw a load balancer in front of it. I recently switched to AWS and now use RDS as the database, but we still use Percona for development and testing. All of our automated testing (acceptance, behavioral, and functional) that utilize a database connection hit Percona through a custom database driver to allow nested transactions via InnoDB SAVEPOINTs. With that I can't give much feedback on 2.9 other than it still works.
When you are load balancing your Percona Cluster you want to make sure the health check is doing a query to check the replication health, e.g.
SHOW STATUS LIKE 'wsrep\_local\_state';
Additionally, ensure your connection lifetime for your load balancer equal to the session length configured in MySQL. You may want to consider setting up server affinity on for your databases as well if you have multiple web servers. Not at all a necessity as the gains you may have from it can be addressed other ways.