MySQL Scaling

1. Overview

By default, the MySQL can be scaled either using Vertical or Hybrid approaches but not fully Horizontal approach.

1.1 What is Scaling, Why and When to use

In simple words, Scaling is making your database handle more traffic or load for reading and write queries

2. Master-Slave approach

Generally, we tend to create a Master-Slave architecture and route all the write queries on the Master instance and all the read queries on the slave instance which are replicated from the Master. We can have multiple Slave instances running at one and scale our read operations horizontally. But the Master can only be scale Vertically.

2.1 Problems with the Master-Slave approach

 

In order to solve the duplicate ID issue, we can have one ID generator which generates the unique IDs and we can use the same ID as our primary key, but this will add one extra point of failure in the system and also very prone to fail in concurrency. If your ID generator is down or slow, the whole database will fail or become slow.

2.2 Solutions and approaches

A best possible approach to solve the multiple master issue in MySQL with almost 0 tradeoffs in terms of extra time was focused to solve the below issues

1. Multiple masters in MySQL to scale my write operations horizontally
2. Handle concurrent requests with no duplicate IDs
3. No extra waiting time added to the write operations

2.2.1 ID Generator

To solve the duplicate ID issue in multiple master architecture I have used one ID generator server which generates the unique time-based serial hashes which can be used as a primary key in MySQL table. The benefit of having time-based serial hashes is the time relation between to two IDs are possible to measure.

2.2.2 Concurrency

To solve the concurrency issue have used a pool of IDs stored in a cache generated by ID Generator is an Asynchronous manner. One a written request comes to the master it takes one ID from the cache and inserts the same as a primary key.

I have a small cache on every master instance stored almost 1,000,000 IDs to be consumed by the master, If the IDs stored in the cache reaches to a certain limit it makes an Asynchronous request to fill up the cache with new IDs.

2.2.3 Approximately 0 extra time

As these requests to the ID generator are Asynchronous so there is no Extra time tradeoff on the write requests except the time taken in IO within the same server cache which was negligible. 

3. Reference

https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-scaleout.html

https://medium.com/@avishwakarma/mysql-scaling-and-how-to-do-it-d7f5b8dbd5b9