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. Todd,

    Let me know how it goes, and if you find any issues that need to be addressed! I’m always glad to see folks try this kind functionality, as I think it’s one of the value-add’s MySQL’s JDBC driver brings.

  2. Mathias Bogaert says:

    Exactly what I needed. Didn’t know this was possible, thanks!

  3. Jack Gorden says:

    One question regarding Hibernate … does it do the required con.setReadOnly calls behind the scenes or did you use the raw connection?

    @@ Read/Write @@

    factory.getCurrentSession().beginTransaction();

    // Do some work
    factory.getCurrentSession().save(…);

    factory.getCurrentSession().getTransaction().commit();

    Or:

    boolean readOnly = factory.getCurrentSession().connection().getReadOnly();
    factory.getCurrentSession().connection().setReadOnly(false);
    factory.getCurrentSession().beginTransaction();

    // Do some work
    factory.getCurrentSession().save(…);

    factory.getCurrentSession().getTransaction().commit();
    factory.getCurrentSession().connection().setReadOnly(readOnly);

    @@ Read-Only @@

    factory.getCurrentSession().beginTransaction();

    // Do some work

    factory.getCurrentSession().getTransaction().commit();

    Or:

    boolean readOnly = factory.getCurrentSession().connection().getReadOnly();
    factory.getCurrentSession().connection().setReadOnly(true);
    factory.getCurrentSession().beginTransaction();

    // Do some work

    factory.getCurrentSession().getTransaction().commit();
    factory.getCurrentSession().connection().setReadOnly(readOnly);

  4. Todd Huss says:

    Hibernate will not do it for you so the latter is what you need to do, however, I would abstract all of the transaction management and setting readonly of the connection out as an interceptor.

  5. Andy says:

    hi,
    i am using the Spring transaction framework. this should set readOnly(true) automatically?

    i am right ?

  6. Todd Huss says:

    Hi Andy, last time I checked the Spring source code, they had changed it so that it doesn’t call conn.setReadOnly so I do it myself in my own Spring interceptor. The best way to be sure is to try it yourself with some real code though and see.

  7. Mike says:

    Hi Todd,

    Do you happen to have example code for that interceptor? You’re right, Spring no longer calls setReadOnly on the JDBC connection. The spring docs say that one can add custom advice to the transaction manager, but I can’t seem to find a working example anywhere.

  8. Todd Huss says:

    Mike, I’m no longer at the company where we implemented that but at the time we ended up defaulting everything to readonly. Then if a controller action needed to do something that might involte a write it called a method to switch the hibernate session to use a connection with the master db.

    However, nowadays I would probably just write a spring interceptor such that if the controller action were annotated with @ReadWrite I would call setReadOnly(false).

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

Leave a Reply

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