Thursday, 11 January 2018

Sping Database Load Balancing

Database pooling can be implemented using Java's Datasource abstraction. Database pooling is not the same as Database connection pooling.
  • Database pooling holds references to multiple database instances.
  • Database connection pooling holds multiple connections to one instance of a DB. 
Database pooling can be used for load balancing purposes. The spring framework makes it even easier to pool databases by providing a Delegating Datasource. Below is how this can be implemented. 


import com.zaxxer.hikari.HikariDataSource;
import org.springframework.jdbc.datasource.DelegatingDataSource;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * Created by Riz
 */
public class RoutingDatasource extends DelegatingDataSource
{
  private DataSourcePool readDatasourcePool  = new DataSourcePool();
  private DataSourcePool writeDatasourcePool = new DataSourcePool();

  /**
  Class holds references to many datasource's which point to the db
  */
  class DataSourcePool{
    AtomicInteger index = new AtomicInteger(0);
    private List<DataSource> dataSources = new ArrayList<>();

    /**
    Called everytime a connection is required, delegates to the underlying datasource,
    round robins through dataSources
    **/
    public DataSource getNextDataSource(){
      return dataSources.get(getNextIndex());
    }

    
    public int getNextIndex(){
      int currentValue, newValue;
      do {
        currentValue = index.get();
        newValue = currentValue % dataSources.size();
      }
      while (index.compareAndSet(currentValue, newValue));
      return newValue;
    }
  }



  /**
   * Constructor taking data sources
   * @param writeDataSources
   * @param readDataSources
   */
  public RoutingDatasource(List<DataSource> writeDataSources, List<DataSource> readDataSources) {
    setTargetDataSource(readDataSources.isEmpty() ? writeDataSources.get(0) : readDataSources.get(0));
    this.readDatasourcePool.dataSources = readDataSources;
    this.writeDatasourcePool.dataSources = writeDataSources;
  }

  /**
   * Constructor uses properties to create hikari datasources
   * uses properties to set up multiple datasources i.edit
   *  db1.datasource.url=jdbc:mysql://localhost:3306/mydb_main
   *  db1.datasource.username=mydb
   *  db1.datasource.password=mypass
   *  
   *
   *  db2.datasource.url=jdbc:mysql://localhost:3306/mydb_replica_1
   *  db2.datasource.username=mydb
   *  db2.datasource.password=mypass
   *  db2.datasource.type=read
   *  
   *  db2.datasource.url=jdbc:mysql://localhost:3306/mydb_replica_2
   *  db2.datasource.username=mydb
   *  db2.datasource.password=mypass
   *  db2.datasource.type=read
   *  
   *  #driver used for databases
   *  db.datasource.driver=com.mysql.jdbc.Driver
   * @param properties
   */
  public RoutingDatasource(Properties properties) {
    String urlKey      = "db%s.datasource.url";
    String usernameKey = "db%s.datasource.username";
    String passwordKey = "db%s.datasource.password";
    String typeKey     = "db%s.datasource.type";
    String driverKey   = "db.datasource.driver";

    for (int i=1; properties.contains(String.format(urlKey, i)); i++){
      String url = properties.getProperty(String.format(urlKey, i));
      String username = properties.getProperty(String.format(usernameKey, i));
      String password = properties.getProperty(String.format(passwordKey, i));
      String type = properties.getProperty(String.format(typeKey, i));

      HikariDataSource ds = new HikariDataSource();
      ds.setJdbcUrl(url);
      ds.setUsername(username);
      ds.setPassword(password);
      ds.setDriverClassName(properties.getProperty(driverKey));


      if(type.equalsIgnoreCase("read")){
        readDatasourcePool.dataSources.add(ds);
      }else{
        writeDatasourcePool.dataSources.add(ds);
      }
    }
  }

  public DataSource getDataSource(){
    if(!readDatasourcePool.dataSources.isEmpty()
        && TransactionSynchronizationManager.isCurrentTransactionReadOnly()){
      return readDatasourcePool.getNextDataSource();
    }
    else {
      return writeDatasourcePool.getNextDataSource();
    }
  }

  public Connection getConnection() throws SQLException
  {
    return getDataSource().getConnection();
  }

}



Using the properties 3 databases can be configured using a properties file:

# Master read/write db
db1.datasource.url=jdbc:mysql://localhost:3306/mydb_main
db1.datasource.username=mydb
db1.datasource.password=mypass

# Read only replica 1
db2.datasource.url=jdbc:mysql://localhost:3306/mydb_replica_1
db2.datasource.username=mydb
db2.datasource.password=mypass
db2.datasource.type=read

# Read only replica 2
db2.datasource.url=jdbc:mysql://localhost:3306/mydb_replica_2
db2.datasource.username=mydb
db2.datasource.password=mypass
db2.datasource.type=read

#driver used for all databases
db.datasource.driver=com.mysql.jdbc.Driver


Spring's Transactional manager can then be used, such that read only transactions are sent to replica's and only writes are done on the master db. This can be achieved by using Spring's @Transaction annotation to tag read only querries methods. 

@Transactional(readOnly = true)




The following dependencies will be required for this to code to work:

<dependency>
    <groupId>hikari-cp</groupId>
    <artifactId>hikari-cp</artifactId>
    <version>2.0.0</version>
</dependency>

<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jpa</artifactId>
    <version>2.0.2.RELEASE</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>

No comments:

Post a Comment