Nusantara Cloud

Cloud and Software Development Services


Scaling Up MySQL Database

3.21.2018

I love MySQL as it supports transactions, robust, well-tested, easy to set-up, and admittedly because I am familiar with the syntax. However, being a relational database, when it comes to scaling up, it just couldn't do it easily out of the box like NoSQL solutions such as MongoDB which are designed for scalability from its conception. But does that mean SQL cannot be used for building a large-scale system?


Image result for master slave replication

Master-slave replication is probably the easiest way to scale MySQL out, but this could only do so much. With this approach, although read capacity is now larger, all writes would still have to go to a single master, creating a bottleneck. Also, for every single write, the master would have to synchronize with all the slaves, creating another performance impact. And finally, when the data grows very large, a single server simply couldn't contain all the data in there.


Image result for master master replication

Master-master replication is another popular MySQL scaling solution. Unfortunately, there is no official solution for this. One would have to use third-party solution such as Gallera ClusterI have never used this myself, but my research found that this isn't the easiest to set up and has lots of problems. I have heard people complaining about their clusters being out of sync due to some bugs, and when this happens, it's a pain to fix. This also still has some of the bottlenecks and master-slave replication has, such as performance impact when masters have to synchronize writes. 


So do we really have to forget MySQL and all of its goodness (i.e. transaction) when it comes to building large-scale system? Not really. We can use MySQL to a certain degree. We just have to know when we can use it and when we can't. If designed as a service-oriented-architecture a large system would consist of multiple services. Some of these services will be less performance-intense than the other. We just have to figure out which services are suitable for MySQL. MySQL would be nice for services that need transaction and strong consistency level. I'd also think financial-related service deserves to live in MySQL as when it comes to numbers, MySQL should have been very well-tested. It'd be nightmare if there's a bug in a number-sensitive applications caused by a floating point bugs or something in a not-so-well-tested database system.