Everything You Need to Know About Scaling MySQL – Part 5: Supporting MySQL

For many small companies that aren’t expecting to grow much in the coming years, the power in an MySQL databases will suffice to handle their business needs.

But for companies that intend on growing—particularly e-commerce businesses—it’s only a matter of time before your MySQL database hits a wall and your website stops functioning properly. And when that happens, your customers become frustrated and you miss out on sales — both today, and tomorrow if they don’t decide to come back.

To ensure this doesn’t happen, some businesses decide to try and scale their MySQL databases. There’s one problem: Achieving scale isn’t easy. Here’s why:

  • Scaling is a massive undertaking. It’s not difficult to support a regular MySQL instance. But if you want to scale it, i.e., ensure your database is able to handle more and more transactions without a hitch, you’re going to be limited by the size of your ‘write-master’ server. Once that server ‘box’ is the largest available, you have less options left. One option is ‘read slaves’ —separate servers which off-load ‘read-only’ server requests to separate servers running copies of the main database. However, due to replication technology’s asynchronous nature, ACID guarantees go out the window once you decide to take this path, making this a difficult solution in an e-commerce environment.
  • Sharding is a nightmare. ’Read slaves’ don’t scale ‘writes’ to the database. As soon as your customers stop ‘reading’ things and actually want to buy something, your ‘write-master’ is now going to get a workout. So at some point you’re going to have to scale Writes as well, and there’s a limited amount of solutions for that. One known solution is to partition the database across multiple servers through a process called sharding. While sharding is certainly an approach to scaling MySQL, it’s a very labor-intensive and costly one: administrators have to oversee multiple partial databases, and application developers have to constantly make sure the correct queries go to the correct place, and everything stays in sync. In other words, the power of the relational database is lost through sharding, and as a result, database managers have to deal with more latency and more code even in a best-case scenario. And as you’d expect, ACID guarantees go out the window again.
  • Maintaining the database requires humanpower. To successfully scale MySQL, you’ll need to employ knowledgeable database administrators who know the ins and outs of the relational database management system. They’ll have to be well-versed in replication and synchronization, and they’ll have to know how to promote slaves to masters. Additionally, your administrators must be able to handle multiple servers and be able to work cohesively with application architects and developers.

Finally, even if you have all the money in the world, like a Facebook or Google, you’ll still encounter plenty of problems trying to scale MySQL. For example, Facebook spent millions of dollars on advanced automation tools (but the social networking juggernaut could have kept that money in the bank by moving away from MySQL altogether). In a similar vein, Google decided to build a database all on its own—a massive undertaking, which could have been avoided had the search engine giant looked for other modern solutions.

While businesses can certainly move forward with scaling MySQL if they can address these concerns, for many the process is an overly complex and unnecessary undertaking.