Lindorm:Develop applications using the Druid connection pool for 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

Paramètres de connexion

Dimensionner le pool de connexions

Les nœuds frontal SQL de Lindorm utilisent Server Load Balancer (SLB) pour l'équilibrage de charge. Évitez de conserver les connexions pendant de longues périodes afin de répartir uniformément les requêtes. Configurez le paramètre phyMaxUseCount pour limiter la réutilisation des connexions.

• Définissez maxActive avec la même valeur que la taille du pool de threads de votre application.

• Pour les charges de travail à haut débit et sensibles à la latence, définissez minIdle avec la même valeur que maxActive afin que les connexions soient toujours prêtes.

• Pour les charges de travail présentant des fluctuations de trafic importantes, utilisez une valeur minIdle plus faible pour réduire la consommation de ressources inactives.

// 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();
    }
}

Gérer les interruptions de connexion

Dans des environnements réseau complexes, des interruptions de connexion peuvent survenir en raison de goulots d'étranglement au niveau de la passerelle, de jitter réseau ou d'une perte élevée de paquets. Les paramètres de keep-alive du fichier druid.properties aident à maintenir les connexions inactives, mais vous devez également implémenter un mécanisme de nouvelle tentative dans le code de votre application pour gérer les échecs temporaires.

Lorsque le serveur est mis à niveau et redémarré, les connexions actives peuvent être temporairement interrompues. Interceptez les exceptions et relancez l'opération.

Surveiller le pool de connexions

Vérifiez régulièrement les configurations effectives et les statistiques du pool de connexions, ainsi que les configurations dans les journaux :

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