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.

  1. 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:

    1. 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:

  1. @Aspect
  2. public class ReadOnlyConnectionInterceptor implements Ordered {
  3.  
  4.     private int order;
  5.     private EntityManager entityManager;
  6.  
  7.     public void setOrder(int order) {
  8.         this.order = order;
  9.     }
  10.  
  11.     @Override
  12.     public int getOrder() {
  13.         return order;
  14.     }
  15.  
  16.     @PersistenceContext
  17.     public void setEntityManager(EntityManager entityManager) {
  18.         this.entityManager = entityManager;
  19.     }
  20.  
  21.     @Around("@annotation(readOnlyConnection)")
  22.     public Object proceed(ProceedingJoinPoint pjp, ReadOnlyConnection readOnlyConnection) throws Throwable {
  23.  
  24.         Connection connection = entityManager.unwrap(java.sql.Connection.class);
  25.  
  26.         boolean autoCommit = connection.getAutoCommit();
  27.         boolean readOnly = connection.isReadOnly();
  28.  
  29.         try {
  30.             connection.setAutoCommit(false);
  31.             connection.setReadOnly(true);
  32.  
  33.             return pjp.proceed();
  34.  
  35.         } finally {
  36.             // restore state
  37.             connection.setReadOnly(readOnly);
  38.             connection.setAutoCommit(autoCommit);
  39.         }
  40.     }
  41. }

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:

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

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

  1. @Transactional(readOnly=true)
  2. @ReadOnlyConnection
  3. public Result serviceMethod() {
  4.    …
  5. }

References:

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

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

  1. Kev

    Very helpful, thanks.

  2. Sam Thomas

    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

    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

    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

    @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.


Leave a Reply



 

Back to top