All Products
Search
Document Center

Hologres:JDBC-based load balancing

Last Updated:Jun 24, 2024

In Hologres V1.3 and later, you can use Java Database Connectivity (JDBC) to configure multiple read-only secondary instances to implement simple load balancing. This topic describes how to use JDBC to implement 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.image..png

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

Prerequisites

Syntax

If you want to configure multiple read-only secondary instances, you need to configure the Endpoint:Port pairs of the instances in the JDBC URL. Separate the Endpoint:Port pairs with commas (,).

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

Parameters

Parameter

Description

Endpoint

The endpoint of the Hologres instance.

You can obtain the endpoint of the Hologres instance on the instance details page in the Hologres console.

Port

The port number of the Hologres instance.

You can obtain the port number of the Hologres instance on the Instance Details page in the Hologres console.

DBNAME

The name of the Hologres database.

AccessKey ID

The AccessKey ID of the Alibaba Cloud account used to access the Hologres instance.

You can obtain the AccessKey ID from the AccessKey Pair page.

AccessKey Secret

The AccessKey secret of the Alibaba Cloud account used to access the Hologres instance.

You can obtain the AccessKey secret from the AccessKey Pair page.

targetServerType

The instances to which JDBC can connect. The value any indicates that JDBC can connect to any instance whose endpoint is specified in the URL.

Valid values in Hologres V2.0.10 and later:

  • master: JDBC can connect to only the primary instance.

  • slave: JDBC can connect to only read-only secondary instances.

  • preferSlave: JDBC preferentially connects to read-only secondary instances. If the connection fails, JDBC connects to the primary instance.

JDBC determines whether an instance is a primary instance or read-only secondary instance based on the value of the Grand Unified Configuration (GUC) parameter in_hot_standby. Valid values of in_hot_standby:

  • off: primary instance.

  • on: read-only secondary instance.

loadBalanceHosts

The sequence in which JDBC attempts to connect to read-only secondary instances. Valid values:

  • False: JDBC connects to read-only secondary instances in the same sequence as the instance endpoints specified in the URL. This is the default value.

  • True: JDBC randomly connects to a read-only secondary instance.

hostRecheckSeconds

The cache time for JDBC to connect to an instance specified in the endpoint list. The default value is 10. Unit: seconds.

If you want to adjust the cache time, you can change the value of the hostRecheckSeconds parameter. In this example, the cache time is changed to 30.

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

Note

For more information about how to configure JDBC, see the JDBC documentation.

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. 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();
                }
                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-wxxxxxxxx04image..png

    • Metrics of the instance hgpostcn-cn-wxxxxxxxx05image..png