Q&A: High availability when using MySQL in the cloudMichael Coburn
Last week I hosted a webinar on using MySQL in the cloud for High Availability (HA) alongside 451 Research analyst Jason Stamper. You can watch the recording and also download the slides (free) here. Just click the “Register” button at the end of that page.
We had several excellent questions and we didn’t have time to get to several of them in the allotted time. I’m posting them here along with the answers. Feel free to ask follow-up questions in the comments below.
Q: Can the TokuDB engine be used in a PXC environment?
A: No, TokuDB cannot currently be used in a PXC environment, the only supported engine in Percona XtraDB Cluster 5.6 is InnoDB.
Q: With Galera replication (PXC), is balancing the load on each node?
A: No, you need to implement your own load balancing and HA layer between your clients and the Percona XtraDB Cluster server. Examples mentioned in the webinar include HAProxy and F5 BigIP.
Q: What’s the best version of Percona XtraDB Cluster regarding InnoDB performance?
A: In general for best performance you should be using the latest release of Percona XtraDB Cluster 5.6, which is currently 5.6.24, released on June 3rd, 2015.
Q: Can I redirect my writes in Percona XtraDB Cluster to multiple nodes using the HAProxy? While trying with SysBench I can see write-only goes to first nodes in PXC while reads does goes to multiple nodes.
A: Yes you can configure HAProxy to distribute both reads and writes across all of your nodes in a Percona XtraDB Cluster environment. Perhaps SysBench created only one database connection for all writes, and so haproxy kept those confined to only one host. You may want to experiment with parallel_prepare.lua.
Q: What’s the optimal HA for small datasets (db is less than 10gb)?
A: The optimal HA deployment for small datasets would be dependent on your level of recovery required (tolerance for loss of transactions) and time that you can be in an unavailable state (seconds, minutes, hours?). Unfortunately there isn’t a single answer to your question, however, if you are interested in further discussion on this point Percona would be happy to coordinate a time to speak. Please feel free to contact me directly and we can continue the conversation at email@example.com.
Q: Is there a concept of local master vs. remote master with PXC?
A: No there is no concept of local vs remote master. All nodes in a Percona XtraDB Cluster can now be classified as Master, regardless of their proximity to the clients.
Q: Are there any concerns when considering AWS RDS or AURORA DB for MySQL HA in the Cloud?
A: Regarding AWS RDS, yes this a good option for MySQL HA in the Cloud. I unfortunately haven’t worked with Aurora DB that much yet so I don’t have an opinion on it’s suitability for HA in the Cloud.
Q: We tried out PXC awhile back and it used to lock everything whenever any ddl was done. Has that changed?
A: We would have to look at the specifics of your environment, however, there have been numerous improvements in the 1½ years of development since Percona XtraDB Cluster went Generally Available (GA) on January 30th, 2014 in version 5.6.15.
Q: Is using the arbitrator a must?
A: No the arbitrator role via the garbd daemon is generally only used when operating in a minimal environment of two nodes that contain the data and you need a third node for quorum but don’t want to store the data a third time.
Q: Can we do a cluster across different zones?
A: Yes you can. However be aware that the latency incurred for all cluster certification operations will be impacted by the round trip time between nodes.
Q: Does PXC also support the MyISAM database?
A: No, Percona XtraDB Cluster does not support any storage engine other than InnoDB as of PXC 5.6.
Q: How do load balancers affect the throughput in a Galera-based setup given that the write would be limited by the slowest node?
A: Load balancers will introduce some measure of additional latency in the form of CPU time in the load balancer layer as it evaluates its own ruleset, and also in network time due to additional hop via load balancer. Otherwise there should be no perceptible difference in the write throughput of a Percona XtraDB Cluster with and without a load balancer as it relates to the “slowest node” factor.
Q: Have you used MaxScale yet? If so, what are your thoughts?
A: Unfortunately I haven’t used MaxScale however Yves Trudeau, Percona Principal Architect, has recently written about MaxScale in this blog post.
Q: How do you configure timeout and maintain persistent connection to HAProxy?
A: I would encourage you to refer to the HAProxy Documentation.