Scaling out and creating fault tolerant systems with MySQL replication

A few days back, reading the article on the Moore's law, I thought it is not too difficult to extend this for an analogous situation, e.g. the amount of data being cached by a search engine like Google also grows exponentially. With changing times we need to have solution that would adapt to the changing data requirements and availability solutions like virtual infinite scale-out like cloud. Even though virtualization is not exactly a database concept, it's  not long when we will have it as a standard for RDBMS. This article is however  about using MySQL replication to scale out reads and make it fault-tolerant.

MySQL replication is essentially asynchronous  i.e. it doesnot guarantee zero latency between the master and the slave(s).  This, by extension means that in case of a master crash, data may be lost if we decide to promote one of the slave to be the master and that is why while scaling out reads we need to take care of this.  A general design for master-slave(s) configuration with reads scaled out will be like this.

MySQL Replication  design: read off-loading   

In the above diagram the application connects to the web clients connected to the replication setup. The web clients segregate the queries into "read" or "write".  This enables the web-clients to redirect the reads to the slave and the writes to the master, effectively off-loading the master. This improves the performance and enables read-intensive tasks like reporting and consolidation application to run without hampering the performance of the master. A problem can however happen if the master has a lot of writes happening there-by causing the slaves to be loaded as well.

Fault Tolerant Systems: One of the basic requirement of a modern-day system is fault tolerance. Master and slave(s) system is capable of tolerating faults caused due to failures on the master. This, however requires the DBA (or an external program) to monitor any such failures and promoting the (most up-to-date) slave to take up the role of the master and changing the topology by re-configuring the slaves to take updates from the new master.

An effective way of monitoring and managing the  clients is to create a layer that monitors the topology and send out the read an write queries to the master-slave(s) system.

 Virtual layer for interaction between the RDBMS and  the  Application  

Essentially this layer will have three modules, the load-balancer, the connection pool manager and the topology monitor. As the name suggests load balancer balances the read loads on various slaves, the connection pool manager holds the records of connection to various servers, and the topology monitor monitors the systems and does an automatic failover sequence (as described earlier) to restore the system to a working condition with no or minimal loss of data. 

Read On

use test; Select "Hello World" from `Universe`

Hello Folks,

I have never been consistent with blogging and most of the time
I create a blog hoping that I would  share my thoughts and plans
with everyone, but as It has to happen, I neglect it.

On this new year I made a resolution that I will work on this blog
(actually it was to create one) and maintain it throughout. This
blog will be the reflection of my ideas, and an attempt to get a feedback on
my work in MySQL replication, together with providing tips and tricks
on using MySQL in distributed environment.

So, last July I joined MySQL replication development team, straight out of
college and it is kind of a big thing for me. In past 7 odd months I have
worked with great people and learnt a lot of things and this has finally lead
to an idea of starting this blog, and posting my thoughts in my spare time.

This blog will particularly be useful for people who work with MySQL be it
on the internals or on using MySQL in distributed systems. I guess it is
too early to tell what one can expect from this blog, but I will try to
keep this interesting and the posts... ummm informative.

Read On