Main Monthly LA DRUPAL Meetup Dec 11th, 2012 at Coloft + Winebar

If there is interest, I'd love to do a short talk on easy mysql master-master-(master) clustering with Percona XtraDB clustering and how CalArts saved their sites from getting hosed a few weeks ago.

I'm willing to demo (functionality and code) the handful of projects I have launched using Foundation 3 and talk about my experience the good and bad. Or if any is interested in using the Foundation 3 framework come hit me up. We can argue about why it's better than Zen grids, Omega, or Bootstrap :P

Thanks to everyone that put up with a very ad-hoc demo of an XtraDB cluster yesterday. Here is a synopsis:

Resources

• Galera Cluster - This is the actual project that is used by Percona (and is in the current MariaDB 5.5 alpha) for having a multi-master cluster.
• Cluster Deployment Variants - Here are various ways you can deploy a db cluster environment. Each one has their pros and cons. As an FYI, CalArts uses (3a) from that list. Whenever we move over into server deployments via puppet, it'll be easy enough for us to work with on those portions. Our eventual goal is to move into a variant of (3a) and (4). Note I've used letters so you look at the document ;)
• Percona XtraDB Cluster - Documentation about XtraDB Cluster. If you are using a derivative of Debian (like Ubuntu) or RHEL (like CentOS), then you should follow install using their binaries.
• Cluster Recipe - In this recipe, you can learn how to install a 3 server cluster (with a 4th server on which haproxy is installed). If you're using Debian/Ubuntu, replace the yum commands with apt-get. With that said, their my.cnf file does *not* provide additional configuration information that really should be in there (like when binary logs expire, etc). What they provide are *additional* settings you should have. Also, your buffer pool size will change depending on the amount of memory you can dedicate to MySQL on your server(s).
• Drupal and Galera - Sakari Keskitalo and Seppo Jaakola presented on using Drupal and Galera at DrupalCamp Helsinki. Slides are at the link.

Additional questions / notes

• As I did the demo and pulled down a server, we saw haproxy redirect db traffic away from the 1st node in our 3 node cluster to the other 2 nodes. Once the 1st node had mysql running again, it synchronized itself with the 3rd node (effectively pulling it down for that duration) so all traffic went to node #2. Mike Stewart noted that a possible scenario that may occur is that node #2 could have been saturated with traffic during that time and died as well thus running into a bad cycle. For CalArts, the scenario of that occurring was fairly low. Also, what is happening is node 3 is still in sync with 2 during this time - it is just not available to take on traffic. Once 1,3 are synchronized, they will be ready while #2 will not. So you will instead have 2 servers up instead of 1 to take on the traffic spike while #2 recovers. With that said, if you are anticipating huge traffic, it makes sense to spin up more servers to be a part of such a cluster so you are less likely to run into such an issue.
• I'd mentioned that you want to aim for having an odd number of database servers in your cluster (at CalArts, we use 3. If we moved up, we'd go to 5, 7, and so on). The reason you do this (and you would should do this for any other db cluster environment like MySQL Cluster or MongoDB/whatnot) is that you want to avoid Split-Brain, where a cluster could have communication failures, which can cause it to split into subclusters. An odd number of nodes in a cluster will effectively hold a vote on which nodes should get used, and the nodes that split off with data during the communication failures will instead synchronize with the winners of the quorum. See http://www.codership.com/wiki/doku.php?id=info#galera_clustering or http://docs.mongodb.org/manual/administration/replication-architectures/ for more information on the topic.
• If you decide you are going to have an even number of cluster nodes, you can set up one or more arbitrator nodes. Arbitrator nodes are effective nodes that know about the database cluster and can see all the traffic to synchronize the data, but don't actually store it. They are typically lightweight (in the case of galera, it is true). These arbitrator nodes can participate in the quorum to decide the winning db cluster nodes. See http://www.codership.com/wiki/doku.php?id=galera_arbitrator for more information about arbitrators. Also note that the idea of arbitrators is used in various other db systems as well.
• I'd mentioned in my presentation that one of our db servers (node #1) completely died during a scheduled update. Instead of trying to recover it, We decided to spin up a new db server. It took 5 minutes to install xtradb cluster and introduce it to the cluster environment. It then took ~15 minutes to get synchronized with all our databases (nearly 10 gigs in data) and it was serving again (total time - 20 minutes). If you have server snapshots, you could recover it even faster (thank you, Chris Stauffer).
• Chris Stauffer asked is it better to have fewer servers with more RAM or more servers with less RAM (ex 3 servers with 2 gigs of RAM vs 7 servers with 1 gig of RAM). The answer to that is ... it depends. In the case of a galera cluster (or a standard mysql master-slave cluster), you have replicas of your databases on these servers. The data is not sharded. Thus, your entire database is on all of these servers. You should aim to go with the servers that can fit all your database indexes in the available RAM. So a generic, not analyzed answer might be (go with 3 servers with lots of RAM) while a closer analysis of the data might reveal you could go for a lot of servers with less RAM.

Please post back if there are any questions related to the wall of text above. And happy holidays!