This post comes as response to 5 Tips for migrating your MySQL server to a Galera Cluster by Henrik Ingo (Galera), which is a good read. This post is a bit lengthy which is one of the reasons why I didn’t just comment on said post.
The Galera talk in London was indeed well crowded, judging from my own chair. I think there is an obvious interest in Galera, as well as a general thirst for information from real-life experience. I am personally very interested in production stories; either “war stories” or “peace stories” – anything to shed more light on the adoption of this technology.
For disclosure, I have not tried Galera myself as yet. Not on production nor on staging machines. But here’s a couple thoughts on Henrik’s post:
Primary keys
I think this is a classic “emerging technology” issue. The technology is designed to work with a specific configuration. In our case: InnoDB tables that all have a PRIMARY KEY. And it makes sense, and it’s good practice anyway, but as it happens people do forget or neglect to create PRIMARY KEYs on their tables.
An emerging technology would want to avoid poor experience (modified values for PK, replication failure) by avoiding the problem in the first place. In my view, Galera should simply refuse to work with InnoDB tables that do not have a PRIMARY KEY. This could work out in several ways:
- Live: cease to replicate (better than replicate wrong info)
- Better yet: refuse to start the server (much as InnoDB will refuse to start if the transaction log size differs from the size indicated in the config file)
- Live & better: refuse to create an InnoDB table without a PRIMARY KEY, or refuse INSERTs into such tables
- Offline: provide with an initial “review_my_database_and_see_if_it_will_work_just_fine_with_galera” shell/Perl/install script.
Number 4 is easiest to begin with, I suppose; is OK for many people to run once, and could detect a variety of problems.
Events and Triggers
I reject the notion of not using triggers because you don’t know how they will behave. Either Galera supports triggers – in which case you should loudly acknowledge that triggers are just fine, or it does not, in which case you should loudly exclaim “this won’t work”.
Triggers have many downsides, performance impact is, for me, the worst of them. However, I don’t understand the question “do you actually know what they will do when you run them in a multi-master cluster? For that matter, do you know what they do on a master-slave cluster?”. Does the question refer to Galera cluster? Well, I don’t really know the math behind Galera, either. Does that mean I should not use Galera? Or does the question relate to standard MySQL replication? In which case – yes, it’s in the docs.
Instructing the users to avoid certain features because “it’s dark out there” is not the right way. [UPDATE: please see Henrik’s comment]
Also
I should also note the very good post by Marco Tusa: MySQL Ndb & MySQL with Galera why we should not compare them. Marco rejects the comparison between NDB Cluster and Galera for many technical reasons. However there is one level of comparison that is not technical: the desire to be able to query any server out of N servers in a group, and to have consistent results, as well as the desire to allow for one server to crash without worrying about immediate actions like failovers, decision making, lost data etc. This level of comparison should not be taken lightly; this is the “what people actually want, without the gory details”.
I’m looking forward to learning more about Galera. While schedule is not completely finalized yet, I think it’s safe to slip the word that you can find some informative talks in the next Percona Live conference in Santa Clara, April (no plug intended though happy to plug).
Hi Shlomi
I must have been unclear. I tried to make it clear that triggers work just fine (as do events). However, my experience with people using triggers (and especially events) is that most people expect them to do something else than they actually do. This experience is already from classic MySQL replication and I have simply continued to advocate the same advice for Galera users: Don’t use features that you don’t understand. (For the same reasons, I discourage most people from using Pacemaker or SANs too…)
In the case of events this is particularly easy to understand: Most people expect an event to fire only once. In reality it will fire on each node. I have also seen attempts to manage this so that an event would fire only on the master, and then if there is a failover your scripts will enable and disable the event on nodes so that it will again be enabled only on the new master. This imo only makes things worse…
Still, it would be wrong to say that events (or triggers) don’t work with MySQL replication, or with Galera, because they work just fine. The problem is just that most people don’t understand what will actually happen if you use them.
Hi Henrik,
That explains it. Your discussion of triggers was unrelated to Galera. Thanks!
I had an experience where triggers to jomla jevents module with autoincrement crashed my 2 node mariadb cluster (10.0)