E-MapReduce:Optimize StarRocks access performance with SLB and client connection pools

SDK method (Java and HikariCP example)

• Maven dependencies

  Add the following dependencies to the pom.xml file of your project.

  <dependency>
      <groupId>com.zaxxer</groupId>
      <artifactId>HikariCP</artifactId>
      <version>5.0.1</version>
  </dependency>
  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.33</version>
  </dependency>

• HikariCP connection pool configuration

  import com.zaxxer.hikari.HikariConfig;
  import com.zaxxer.hikari.HikariDataSource;
  
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  
  public class StarRocksConnectionPoolExample {
      public static void main(String[] args) {
          // Configure the HikariCP connection pool
          HikariConfig config = new HikariConfig();
          // Internal same-region endpoint
          config.setJdbcUrl("jdbc:mysql://fe-c-xxx-internal.starrocks.aliyuncs.com:9030/<yourDatabase>"); 
          config.setUsername("<yourUsername>");
          config.setPassword("<yourPassword>");
  
          // Configure core parameters
          config.setMaximumPoolSize(20);                   // Set the maximum number of connections to 20.
          config.setMinimumIdle(5);                        // Set the minimum number of idle connections to 5.
          config.setConnectionTimeout(30000);              // Set the connection timeout to 30 seconds (30,000 milliseconds).
          config.setIdleTimeout(600000);                   // Set the idle connection timeout to 10 minutes (600,000 milliseconds).
          config.setMaxLifetime(1800000);                  // Set the maximum lifetime of a connection to 30 minutes (1,800,000 milliseconds).
          config.setConnectionTestQuery("SELECT 1");       // Health check SQL query
          config.setValidationTimeout(5000);               // Validation timeout
          config.setLeakDetectionThreshold(60000);         // Connection leak detection (1 minute)
  
          // Create the data source
          HikariDataSource dataSource = new HikariDataSource(config);
  
          // Use the connection pool to execute a query
          try (Connection conn = dataSource.getConnection();
               PreparedStatement stmt = conn.prepareStatement("SELECT count(*) FROM your_table");
               ResultSet rs = stmt.executeQuery()) {
  
              if (rs.next()) {
                  System.out.println("Count: " + rs.getLong(1));
              }
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
  }

BI tool configuration

Common BI tools, such as Tableau, Superset, Metabase, and Quick BI, support setting connection pool parameters in their advanced options.