Load balancing across MySQL servers using JDBC

In our production environment at GreatSchools we have 3 production MySQL database servers: 1 read-write master and 2 read-only slaves. In moving our site from Perl to Java we need to load balance read-only connections across the read-only slave servers so that our read-write master doesn’t get overwhelmed under heavy load. As our systems administrators discovered there’s a solution to load balancing read-only connection in the MySQL JDBC driver!

For the JDBC driver you’ll need to use the

com.mysql.jdbc.ReplicationDriver

for the connection string, set a property

roundRobinLoadBalance

to true, and use

 jdbc:mysql://master,slave1,slave2/dbname

as the connection string. Once you have a connection that you’ll be using for read-only purposes do a

conn.setReadOnly(true)

and you’re off and running!

I’ve also started using this approach in conjunction with Spring/Hibernate and so far it looks like it will work fine. We’re going to be doing some more extensive testing and load testing of this feature before we put it into production though!

This entry was posted in Database, Java. Bookmark the permalink.

9 Responses to Load balancing across MySQL servers using JDBC

  1. Pingback: MySQL Master/Slave Load Balancing with JPA and Spring — Dragisa Krsmanovic