How I Learned to Stop Worrying and Love Automated Database Failover
braintreepayments.com
braintreepayments.com
This is one of the most important sentences in the article. I've seen too many systems in my time fail because the wonderful recover/failover system has never really been tested in anger, or the person who set it up left the company and the details never quite made it into the pool of common knowledge. Dealing with failover situations has to become normal.
One of the nicest piece of advice I got, many years ago, was naming. Never name systems things like 'db-primary' and 'db-failover' or 'db-alpha' and 'db-beta' - nothing that has an explicit hierarchy or ordering. Name them something random like db-bob and db-mary, or db-pink and db-yellow instead. It helps break the mental habit of thinking that one system should be the primary, rather than one systems just happens to be the primary right now.
Once you do that start picking a day each week to run the failover process on something. Like code integration - do it often enough and it stops being painful and scary.
(Geek note: In the late nineties I worked briefly with a D&D fanatic ops team lead. He threw a D100 when he came in every morning. Anything >90 he picked a random machine to failover 'politely'. If he threw a 100 he went to the machine room and switched something off or unplugged something. A human chaos monkey).
They just fail sometimes. The ability to be back up and running before an admin can even respond will pay for itself after your first automated failover (which doesn't even address the fact that automated failover scales well - human based failover doesn't).
I also like their modifications to the Pacemaker resource to not flap the master role - that's really important with databases, and often overlooked with Pacemaker.
Does anyone have any experience with Galera in a production environment? Is the setup in this article preferable to that?
On the other hand, your overall throughput is constrained by the network, since all commits must at least ping all of the nodes.
Which setup are you referring to? MySQL 5.5, or MySQL NDB cluster?
[1]http://www.mysqlperformanceblog.com/2012/08/17/percona-xtrad...
I was initially set on using NDB cluster to provide failover, but the table constraints (tables can't be too wide) means that there would have to be code changes. I recently set up and tried Galera, and everything "just worked". The application currently uses standard MySQL 5 with Innodb, and going with Galera would mean that no code change would be necessary.
It just sounded too good to be true though, so I just wanted to know that people actually use this in production. The application handles payments, so corrupted or rows not committing on all nodes would be a huge problem.