Use Druid Connection Pool for Stable MySQL Connections - AnalyticDB

Configuring a connection pool incorrectly for AnalyticDB for MySQL can cause dropped connections, uneven load across frontend nodes, and pool exhaustion under traffic spikes. This topic shows how to configure Druid to connect reliably to an AnalyticDB for MySQL cluster in the Spring Framework, with recommended parameter values and diagnostics.

Prerequisites

Before you begin, ensure that you have:

Druid version later than 1.1.16
The cluster endpoint — available on the Cluster Information page of the AnalyticDB for MySQL console
A database account (privileged account or standard account) and its password

How AnalyticDB for MySQL uses connections

AnalyticDB for MySQL uses Server Load Balancer (SLB) to distribute traffic across its frontend nodes. Three behaviors follow from this architecture:

Configure a large number of connections so requests spread evenly across all frontend nodes.
Set maxEvictableIdleTimeMillis to a small value so idle connections are recycled quickly, preventing load from concentrating on a subset of nodes.
Set keepAlive to true to reuse connections and avoid the overhead of short-lived connections.

Configure the connection pool

The following XML snippet configures a DruidDataSource bean for the Spring Framework. Replace the three ${...} placeholders with your actual values.

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
      init-method="init" destroy-method="close">

    <!-- Connection credentials -->
    <property name="url"      value="${jdbc_url}" />
    <property name="username" value="${jdbc_user}" />
    <property name="password" value="${jdbc_password}" />

    <!-- Pool size -->
    <property name="initialSize" value="5" />
    <property name="minIdle"     value="10" />
    <property name="maxActive"   value="20" />

    <!-- Connection acquisition timeout (ms) -->
    <property name="maxWait" value="60000" />

    <!-- Idle connection lifetime (ms)
         Connections idle longer than maxEvictableIdleTimeMillis are removed.
         Set maxEvictableIdleTimeMillis to a small value so idle connections
         rotate across SLB frontend nodes, keeping the load balanced. -->
    <property name="minEvictableIdleTimeMillis" value="600000" />
    <property name="maxEvictableIdleTimeMillis" value="900000" />

    <!-- Idle-connection scan interval (ms)
         Default: 60000. A value that is too high may prevent Druid from
         detecting dropped connections in time. -->
    <property name="timeBetweenEvictionRunsMillis" value="2000" />

    <!-- Validate connections idle longer than timeBetweenEvictionRunsMillis.
         Prefer this over testOnBorrow/testOnReturn in normal networks. -->
    <property name="testWhileIdle" value="true" />

    <!-- Validate on borrow/return — enable only in highly important or unreliable network scenarios. -->
    <property name="testOnBorrow" value="false" />
    <property name="testOnReturn" value="false" />

    <!-- Keepalive — prevents short-lived connections and improves connection reuse.
         Default for keepAliveBetweenTimeMillis: 120000 ms. -->
    <property name="keepAlive"                 value="true" />
    <property name="keepAliveBetweenTimeMillis" value="30000" />

    <!-- Limit reuse per connection to avoid long-term load imbalance across frontend nodes. -->
    <property name="phyMaxUseCount" value="1000" />

    <!-- Validation query (see "Enable validationQuery in newer Druid versions" below) -->
    <property name="validationQuery" value="select 1" />

    <!-- Monitoring statistics filter -->
    <property name="filters" value="stat" />
</bean>

Key parameters

Parameter	Recommended value	Effect of getting it wrong
`keepAlive`	`true`	If `false`, short-lived connections create overhead and may exhaust the pool under load.
`maxEvictableIdleTimeMillis`	A small value	If too large, connections stay on the same frontend node, causing load imbalance.
`timeBetweenEvictionRunsMillis`	`2000` (default: `60000`)	If too large, Druid may not detect dropped connections in time, leading to connection errors.
`testOnBorrow` / `testOnReturn`	`false` (default)	Setting both to `true` adds a round-trip on every get/return — use only in highly important or unreliable network scenarios.
`phyMaxUseCount`	`1000`	If unset, a single long-lived connection may handle all traffic, creating a hot frontend node.

Enable validationQuery in newer Druid versions

In newer versions of Druid, validationQuery is replaced by mysqlPing by default. To use validationQuery (select 1) instead, set the druid.mysql.usePingMethod property to false after calling init():

druidDataSource.init();

Properties properties = new Properties();
properties.setProperty("druid.mysql.usePingMethod", "false");
druidDataSource.setConnectProperties(properties);

Diagnose the connection pool

Get a pool snapshot

Call dataSource.dump() to get the current state of the pool and its individual connections:

{
    CreateTime:"2022-06-01 15:28:10",
    ActiveCount:0,
    PoolingCount:2,
    CreateCount:2,
    DestroyCount:0,
    CloseCount:4,
    ConnectCount:4,
    Connections:[
        {
            ID:525571,
            ConnectTime:"2022-06-01 15:28:11",
            UseCount:0,
            LastActiveTime:"2022-06-01 15:28:11"
        },
        {ID:1263877414, ConnectTime:"2022-06-01 15:28:11", UseCount:4, LastActiveTime:"2022-06-01 15:28:11"}
    ]
}

Field	Description
`CreateTime`	Time when the connection pool is created
`ActiveCount`	Connections currently checked out and in use
`PoolingCount`	Connections currently idle in the pool
`CreateCount`	Total connections ever created, including those recreated after destruction
`DestroyCount`	Total connections destroyed due to expiration or errors
`CloseCount`	Total `conn.close()` calls — connections returned to the pool
`ConnectCount`	Total `dataSource.getConnection()` calls; equals the sum of all `UseCount` values
`ID`	Internal connection identifier
`ConnectTime`	Time the physical connection was established
`UseCount`	Number of times this connection has been checked out
`LastActiveTime`	Last time this connection was checked out; equals `ConnectTime` if never used

Verify effective configuration

Call dataSource.getStatData() to confirm that your configuration is in effect. Compare the returned values against your bean definition — discrepancies indicate a setting that did not apply.

{
    InitialSize=1,
    MinIdle=2,
    MaxActive=3,
    TestOnBorrow=true,
    TestOnReturn=true,
    TestWhileIdle=true,
    KeepAlive=true,
    KeepAliveCheckCount=332,
    MinEvictableIdleTimeMillis=600000,
    MaxEvictableIdleTimeMillis=900000,
    MaxWait=6000,
    ...
}

Call getStatData() and dump() periodically in production to confirm that settings remain in effect and the pool is behaving as configured. You can also verify configuration from application logs.

Usage notes

Always call conn.close() as soon as a query finishes to return the connection to the pool. If a connection is not returned after being used and becomes invalid, Druid cannot detect its invalid status.
In complex network environments — such as those with gateway bottlenecks, long network paths, network jitters, high retransmission rates, or packet loss — connections may drop even with a correctly configured pool. Add a retry policy to your application code to handle transient disconnections.