MySQL High Availability, 2nd Edition

Foreword for the Second Edition

Yashwanth Nelapati

Pinterest

Marty Weiner

Pinterest

In 2011, Pinterest started growing. Some say we grew faster than any other startup to date. In the earliest days, we were up against a new scalability bottleneck every day that could slow down the site or bring it down altogether. We remember having our laptops with us everywhere. We slept with them, we ate with them, we went on vacation with them. We even named them. We have the sound of the SMS outage alerts imprinted in our brains.

When the infrastructure is constantly being pushed to its limits, you can’t help but wish for an easy way out. During our growth, we tried no less than five well-known database technologies that claimed to solve all our problems, but each failed catastrophically. Except MySQL. The time came around September 2011 to throw all the cards in the air and let them resettle. We re-architected everything around MySQL, Memcache, and Redis with just three engineers.

MySQL? Why MySQL? We laid out our biggest concerns with any technology and started asking the same questions for each. Here’s how MySQL shaped up:

• Does it address our storage needs? Yes, we needed mappings, indexes, sorting, and blob storage, all available in MySQL.

• Is it commonly used? Can you hire somebody for it? MySQL is one of the most common database choices in production today. It’s so easy to hire people who have used MySQL that we could walk outside in Palo Alto and yell out for a MySQL engineer and a few would come up. Not kidding.

• Is the community active? Very active. There are great books available and a strong online community.

• How robust is it to failure? Very robust! We’ve never lost any data even in the most dire of situations.

• How well does it scale? By itself, it does not scale beyond a single box. We’d need a sharding solution layered on top. (That’s a whole other discussion!)

• Will you be the biggest user? Nope, not by far. Bigger users included Facebook, Twitter, and Google. You don’t want to be the biggest user of a technology if you can help it. If you are, you’ll trip over new scalability problems that nobody has had a chance to debug yet.

• How mature is it? Maturity became the real differentiator. Maturity to us is a measure of the blood, sweat, and tears that have gone into a program divided by its complexity. MySQL is reasonably complex, but not nearly so compared to some of the magic autoclustering NoSQL solutions available. Additionally, MySQL has had 28 years of the best and the brightest contributing back to it from such companies as Facebook and Google, who use it at massive scale. Of all the technologies we looked at, by our definition of maturity, MySQL was a clear choice.

• Does it have good debugging tools? As a product matures, you naturally get great debugging and profiling tools since people are more likely to have been in a similar sticky situation. You’ll find yourself in trouble at 3 A.M. (multiple times). Being able to root cause an issue and get back to bed is better than rewriting for another technology by 6 A.M.

Based on our survey of 10 or so database technologies, MySQL was the clear choice. MySQL is great, but it kinda drops you off at your destination with no baggage and you have to fend for yourself. It works very well and you can connect to it, but as soon as you start using it and scaling, the questions starting flying:

• My query is running slow, now what?

• Should I enable compression? How do I do it?

• What are ways of scaling beyond one box?

• How do I get replication working? How about master-master replication?

• REPLICATION STOPPED! NOW WHAT?!

• What are options for durability (fsync speeds)?

• How big should my buffers be?

• There are a billion fields in mysql.ini. What are they? What should they be set to?

• I just accidentally wrote to my slave! How do I prevent that from happening again?

• How do I prevent running an UPDATE with no where clause?

• What debugging and profiling tools should I be using?

• Should I use InnoDB, MyISAM, or one of several other flavors of storage engine?

The online community is helpful for answering specific questions, finding examples, bug fixes, and workarounds, but often lacks a strong cohesive story, and deeper discussions about architecture are few and far between. We knew how to use MySQL at small scale, but this scale and pace were insane. High Availability MySQL provided insights that allowed us to squeeze more out of MySQL.

One new feature in MySQL 5.6, Global Transaction Handlers, adds a unique identifier to every transaction in a replication tree. This new feature makes failover and slave promotion far easier. We’ve been waiting for this for a long time and it’s well covered in this new edition.

During our grand re-architecture to a sharded solution, we referred to this book for architectural decisions, such as replication techniques and topologies, data sharding alternatives, monitoring options, tuning, and concerns in the cloud. It gave us a deeper understanding of how MySQL works underneath the hood, which allowed us to make better informed choices around the high level queries, access patterns, and structures we’d be using, as well as iterate on our design afterward. The resulting MySQL architecture still serves Pinterest’s core data needs today.