Thanks to all who attended my Nov. 12 webinar titled, “Percona XtraDB Cluster as a MySQL HA Solution for OpenStack.” I had several questions which I covered at the end and a few that I didn’t. You can view the entire webinar and download the slides here.
Q: Is the read,write speed reduced in Galera compared to normal MySQL?
For reads, it’s the same (unless you use the sync_wait feature, used to be called causal reads).
For writes, the cost of replication (~1 RTT to the worst node), plus the cost of certification will be added to each transaction commit. This will have a non-trivial impact on your write performance, for sure. However, I believe most OpenStack meta store use cases should not suffer overly by this performance penalty.
Q: Does state transfers affect a continuing transaction within the nodes?
Mostly, no. The joining node will queue ongoing cluster replication while receiving its state transfer and use that to do its final ‘catchup’. The node donating the state transfer may get locked briefly during a full SST, which could temporarily block writes on that node. If you use the built-in clustercheck (or check the same things it checks), you can avoid this by diverting traffic away from a donor.
Q: Perhaps not the correct webinar for this question, but I was also expecting to hear something about using PXC in combination with OpenStack Trove. If you’ve got time, could you tell something about that?
Trove has not supported the concept of more than a single SQL target. My understanding is a recent improvement here for MongoDB setups may pave the way for more robust Trove instances backed by Percona XtraDB Cluster.
Q: For Loadbalancing using the Java Mysql driver, would you suggest HA proxy or the loadbalancing connection in the java driver. Also how does things work in case of persistent connections and connection pools like dbcp?
Each node in a PXC cluster is just a mysql server that can handle normal mysql connections. Obviously if a node fails, it’s easy to detect that you should probably not keep using that node, but in Percona XtraDB Cluster you need to watch out for things like cluster partitions where nodes lose quorum and stop taking queries (but still allow connections) I believe it’s possible to configure advanced connection pooling setups to properly health check the nodes (unless the necessary features are not in the pool implementation), but I don’t have a reference architecture to point you to.
Q: Are there any manual solutions to avoid deadlocks within a high write context to force a query to execute on all nodes?
Yes, write to a single node, at least for the dataset that has high write volume.
Remember what I said in the webinar: you can only update a given row once per RTT, so there’s an upper cap of throughput on the smallest locking granularity in InnoDB (i.e., a single row).
This manifests itself in two possible ways:
1) In a multi-node writing cluster by triggering deadlocks on conflicts. Only approximately 1 transaction modifying a given row per RTT would NOT receive a deadlock
2) In a single-node writing cluster by experiencing longer lock waits. Transaction times (and subsequently lock times) are extended by the replication and certification time, so other competing transactions will lock wait until the blocking transaction commit. There are no replication conflict deadlocks in this case, but the net effect is exactly the same: only 1 transaction per row per RTT.
Galera offers us high data redundancy at the cost of some throughput. If that doesn’t work for your workload, then asynchronous replication (or perhaps semi-sync in 5.7) will work better for you.
Note that there is wsrep_retry_autocommit. However, this only works for autocommited transactions. If your write volume was so high that you need to increase this a lot to get the conflict rate down, you are likely sacrificing a lot of CPU power (rollbacks are expensive in Innodb!) if a single transaction needs multiple retries to commit. This still doesn’t get around the law: 1 trx per row per RTT at best.
That was all of the questions. Be sure to check out our next OpenStack webinar on December 10 by my colleague Peter Boros. It’s titled “MySQL and OpenStack Deep Dive” and you can register here (and like all of our webinars, it’s free). I also invite you to visit our OpenStack Live 2015 page and learn more about that conference in Santa Clara, California this coming April 13-14. The Call for Papers ends Nov. 16 and speakers get a full-access conference pass. I hope to see you there!
