Database Scaling Techniques for Websites: Part 1
Have you every tried to think, what will I do when my website or forum will get a hundred fold more visitors than I currently have?
I myself never had planned such a thing, but it did happen to one of our clients who used our chatting solution FreiChat. Caching was not at all an option as messages had to reach in real time, so the only option was to increase the hardware infrastructure.
So we decided to take the next logical step, i.e., to introduce a new database configured as “slave” to the “master” database. This is because a single database server could not serve all the requests from the ever increasing number of users. This way, we distribute our read and write traffic to separate database hosts.
one master multiple slave
But this too could not hold the load for long, so as usual we increased the number of servers, we used multiple slave servers. One more important thing we implemented was the role for each slave, one slave would be responsible for storing file uploads, another one only for presence related information and some other slave just for messages. This helped us initially and worked exceptionally well for read heavy processes. But we soon realized that this could not continue forever. As we kept on increasing the number of slaves just to keep up with the load, the chat messages started going off sync.
Now the only option we had was to try something unconventional, NoSQL seemed to be the obvious choice. After doing some performance tests we came to learn that the performance of the MySQL server would almost equal that of the NoSQL database if the data was denormalized.
Data stored inside a database is usually normalized, this allows data to be distributed among multiple tables. For example, user info is stored in "user" table while all his messages will be stored in "messages" table. In a denormalized table both user info and messages would be stored in a single table. An example is shown below:
Denormalized Table
This made our queries perform extremely fast as there was no need for a JOIN operation, but the drawback of this technique is redundancy, which means the data is duplicated every time a new record is inserted. This is also meant that we had to update the data in every row, even if there was a small change to the user info. For example, the username if changed had to be updated in every row and every column where the old username was encountered.
Due to this drawback, we again went back to the drawing board in search of a different and effective database scaling technique. We soon came across something called "Sharding". Although the name sounds a bit scary, it basically means fragmenting the table into smaller pieces. There are two types of fragmentation, vertical and horizontal. We opted for horizontal fragmentation as we could segregate old messages and new messages into different tables. An example is shown below:
table fragmentation
This set up allowed us to store old messages into different tables or databases altogether, which allowed the tables with new messages to be smaller and responded a lot quicker. Now our current operational database size is just a few gigs, as soon as the size exceeds a given limit, the tables are then fragmented and all older messages are then sent to another server as they are requested less frequently. This allowed us to scale really well.
Currently we use all three techniques, a master slave setup, a few fields are denormalized for performance and tables are fragmented as they get bigger in size. Do leave your feedback in the comment section and in the next blog, I will cover, how to write scalable code that can work with this multi-server setup.
So we decided to take the next logical step, i.e., to introduce a new database configured as “slave” to the “master” database. This is because a single database server could not serve all the requests from the ever increasing number of users. This way, we distribute our read and write traffic to separate database hosts.
one master multiple slave Denormalized Table table fragmentation
