MySQL Master/Slave Load Balancing with JPA and Spring

MySQL Connector/J driver has built-in feature for load balancing.

If you have a cluster of read/write MySQL servers. Putting loadbalance: in the JDBC URL will ensure both read and write operations are distributed across servers.
jdbc:mysql:loadbalance://master1,master2,master3…/database?loadBalanceBlacklistTimeout=5000&loadBalanceConnectionGroup=cgroup&loadBalanceEnableJMX=true&autoReconnect=true&autoReconnectForPools=true

What we needed is all write operations to go to master server and read-only operations to be equally distributed among multiple read-only slaves.

For that you need to:

  1. Use special JDBC driver: com.mysql.jdbc.ReplicationDriver
  2. Set replication: in the URL:
    jdbc:mysql:replication://master,slave1,slave2…/database?loadBalanceBlacklistTimeout=5000&loadBalanceConnectionGroup=ugc&loadBalanceEnableJMX=true&autoReconnect=true&autoReconnectForPools=true

After setting our connection pool like this, all load still ended up going to our single read/write master server.

The reason is that, for the ReplicationDriver to know that queries can go to read-only slaves, two conditions need to be met:

  1. Auto commit needs to be turned off. (*)
  2. Connection needs to be set to read-only.

(*)There is a workaround to allow auto commit: Connector/J load-balancing for auto-commit-enabled deployments

Turns out, even if transaction is set to read-only, neither Spring nor JPA providers like Hibernate or EclipseLink will set JDBC connection to readOnly.

To ensure JDBC Connection is set to read-only, I created an annotation and a simple AOP interceptor.

Here is an example code:

@Aspect
public class ReadOnlyConnectionInterceptor implements Ordered {

    private int order;
    private EntityManager entityManager;

    public void setOrder(int order) {
        this.order = order;
    }

    @Override
    public int getOrder() {
        return order;
    }

    @PersistenceContext
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Around("@annotation(readOnlyConnection)")
    public Object proceed(ProceedingJoinPoint pjp, ReadOnlyConnection readOnlyConnection) throws Throwable {

        Connection connection = entityManager.unwrap(java.sql.Connection.class);

        boolean autoCommit = connection.getAutoCommit();
        boolean readOnly = connection.isReadOnly();

        try {
            connection.setAutoCommit(false);
            connection.setReadOnly(true);

            return pjp.proceed();

        } finally {
            // restore state
            connection.setReadOnly(readOnly);
            connection.setAutoCommit(autoCommit);
        }
    }
}



One important thing is to set priority of this interceptor to be lower than the Spring’s transaction interceptor. That way we want to make sure transaction is created before we start modifying the connection. In other words, you would set order for @Transactional to be less than order for your new interceptor:


<tx:annotation-driven order="10">
    <bean id="readOnlyInterceptor" class="com.myproject.ReadOnlyConnectionInterceptor">
        <property name="order" value="20"></property>
    </bean>
</tx:annotation-driven>



Now, if you want queries to execute on your pool of slave databases, you can just set an annotation on the service method:


@Transactional(readOnly=true)
@ReadOnlyConnection
public Result serviceMethod(...) {
    ...
}



References:

Category: Software | Tags: , , , , , , 10 comments »

10 Responses to “MySQL Master/Slave Load Balancing with JPA and Spring”

  1. Kev
    November 4th, 2011 at 3:59 am

    Very helpful, thanks.
  2. Sam Thomas
    July 9th, 2012 at 4:05 pm

    Thank you for this. I’m trying to get this working for a non-JPA usecase and I can’t figure out how to get the connection using SessionFactory instead of EntityManager. Would you be willing to provide an example of the Aspect for that use case?
  3. Dragisa Krsmanovic
    July 12th, 2012 at 5:19 pm

    Another way to do this is via connection pool hooks.

    In your hook code, you can always check if you are inside a read-only or r/w transaction using Spring TransactionSynchronizationManager

    I think this is a better approach then the one I initially suggested in my article.
  4. dustincg
    November 20th, 2012 at 6:19 am

    Dragisa, have you tried using the connection hooks? I tried it using BoneCP and the TransactionSynchronizationManager, however it doesn’t seem to work. Changing the connection to read only in onBeforeStatementExecute() doesn’t error, and the logical connection looks correct, but it also still reads from the master. If you could get the transaction information in checkOut() it would work, but the transaction isn’t active at that point.
  5. Dragisa Krsmanovic
    November 25th, 2012 at 1:08 am

    @dustincg It works fine with c3p0. I don’t see why it shouldn’t work the same way with BoneCP.

    It’s strange that you are seeing connection being checked out from pool before the Spring transaction has started. Spring transaction context is available inside a @Transactional method.
  6. Shridutt
    March 22nd, 2016 at 8:59 am

    How to do MySQL Master/Slave Load Balancing with SpringBoot??
  7. Piotr
    March 8th, 2017 at 1:17 pm

    Any better solutions to this problem? It seems to still be a problem with spring 4.3 and hibernate 4.3.
  8. Gyuseo Shim
    April 3rd, 2018 at 7:55 pm

    This article has helped a lot. thank you.
  9. davlee
    April 4th, 2019 at 6:11 pm

    why can not see artical?
  10. Spring Boot Mysql Replication | Mysql Replication 답을 믿으세요
    September 25th, 2022 at 12:26 pm

    […] + 여기에 보기 […]

Leave a Reply



 

↑ Back to top