Hologres:JDBC-based load balancing

Background information

In Hologres, a primary instance can be associated with multiple read-only secondary instances. The primary and secondary instances share storage resources but do not share computing resources. This allows you to deploy high-availability instances based on read/write splitting. For more information, see Configure multi-instance high-availability deployment.

You can use JDBC to associate multiple read-only secondary instances with a primary instance to implement simple load balancing, as shown in the following figure.

Hologres provides the following features related to read-only secondary instances that are configured by using JDBC:

• Query requests are randomly allocated to read-only secondary instances. This prevents a secondary instance from being overloaded.

• Query requests are allocated to secondary instances in sequence to ensure that services are available in case of instance failures.

  • If JDBC fails to connect to Secondary Instance 1, JDBC automatically attempts to connect to Secondary Instance 2.

  • If JDBC fails to connect to Secondary Instances 1 and 2, JDBC automatically attempts to connect to Secondary Instance 3.

  • If JDBC fails to connect to all secondary instances, the system displays a connection failure message.

In Hologres V2.0.10 and later, more values are available for the targetServerType parameter. This way, you can implement load balancing in more scenarios.

Usage notes

jdbc:postgresql://<Endpoint1>:<Port1>,<Endpoint2>:<Port2>,<Endpoint3>:<Port3>.../<DBNAME>?user=<AccessKey ID>&password=<AccessKey Secret>&targetServerType=any&loadBalanceHosts=<value>[&hostRecheckSeconds=<value>]

jdbc:postgresql://{ENDPOINT1}:{PORT1},{ENDPOINT2}:{PORT2},{ENDPOINT3}:{PORT3}.../{DBNAME}?targetServerType=any&loadBalanceHosts=true&hostRecheckSeconds=30

Examples

• Query requests are randomly allocated to three read-only secondary instances. If a connection fails, JDBC automatically attempts to connect to another instance.

  import java.sql.*;
  import java.util.HashMap;
  import java.util.Map;
  import java.util.Properties;
  
  public class hatest {
      public static void main(String[] args) {
          //Configure the endpoint of Hologres Read-only Secondary Instance 1.
          String endpoint1 = "hgpostcn-cn-wxxxxxxxx01-cn-shanghai.hologres.aliyuncs.com:80";
          //Configure the endpoint of Hologres Read-only Secondary Instance 2.
          String endpoint2 = "hgpostcn-cn-wxxxxxxxx02-cn-shanghai.hologres.aliyuncs.com:80";
          //Configure the endpoint of Hologres Read-only Secondary Instance 3.
          String endpoint3 = "hgpostcn-cn-wxxxxxxxx03-cn-shanghai.hologres.aliyuncs.com:80";      
          //Specify the name of the database to which you want to connect.
          String dbname = "postgres";
          String jdbcUrl = "jdbc:postgresql://" + endpoint1 + "," + endpoint2 + "," + endpoint3 + "/" + dbname;
          Properties properties = new Properties();
          //Specify the AccessKey ID of the account that is used to connect to the database.
          properties.setProperty("user", "xxxx");
          //Specify the AccessKey secret of the account that is used to connect to the database.
          properties.setProperty("password", "xxxx");
          // Set targetServerType to any. This allows JDBC to connect to any instance whose endpoint is specified in the URL.
          properties.setProperty("targetServerType", "any");
          //Set loadBalanceHosts to true to enable load balancing.
          properties.setProperty("loadBalanceHosts", "true");
          // Set hostRecheckSeconds to 10. Unit: seconds.
          properties.setProperty("hostRecheckSeconds", "10");
          try {
              Class.forName("org.postgresql.Driver");
              Connection connection = DriverManager.getConnection(jdbcUrl, properties);
              PreparedStatement preparedStatement = connection.prepareStatement("show hg_frontend_endpoints;" );
              ResultSet resultSet = preparedStatement.executeQuery();
              while (resultSet.next()) {
                  ResultSetMetaData rsmd = resultSet.getMetaData();
                  int columnCount = rsmd.getColumnCount();
                  Map map = new HashMap();
                  for (int i = 0; i < columnCount; i++) {
                      map.put(rsmd.getColumnName(i + 1).toLowerCase(), resultSet.getObject(i + 1));
                  }
                  System.out.println(map);
              }
          } catch (Exception exception) {
              exception.printStackTrace();
          }
      }
  }

• Queries are polled for 100 times and are allocated to two instances.

  import java.sql.*;
  import java.util.HashMap;
  import java.util.Map;
  import java.util.Properties;
  
  public class hatest {
      public static void main(String[] args) {
          int x = 1;
          while( x <= 100 ){
              // Configure the endpoint of the Hologres primary instance.
              String endpoint1 = "hgpostcn-cn-wxxxxxxxx04-cn-hangzhou.hologres.aliyuncs.com:80";
              // Configure the endpoint of the Hologres read-only secondary instance.
              String endpoint2 = "hgpostcn-cn-wxxxxxxxx05-cn-hangzhou.hologres.aliyuncs.com:80";
              // Specify the name of the database to which you want to connect.
              String dbname = "postgres";
              String jdbcUrl = "jdbc:postgresql://" + endpoint1 + "," + endpoint2 + "/" + dbname ;
              Properties properties = new Properties();
              //Specify the AccessKey ID of the account that is used to connect to the database.
              properties.setProperty("user", "xxx");
              //Specify the AccessKey secret of the account that is used to connect to the database.
              properties.setProperty("password", "xxx");
              // Set targetServerType to any to allow JDBC to connect to any instance whose endpoint is specified in the URL.
              properties.setProperty("targetServerType", "any");
              //Set loadBalanceHosts to true to enable load balancing.
              properties.setProperty("loadBalanceHosts", "true");
              // Set hostRecheckSeconds to 10. Unit: seconds.
              properties.setProperty("hostRecheckSeconds", "10");
              try {
                  Class.forName("org.postgresql.Driver");
                  Connection connection = DriverManager.getConnection(jdbcUrl, properties);
                  PreparedStatement preparedStatement = connection.prepareStatement("show hg_frontend_endpoints;" );
                  ResultSet resultSet = preparedStatement.executeQuery();
                  while (resultSet.next()) {
                      ResultSetMetaData rsmd = resultSet.getMetaData();
                      int columnCount = rsmd.getColumnCount();
                      Map map = new HashMap();
                      for (int i = 0; i < columnCount; i++) {
                          map.put(rsmd.getColumnName(i + 1).toLowerCase(), resultSet.getObject(i + 1));
                      }
                      System.out.println(map);
                  }
              } catch (Exception exception) {
                  exception.printStackTrace();
              }
              x++;
          }
      }
  }

  Metric data of the two instances indicates that the numbers of connections of the two instances are basically the same. For more information about how to view metric data of the instances, see View metrics.

  • Metrics of the instance hgpostcn-cn-wxxxxxxxx04

  • Metrics of the instance hgpostcn-cn-wxxxxxxxx05