Lindorm:Desenvolva aplicações com o pool de conexões Druid para Java

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.2.11</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>8.3.0</version>
</dependency>

<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>mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>8.3.0</version>
</dependency>

# Driver class — keep this unchanged.
driverClassName=com.mysql.cj.jdbc.Driver

# Replace <endpoint> with your LindormTable endpoint for MySQL and <database> with your database name.
# To get the endpoint, see View endpoints: https://www.alibabacloud.com/help/en/lindorm/user-guide/view-endpoints
# Keep the other URL parameters unchanged to maintain optimal performance.
url=jdbc:mysql://<endpoint>:33060/<database>?sslMode=disabled&allowPublicKeyRetrieval=true&useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=100&prepStmtCacheSqlLimit=50000000&socketTimeout=120000
username=<username>
password=<password>

# Initialize the connection pool on startup — keep this unchanged.
init=true

# Number of connections to create during initialization.
initialSize=10
# Maximum number of connections in the pool. Set this to match your thread pool size.
maxActive=40
# Minimum number of idle connections. For high-throughput scenarios, set this equal to maxActive.
# For workloads with large fluctuations, use a smaller value.
minIdle=40
# Maximum time (ms) the client waits to get a connection — keep this unchanged.
maxWait=30000

# Limits how many times a single connection can be reused, preventing uneven load
# across Lindorm SQL frontend nodes. Has a minor performance impact.
phyMaxUseCount=30000

# Keep-alive settings — keep these unchanged to prevent unexpected disconnections.
druid.keepAlive=true
# If a connection has been idle for longer than this value (ms), its validity is checked.
druid.keepAliveBetweenTimeMillis=120000
# Interval (ms) at which idle connections are checked for eviction or keep-alive.
timeBetweenEvictionRunsMillis=60000
# Connections idle for longer than this value (ms) are candidates for eviction.
minEvictableIdleTimeMillis=1800000
maxEvictableIdleTimeMillis=1800000

# Connection validation settings — keep these unchanged.
testWhileIdle=true
testOnBorrow=false
testOnReturn=false

Parâmetros de conexão

Dimensione o pool de conexões

Os nós de front-end do Lindorm SQL usam o Server Load Balancer (SLB) para balanceamento de carga. Evite manter conexões abertas por longos períodos para distribuir as solicitações uniformemente. Configure phyMaxUseCount para limitar a reutilização de conexões.

• Defina maxActive igual ao tamanho do pool de threads da aplicação.

• Para cargas de trabalho com alto throughput e sensíveis à latência, defina minIdle igual a maxActive para garantir que as conexões estejam sempre prontas.

• Em cenários com flutuações significativas de tráfego, use um valor menor para minIdle para reduzir o consumo de recursos ociosos.

// Load configuration from druid.properties.
Properties properties = new Properties();
InputStream inputStream = DruidPoolDemo.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(inputStream);

// Initialize the connection pool.
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

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 a single row.
try (Connection connection = dataSource.getConnection()) {
    String sql = "insert 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);
    }
}

// Batch insert.
String insertSql = "insert into " + tableName + "(id,name) values(?,?)";
int batchSize = 100;
try (Connection connection = dataSource.getConnection()) {
    try (PreparedStatement ps = connection.prepareStatement(insertSql)) {
        for (int i = 0; i < batchSize; i++) {
            ps.setString(1, "aa" + i);
            ps.setString(2, "bb" + i);
            ps.addBatch();
        }
        ps.executeBatch();
    }
}

// Query data.
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.
try (Connection connection = dataSource.getConnection()) {
    String sql = "delete from " + tableName + " where id=?";
    try (PreparedStatement ps = connection.prepareStatement(sql)) {
        ps.setString(1, "aa");
        ps.executeUpdate();
    }
}

Lidar com interrupções de conexão

Em ambientes de rede complexos, interrupções de conexão podem ocorrer devido a gargalos de gateway, instabilidade na rede ou alta perda de pacotes. As configurações de keep-alive em druid.properties ajudam a manter conexões ociosas, mas implemente também um mecanismo de nova tentativa no código da aplicação para falhas transitórias.

Durante atualizações e reinicializações do servidor, as conexões ativas podem sofrer interrupções temporárias. Capture as exceções e tente executar a operação novamente.

Monitorar o pool de conexões

Verifique periodicamente as configurações efetivas e as estatísticas do pool de conexões. Confira também as configurações nos logs:

druidDataSource.getStatData();  // Returns current pool statistics
druidDataSource.dump();         // Logs detailed pool configuration and state

jdbc:mysql://<endpoint>:33060/<database>?sslMode=disabled&allowPublicKeyRetrieval=true&useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=100&prepStmtCacheSqlLimit=50000000&socketTimeout=120000