Database Server Load Balancing — Using MySql Proxy Server


Load Balancing for database server is actually to distribute the load to multiple database servers.

Question arises that When and Why we need this?

Just suppose, you have a database based application. You will start by deploying your web application (or any other using databases) with a single database server. And fortunately your application attracts people and number of users get a massive increase. Here you will feel that the performance of your application is not as satisfactory as it was in the early days of your application.

Now what will be the solutions. Although there are many smart solutions that my increase the efficiency of your application like optimizing code, optimizing queries, optimizing database server settings, optimizing web server settings. etc. All these mentioned solutions means a lot and need to be individually discussed. But let suppose you got through all of them and still not satisfied with the performance of your application.

Here the need arises to have another database server to be deployed. Now you will have to manage your data and traffic to both servers.

Firstly, the concept of replication is used. You replicate all of your data from the existing server to the new server. In this case the existing server will be called master and the server that has the replica of data will be called slave.

In order to implement replication first you will have to enable binary logs at master. And direct the slave to master explicitly in order to communicate with. Then the slave will always have a process alive in order to receive updates from the binary log of master. All the changes made to master are logged in binary log and the slave will receive these changes and make on its database. Replication has an abstract information over here and a detailed post will be made on later on stages.

Another thing to be discussed at this stage is the type of traffic that is, it may be read requests and write requests. Now you need to direct all of you writes to the master as it actually makes the changes to database and reads may be distributed among both of the servers.

Here MySql Proxy Server comes in action, it stands between the web application and database servers and distribute all the traffic to database servers as per requirement. In its settings you will mention the master server and slaves as well. It will automatically redirect all of your writes to master. It actually follows round robin technique to deal with traffic. A detailed implementation will be made by a later post.

All this information is disused in very abstract manner the actual theme to share this is to mention a way out of load balancing there may be others as well, if you people have some other solution, please bring it up. This blog post is open for discussion.

1 thought on “Database Server Load Balancing — Using MySql Proxy Server”

Leave a Reply

Your email address will not be published. Required fields are marked *