Make MySQL clustering work for you

We’ve told you all about MySQL mult-master replication’s limitations. If you write to two masters it is bound to fail for myriad reasons.

Now what? Do what the pros do that’s what.

A. Don’t write to both masters

Using multi-master replication works great as long as you do so in active-passive mode. Never write to two masters at the same time. When you promote a new side to being a master, do it carefully:

o Put application into read-only mode temporarily (disable editing)
o Set current master to read-only mode
o Change all webservers to point to new master
o Change new master to read-write mode
o Turn application edit back on

B. Use Statement based replication

Statement based replication has been around forever, it’s proven and it’s limitations are well known. It’s been tested for many many edge cases. You know what you’re getting.

o supports online schema changes

Perform alter tables, add or drop columns or modify indexes on the inactive master. Once those changes are complete, promote the inactive side to being primary master and perform the changes again on that master. All with zero downtime to your application. Statement based replication makes this easy as differences to columns, column order and so forth won’t break things.

o facilitates point-in-time recovery

With the SQL of all your queries being written directly to binlogs, the forensic process of reconstructing things during point-in-time recovery becomes much easier.

o perform regular checksums against current master

Use the pt-table-checksum tool to verify data. Integrity checking will help you avoid any data drift and keep everything tightly in sync.

C. Degrade gracefully – build for a read-only mode

- facilitates failover
- facilitates maintenance
- insurance plan
- disaster recovery
- levers & dials for the operations team

D. Put Memcache between application and database

- reduces load on database
- reduces latency for remote write master
- key value stores are easier to scale
- continue to get fast application response

E. Misc recommenations

o use provisioned IOPS for the database servers
o use percona server 5.6
o use multi-threaded slaves
o use semi-syncronous replication
o using percona toolkit checksum tool to provide data integrity checks
o using percona toolkit heartbeat to check slave lag
o use percona xtrabackup to do hotbackups
o perform firedrills to restore backups
o perform firedrills to do point-in-time recovery

Like our content? We publish an exclusive monthly Scalable Startups where we share more tips and special content. Here’s a sample

Sales sucks, but then I learned

Are you a developer or startup entrepreneur? Have you ever been frustrated with some of the claims made by the sales team or lacked the patience or ability to communicate across departments?

Join 4000 others and follow Sean Hull on twitter @hullsean.

Just out of college

Just out of college I got a job as a Macintosh Software Developer for a small firm outside of University at Buffalo. It was a ten person company, and half of us were on the technology side of the house. I was doing C++ & Graphical Interface design & coding.

Why is it so hard to find operations & devops talent? Enter the Mythical MySQL DBA!.

Sales is “ahead” of engineering

Besides coding, I also fielded support calls from customers which brought me perspective on both what they wanted, and where they struggled with the software. Our app helped consumers and nutritionists track diet & exercise.

[quote]
The sales team made promises of technology the company wasn’t capable of delivering. Meanwhile the engineering team was sent scrambling to answer to those promises.
[/quote]

Soon I was fielding questions from customers asking when the new heart rate monitoring would be available. I followed up by talking with the team lead & chief architect. He had no plans of building such a feature, nor did we even know how it would be possible!

[mytweetlinks]

Searching for a database expert? MySQL DBA Interview Guide.

We checked in at our weekly meetings, and the CEO explained that the sales team was simply “ahead” of engineering. Years ahead apparently even of the technology that was possible at the time!

Fast forward 5 years to professional services

A half decade later I’m doing independent consultanting for dot-coms. Much of my business came from word of mouth. Helping a firm out of a pinch, speeding their site so they can handle 10x customers on the same servers and suddenly everyone is your friend!

Too many customers is a good problem to have right? For hyper growth companies there are 5 Things Toxic To Scalability .

But all is not smooth sailing in the freelance consulting world. The dot-com crash comes along and budgets are squeezed tighter. Business spend is reduced and every dollar is scrutinized. I learned to speak to prospects about savings and personalized service, advantages of lower overhead, and real return I could provide. At the end of the day if they’re not buying, your services aren’t worth their cost!

[quote]
The sales process should inform the business about what customers really want. In a successful startup there is communication back and forth with engineering and business units so all are working in harmony.
[/quote]

Full Circle

Now coming full circle I have a wide perspective on business. I understand the engineering fundamentals, and the limitations of technology. I also have a grasp of product, and how business units must manage the bottom line, and deliver to customers or else perish in the marketplace.

Looking for a top flight cloud engineer? Grab our Amazon EC2 Interview Guide.

For the two to achieve a happy marriage, you must bring a balance of execution & technical debt, with satisfying a real customer need in the marketplace. And therein lies the innovation & startup sweet spot!

You might also like our piece Why generalists are better at scaling the web

Like our content? We publish an exclusive monthly Scalable Startups where we share more tips and special content. Here’s a sample

Dinner, dollars & devlishly creative thinking

Efficiency at Dinner?

I just finished reading Tyler Cowen’s opus, An Economist Gets Lunch. I have to admit I’m already a fan of his writing, getting a daily dose on from his blog Marginal Revolution.

Join 4000 others and follow Sean Hull on twitter @hullsean.

What I like about this book is that it is unconventional by definition. Further economists like scalability engineers like to think about efficiency. How can I squeeze out more from less? Like the business question how do I get better ROI or more bang for my buck? Questions spring to mind like – What does an economist know about food? Or – What does food eating have to do with economics? Well on both points you’ll get some surprising answers.

Hiring or job seeking? Check out our MySQL DBA Interview questions which is useful to managers, candidates and human resources alike.

To the former question, Cowen has some really good insight because he brings the fresh perspective of an economist. His sort of mantra throughout the book is:

[quote]
Food is a product of economic supply and demand, so try to figure out where the supplies are fresh, the suppliers are creative and the demanders are informed.
[/quote]

Economists & engineers talk shop

What about the second question, how is the food we eat related to economics? Further does it have an impact on environmental and energy consumption questions? As it turns out in a rather big way yes it does. Let Tyler say it in his own words…

[quote]
When it comes to relieving climate change problems, there are two approaches. The first to put it squarely is to have everyone memorize facts about boats & bananas, and update that analysis as often as is necessary. The second approach is to rely on the price system, specifically to modify prices so that they reflect more information about the value of the environment. That’s the economically smart way to address climate change. The first method is wielding a pea shooter and the second is more like a bazooka.
[/quote]

Interested in web speed? Why generalists are better at scaling the web.

What he advocates more specifically is taxing the things we want to reduce. Biggest on the list are fossil fuels he says and next up meat production which through methane emissions contribute to climate change problems. These taxes will naturally curb our use, cause us to take fewer trips, be more efficient with our use, and tighten the wallet naturally.

Applying an economists eye to food & environment yields some excellent insights. For those of us in the startups & internet these fresh takes may well give us some insight in business too. If nothing else it’ll help us find the best meal for dinner!

Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample

No tools to reconcile MySQL with two masters

Here’s the last nail in the coffin. We hope this convinces you not to write to multiple masters with MySQL replication.

Reason 10 – No tools to reconcile inconsistent masters

For all the endless reasons we’ve already outlined MySQL replication is prone to failure. We know it’s going to happen, you now know too.

When replication breaks, your application may still be writing to both sides, both masters. Now you have data differences on both sides, random rows, in random tables.

Percona Toolkit checksum tool is great when you have a single authoritative master. It can compare & show you diffs. But when you’ve changed two masters, you have no authoritative side. Neither one is the last word on your data. You’re then faced with an intractable problem of reconciling your data in some manual or do-it-yourself method.

Not only would such a scenario be hairy and prone to error, but you would effectively have an outage for the duration. All ugly scenarios to be sure.

Click through to the end for multi-master solutions that work with MySQL.

PREV: Reason 9 – Temp tables can break replication

Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample

Temp tables can break replication

If you’re not convinced yet that writing to dual masters is a bad idea, we have a couple more reasons.

9. Temp tables break replication after restart

MySQL’s replication is sensitive to temporary tables. You shouldn’t use them. If your queries create them to work against, and a node crashes, the temp tables will be missing upon restart. You can imagine what this will do to all those queries which expect to see a temp table. That’s right, they’ll fail.

Theoretically you could have a start script which creates and populates such temp tables. However this may add operational complexity if not carefully coordinated with developers & change management.

NEXT: Reason 10 – No tools to reconcile inconsistent masters

PREV: Reason 8 – Crashed nodes can corrupt your cluster

Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample

Crashed nodes corrupt your MySQL cluster

Writing to two masters is like walking around with a loaded shotgun. Eventually one of your instances will fail and when it does, replications position & synchronization information could easily become corrupt!

Reason 8 – Crashed nodes cause big problems

MySQL instances, unfortunately can crash. When that happens, they don’t always sync the replication position properly. That’s a big risk. It’s one of the reasons why operational DBAs know that replication slaves need to be rebuilt from time to time.

If the replication position doesn’t sync properly when MySQL restarts it may do so at the wrong position and replay or miss some transactions. What this would do to an active-active setup is unpredictable. Again no single authoritative master means trouble!

MySQL 5.5 has introduced some parameters to address this. During my tests I’ve seen a performance hit, so use them with caution.

[code]
sync_binlog
sync_relay_log
sync_master_info
sync_relay_log_info
[/code]

NEXT: Reason 9 – Crashed nodes cause big problems

PREV: Reason 7 – Can’t add third node easily

Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample

Transaction isolation breaks when writing two masters

Continuing our discussion of multi-master replication, we hit on five more reasons why writing to two masters aka active-active replication is very dangerous.

Click through to the end for multi-master solutions that work with MySQL.

Reason 6 – You lose transaction isolation

This may sound like a theoretical point to some. But hopefully we can all appreciate that while you’re making changes to your data, you don’t want another user or session with their fingers in the pie, right?

This is implemented in all relational databases, MySQL, Oracle, and SQL Server included as transactions or transactional isolation. While you’re operating on rows they’re locked for other sessions to write to. If they want to do so, they wait in line until you’re done.

In master-master replication, this principal is completely broken. Altough your MySQL instance enforces this on the source side, the second master knows nothing about what you are doing. All of the transactions on that side may do whatever they like to your data.

Let’s give an example:

[code]
mysql> select * from a;
+------+
| c1 |
+------+
| 5 |
+------+
1 row in set (0.00 sec)

masterdb1> set autocommit=0;
masterdb1> update a set c1 = c1 + 5;

masterdb2> set autocommit=0;
masterdb2> update a set c1 = c1 * 5;

masterdb1> commit;
masterdb2> commit;
[/code]

Depending on the order you commit one side will then have 10 as the value, while the other side has 25! Strange indeed.

There’s no way to prevent a myriad of scenarios like this without locking. That’s why relational database introduced shared and exclusive locks, and that’s what’s missing in active-active multi-master replication.

Isolation is big, it’s serious, and without it you’re doomed!

NEXT: Reason 7 – Can’t add third node easily

PREV: Reason 5 – Cannot do integrity checking with active-active

Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample

Active-active replication reduces operational flexibility

Among the myriad data integrity and corruption risks associated with active-active replication, you also lose out on configurability and operational flexibility.

Reason 7 – Can’t add nodes easily

The often touted solution to use auto_increment_increment and auto_increment_offset fixes you to a given setup forever. You can set it up with a discrete two nodes, all primary keys on node1 being even, all on node2 being odd, or three nodes in a similar way.

But once you’ve set that up, adding a new node would require you to renumber every row in every table. A mountainous and painful undertaking.

NEXT: Reason 8 – Crashed nodes cause big problems

PREV: Reason 6 – You lose transaction isolation

Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample

MySQL needs single master to check data integrity

MySQL slaves can drift out of sync. Many of our clients are surprised to find some data differences in their replication topology, once we do some checking and sniffing around. Such checks require a single reliable or authoritative master to compare against.

Click through to the end for multi-master solutions that work with MySQL.

Reason 5 – No way to do integrity checking

MySQL’s statement based replication has a number of problems. It is notoriously bad at handling stored procedures. There are also lots of problems around non-deterministic functions. When you’re lucky it fails with an error. But many environments have replication problems and data inconsistencies that they don’t even know about.

This problem of data drift is well known in MySQL replication. Luckly there is a solution. Formerly part of Maatkit, now redubbed percona toolkit, the table checksum tool attacks this problem head on. pt-table-checksum is a great tool, but won’t help you much when you don’t have a single master.

Which database holds the correct view of all your data?

NEXT: Reason 6 – Active-active breaks transaction isolation

PREV: Reason 4 – Cannot clone & build a slave without a single master

Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample