Notes on MySQL, MariaDB, and Galera

This is just a set of random notes on MySQL, MariaDB, Galera, other database related thoughts, and googling results for test databases and performance testing MySQL/MariaDB as I work towards getting a better understanding of my MariaDB galera cluster.

MariaDB Cluster

I’ve got a MariaDB cluster made up (well, as of right this moment) 3 nodes on one network and a garbd on another. This is not the right way to do this, but again, this is just a set of notes. Eventually I’ll have 3 networks (in test) that are meant to represent 3 data centers. Two networks will have 3 nodes on each (for a total of 6) and another network a single garbd server. What I’m aiming for is for any of the DCs to go down and to still have a working cluster in the remaining DC. The nodes are all virtual machines.

I’m a bit behind on my MariaDB version, so I’m still back on 5.5, but that is what is in production right now so that is what I need to test with.

OpenStack Ansible Galera playbooks

I (internally) forked the OpenStack Ansible Galera playbooks some time ago. They are a good way to get a MariaDB Galera cluster up and running quickly. The roles can easily be found on the github site.

Galera-arbitrator (arbiter?)

Galera-arbitrator (garb or garbd) is a useful service that can help a Galera cluster with maintaining quorum, but doesn’t take up as many resources to run it as it would a full-fledged database server. Usually people who only have two good database servers use garbd on a lower-end server to help with quorum because you shouldn’t have a cluster of two nodes or you’ll end up in split-brain, and split-brain is as bad as it sounds. So if you have MariaDB + Galera on two good servers and garbd on a third (less good) server, then you should be able to avoid split-brain.

In my case I have two datacenters with multiple galera nodes in a large cluster, and I want a garbd running in a third datacenter so that if I lose an entire DC, or the interconnect between them, I don’t end up in split-brain at the datacenter level.

MySQL Procedures

While I was looking for test databases, I stumbled on this stackoverflow post that had an example prepared statement in it. I figured why not give it a try, I’d never used a prepared statement in MySQL before. Another technology to look into…

sysbench

I put up a quick Ansible playbook that installs the lastest sysbench here. Currently that is version 0.5. Apparently 0.5 adds the ability to use lua scripts, and in fact comes with some example scripts which I use below.

Following this post I setup and ran tests using the below commands (where all the right databases and users and permissions and such were put into place).

For some kind of comparison, good or bad, here’s the same test run on a single instance of the default mysql server you get when you install it on Ubuntu trusty. Same instance type as the above tests were run on.

More work to do

So, like I said, these are just a bunch of notes I took when messing around with a virtual Galera cluster and doing some basic research into performance testing. I’ll update this post as I continue on. Now that I have a virtualized test cluster that I can destroy and rebuild at will I can really get into understanding how it works and what the failure domains are, as well as how it performs. Eventually I would like to get MariaDB MaxScale into the loop as well, and send writes to one host and reads to all.