Everything You Need to Know About Scaling MySQL – Part 2: Difficulties

Nearly 80 percent of today’s databases are SQL-based.

If you have an e-commerce website and haven’t done anything to the database that powers it in recent memory, you’re most likely running MySQL, a database which traces its roots back to 1995. After all, the term “NewSQL” didn’t become a part of the digital lexicon until Matt Aslett, an analyst for the 451 Group, coined it in 2011.

Unlike their MySQL counterparts, NewSQL databases provide scalability—while at the same time still ensuring atomicity, consistency, isolation and durability (ACID) in every transaction.

While MySQL is certainly capable of handling a good deal of traffic, as your business continues to grow, your database will probably reach maximum capacity and your website will stop working correctly. That’s because MySQL is not equipped to handle the following demands:

  • Increasing reads and increasing writes. MySQL has capacity limitations; as more and more customers complete transactions on your website, it’s only a matter of time before your database stalls. MySQL can scale reads via ‘read-slaves’, but applications have to be aware that reads aren’t synchronous with the write-master. For example, when a customer updates products in his or her e-commerce cart, it better be read from the write-master. Otherwise, you risk available-to-promise quantities being wrong. Should that happen, you’ll have a bottleneck in the worst possible place: your e-commerce checkout line. That very well could result in abandoned carts, or worse, you’ll sell inventory you don’t have, and have to deal with upset customers, refunds, and negative social media exposure.
  • Increasing availability. To make money, an e-commerce website needs to be up and running—it’s as simple as that. Since MySQL has a single point of failure at the master server, if that server goes down, you will experience downtime. In addition to not being able to buy anything from you, when your website’s not working, there’s a chance your customers will become so frustrated that they’ll take their business elsewhere, permanently.
  • Flexing up and down. MySQL writes aren’t scalable via slaves. So to accommodate increased traffic, many decision makers choose to pay a premium and scale up, buying larger server boxes until they hit the next wall. Other merchants choose a process called sharding, wherein databases are partitioned across multiple servers. While sharding allows databases to handle more capacity, they lose their relational functionality, making it much harder to manage data. For example, once a write-master goes down, businesses need to manually intervene to ensure the database is operating as it should. Additionally, sharded databases are unable to guarantee ACID compliance, a critical requirement for e-commerce transactions. In other words, MySQL can’t flex up and down in a way that benefits e-commerce businesses.

It shouldn’t come as much of a surprise then that a technology that was built two decades ago is struggling to keep up in today’s fast-paced digital world. Think about it: How could programmers in 1995 foresee just how powerful the Internet would actually become?