{"id":307,"date":"2011-06-13T13:25:00","date_gmt":"2011-06-13T20:25:00","guid":{"rendered":"http:\/\/www.dragishak.com\/?p=307"},"modified":"2020-08-28T13:13:44","modified_gmt":"2020-08-28T20:13:44","slug":"mysql-masterslave-load-balancing-with-jpa-and-spring","status":"publish","type":"post","link":"https:\/\/www.dragishak.com\/?p=307","title":{"rendered":"MySQL Master\/Slave Load Balancing with JPA and Spring"},"content":{"rendered":"

MySQL Connector\/J<\/a> driver has built-in feature for load balancing.<\/p>\n

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

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.<\/p>\n

For that you need to:<\/p>\n

    \n
  1. Use special JDBC driver: com.mysql.jdbc.ReplicationDriver<\/b><\/code><\/li>\n
  2. Set replication:<\/b><\/code> in the URL:
    \njdbc:mysql:replication:\/\/master,slave1,slave2…\/database?loadBalanceBlacklistTimeout=5000&loadBalanceConnectionGroup=ugc&loadBalanceEnableJMX=true&autoReconnect=true&autoReconnectForPools=true<\/strong><\/li>\n<\/ol>\n

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

    The reason is that, for the ReplicationDriver to know that queries can go to read-only slaves, two conditions need to be met:<\/p>\n

      \n
    1. Auto commit needs to be turned off. (*)<\/li>\n
    2. Connection needs to be set to read-only.<\/li>\n<\/ol>\n

      (*)<\/sup>There is a workaround to allow auto commit: Connector\/J load-balancing for auto-commit-enabled deployments<\/a>
      \n<\/i><\/p>\n

      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.<\/p>\n

      To ensure JDBC Connection is set to read-only, I created an annotation and a simple AOP interceptor.<\/p>\n

      Here is an example code:<\/p>\n

      \n@Aspect\npublic class ReadOnlyConnectionInterceptor implements Ordered {\n\n    private int order;\n    private EntityManager entityManager;\n\n    public void setOrder(int order) {\n        this.order = order;\n    }\n\n    @Override\n    public int getOrder() {\n        return order;\n    }\n\n    @PersistenceContext\n    public void setEntityManager(EntityManager entityManager) {\n        this.entityManager = entityManager;\n    }\n\n    @Around("@annotation(readOnlyConnection)")\n    public Object proceed(ProceedingJoinPoint pjp, ReadOnlyConnection readOnlyConnection) throws Throwable {\n\n        Connection connection = entityManager.unwrap(java.sql.Connection.class);\n\n        boolean autoCommit = connection.getAutoCommit();\n        boolean readOnly = connection.isReadOnly();\n\n        try {\n            connection.setAutoCommit(false);\n            connection.setReadOnly(true);\n\n            return pjp.proceed();\n\n        } finally {\n            \/\/ restore state\n            connection.setReadOnly(readOnly);\n            connection.setAutoCommit(autoCommit);\n        }\n    }\n}\n<\/pre>\n
      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:<\/p>\n
      \n<tx:annotation-driven order="10">\n    <bean id="readOnlyInterceptor" class="com.myproject.ReadOnlyConnectionInterceptor">\n        <property name="order" value="20"><\/property>\n    <\/bean>\n<\/tx:annotation-driven>\n<\/pre>\n
      Now, if you want queries to execute on your pool of slave databases, you can just set an annotation on the service method:<\/p>\n
      \n@Transactional(readOnly=true)\n@ReadOnlyConnection\npublic Result serviceMethod(...) {\n    ...\n}\n<\/pre>\n
      References:<\/p>\n