Lindorm:Use Druid to develop applications

Prerequisites

• Java Development Kit (JDK) 1.8 or later is installed.

• A whitelist is configured for the Lindorm instance. For more information, see Configure whitelists.

• The version of LindormTable is 2.3.1 or later. For more information about how to upgrade the version of LindormTable, see Upgrade the minor engine version of a Lindorm instance.

Considerations

• Lindorm frontend nodes use Server Load Balancer (SLB) for load balancing, and clients connect to these frontend nodes. To distribute client requests evenly across frontend nodes, we recommend that you do not maintain connections for too long. You can configure the phyMaxUseCount and phyTimeoutMillis parameters.

• You need to obtain a connection from the connection pool before executing a query, and call the conn.close() operation to return the connection to the pool after the query. The next time you execute a query, you can obtain a connection again from the connection pool. If a connection is not returned after the query and becomes invalid, Druid cannot detect its invalid state.

• In complex network environments, connection interruptions may occur due to gateway performance bottlenecks, long network links, network jitter, high retransmission rate, or high packet loss rate. We recommend that you properly configure the connection pool and implement the retry mechanism in your business code when necessary.

• When the server is upgraded and restarted, connections may be temporarily interrupted. Even with a connection pool, your business may experience exceptions. We recommend that you catch exceptions and implement the retry mechanism.

• We recommend that you modify connection pool configurations based on your business requirements and ensure that the configurations take effect. You can periodically query effective configurations and connection pool information in your program by using the DruidDataSource#getStatData() and DruidDataSource#dump() methods, and check the configurations in logs.

Preparations

• Before you use Druid to connect to LindormTable, you must install Druid and Lindorm JDBC Driver. For example, you can add the following dependencies to the pom.xml file in your Maven project:

  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.11</version>
  </dependency>
  <dependency>
    <groupId>com.aliyun.lindorm</groupId>
    <artifactId>lindorm-all-client</artifactId>
    <version>2.2.1.3</version>
  </dependency>

• If you want to use druid-spring-boot-starter to start Druid, you must first exclude the druid component on which druid-spring-boot-starter depends and then explicitly add the dependency on the druid component to the configuration file. The following example shows how to configure dependencies when you use druid-spring-boot-starter to start Druid:

  <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>1.2.11</version>
     <exclusions>
        <exclusion>
           <groupId>com.alibaba</groupId>
           <artifactId>druid</artifactId>
        </exclusion>
     </exclusions>
  </dependency>
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.11</version>
  </dependency>
  <dependency>
    <groupId>com.aliyun.lindorm</groupId>
    <artifactId>lindorm-all-client</artifactId>
    <version>2.2.1.3</version>
  </dependency>

Procedure

1. Configure parameters for Druid. In the src/main/resources path of your Maven project, create the druid.properties file and add the following configurations to the file:

   # Specify the name of the driver class. You can keep this configuration unchanged.
   driverClassName=com.aliyun.lindorm.table.client.Driver
   # You can replace the url, username, and password parameters with the actual values in your business. You can obtain the values of these parameters on the Lindorm console.
   url=jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****.lindorm.rds.aliyuncs.com:30060
   username=****
   password=****
   # Replace **** with the database that you want to connect to.
   connectionProperties=database=****
   
   # Initialize the connection pool to create a connection. We recommend that you keep this configuration unchanged.
   init=true
   # Specify the number of connections that you want to create during initialization. You can configure this parameter based on your requirements.
   initialSize=10
   # Specify the number of idle connections in the connection pool. You can configure this parameter based on your requirements. For high-performance scenarios, set this parameter to a value the same as maxActive. If the business has significant fluctuations, set this parameter to a smaller value.
   minIdle=40
   # Specify the maximum number of idle connections in the connection pool. You can configure this parameter based on your requirements. We recommend that you set this parameter to a value the same as the thread pool size.
   maxActive=40
   # Specify the maximum time that the client can wait to obtain a connection. Unit: ms. We recommend that you keep this configuration unchanged.
   maxWait=30000
   
   # Specify the maximum usage count per connection to prevent server-side load imbalance caused by prolonged use of the same connection, which may slightly affect performance.
   druid.phyMaxUseCount=10000
   
   # Configure parameters related to connection keep-alive. We recommend that you keep these configurations unchanged. Otherwise, the connection may be unexpectedly disconnected.
   # In this case, the ConnectionDisconnectedException exception is reported.
   druid.keepAlive=true
   druid.keepAliveBetweenTimeMillis=30000
   minEvictableIdleTimeMillis=300000
   maxEvictableIdleTimeMillis=600000
   timeBetweenEvictionRunsMillis=5000
   phyTimeoutMillis=1800000
   
   # Configure parameters that are required to verify connections. We recommend that you keep these configurations unchanged.
   validationQuery=SELECT 1
   testWhileIdle=true
   testOnBorrow=false
   testOnReturn=false
   
   # Configure parameters related to cache. In this example, cache is disabled. We recommend that you keep these configurations unchanged.
   # Otherwise, the NoSuchStatement exception may be reported.
   poolPreparedStatements=false
   maxOpenPreparedStatements=-1
   druid.maxPoolPreparedStatementPerConnectionSize=-1

   Note

   • Adjust or replace the values of the following parameters in actual use:

     • url: The endpoint used to connect to LindormTable. For more information about how to obtain the endpoint, see Obtain an endpoint.

     • username: The username used to access LindormTable. You can view the username in the cluster management system of LindormTable. For more information, see Manage users.

     • password: The password used to access LindormTable. If you forget your password, you can change the password in the cluster management system of LindormTable. For more information about how to change your password, see Change the password of a user.

   • For more information about the parameters that you can configure, see DruidDataSource parameter configurations.

2. Load the parameter configurations of Druid and initialize the connection pool.

   // Load parameter configurations.
   Properties properties = new Properties();
   InputStream inputStream = DruidPoolDemo.class.getClassLoader().getResourceAsStream("druid.properties");
   properties.load(inputStream);
   // Initialize the connection pool.
   DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

3. Use Druid to obtain connection information from JDBC and connect to LindormTable.

   /* -------------- Example on how to connect to LindormTable using JDBC  ----------------- */
   
   String tableName = "sql_table_" + new Random().nextInt(1000);
   // Create a table.
   try (Connection connection = dataSource.getConnection()) {
       try (Statement statement = connection.createStatement()) {
           String sql = "create table if not exists " + tableName + "(id VARCHAR, name VARCHAR, primary key(id))";
           int ret = statement.executeUpdate(sql);
           System.out.println(ret);
       }
   }
   
   // Insert data to the table.
   try (Connection connection = dataSource.getConnection()) {
       String sql = "upsert into " + tableName + "(id,name) values(?,?)";
       try (PreparedStatement ps = connection.prepareStatement(sql)) {
           ps.setString(1, "aa");
           ps.setString(2, "bb");
   
           int ret = ps.executeUpdate();
           System.out.println(ret);
       }
   }
   
   // Query data in the table.
   try (Connection connection = dataSource.getConnection()) {
       String sql = "select * from " + tableName + " where id=?";
       try (PreparedStatement ps = connection.prepareStatement(sql)) {
           ps.setString(1, "aa");
           ResultSet rs = ps.executeQuery();
           while (rs.next()) {
               String id = rs.getString(1);
               String name = rs.getString(2);
               System.out.println("id=" + id);
               System.out.println("name=" + name);
           }
       }
   }
   
   // Delete data from the table.
   try (Connection connection = dataSource.getConnection()) {
       String sql = "delete from " + tableName + " where id=?";
       try (PreparedStatement ps = connection.prepareStatement(sql)) {
           ps.setString(1, "aa");
           ps.executeUpdate();
       }
   }

Appendix: Load balancing explanation for connection pools

The connection pool mode (TCP persistent connections) is more efficient, but it may lead to uneven connection distribution in the following scenarios:

• Burst creation of numerous connections causing uneven distribution

  When an application suddenly creates many connections, if the load balancing device cannot refresh the connection statistics information of backend nodes in time, some backend LDServer nodes may handle more connection requests. Combined with connection pooling mechanisms, this ultimately causes some LDServer nodes to experience higher pressure than others, resulting in uneven connection distribution and affecting overall system performance.

• Load balancing health check anomalies causing uneven distribution

  Load balancers use active health checks to determine if backend nodes are functioning normally. When occasional anomalies occur in these health checks, some LDServer nodes may receive fewer connections. Combined with connection pooling, this ultimately causes some LDServer nodes to experience lower pressure than others, affecting overall system performance.

Druid connection pool has added the phyTimeoutMillis and phyMaxUseCount parameters to periodically refresh connections in the pool (for example, every 30 minutes or 10,000 executions). This solves the above issues while maintaining performance. We recommend adding these two parameters by default.