This topic describes how to use the Java Database Connectivity (JDBC) Druid connection pool to connect to an AnalyticDB for MySQL cluster.

Precautions

  • Only versions later than Druid 1.1.16 are supported.
  • When you use the Druid connection pool to connect to an AnalyticDB for MySQL cluster, we recommend that you set keepAlive to true. This way, you can reuse connections and prevent short-lived connections.
  • Server Load Balancer (SLB) is used to connect the frontend nodes of AnalyticDB for MySQL to the client. To send client requests evenly to the frontend nodes, we recommend that you configure a large number of connections in the connection pool and set maxEvictableIdleTimeMillis to a small value.
  • Modify connection pool configurations based on business requirements and make sure that the configurations take effect. You can call DruidDataSource#getStatData() and DruidDataSource#dump() to periodically obtain the effective configuration information and connection pool information. You can also view and verify the configuration information from logs.
  • To execute queries, you can obtain connections from the connection pool. After a query is complete, you must call conn.close() at the earliest opportunity to return the used connection to the connection pool. The next time you execute a query, you can obtain a connection again from the connection pool. If a connection is not returned after being used and becomes invalid, Druid cannot detect its invalid state.
  • In complex network scenarios that involve gateway performance bottlenecks, long network links, network jitters, high retransmission rates, or high packet loss rates, connections may be closed. If disconnections persist even though the connection pool is properly configured, we recommend that you use the retry policy to optimize the code.

Configure the Druid connection pool

 <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> 
     <!--jdbc_url: the endpoint of the AnalyticDB for MySQL cluster to which you want to connect. You can obtain the endpoint on the Cluster Information page of the AnalyticDB for MySQL console. -->
     <property name="url" value="${jdbc_url}" />

     <!--jdbc_user: the account used to connect to the AnalyticDB for MySQL cluster. It can be a privileged account or a standard account. -->
     <property name="username" value="${jdbc_user}" />

     <!--jdbc_password: the password of the account used to connect to the AnalyticDB for MySQL cluster. -->
     <property name="password" value="${jdbc_password}" />

     <!--Set the initial size of the connection pool, and the minimum and maximum numbers of connections. -->
     <property name="initialSize" value="5" />
     <property name="minIdle" value="10" /> 
     <property name="maxActive" value="20" />

     <!--Set the timeout period for obtaining a connection from the connection pool. -->
     <property name="maxWait" value="60000" />

     <!--Set the minimum and maximum amounts of time for a connection to keep idle in the connection pool. Unit: milliseconds. If the idle time of a connection exceeds the maximum amount of time, the connection is removed. -->
     <property name="minEvictableIdleTimeMillis" value="600000" />
     <property name="maxEvictableIdleTimeMillis" value="900000" />

     <!--Set the intervals at which the system detects idle connections to be closed. Unit: milliseconds.  The default value is 60000. A higher value may cause the Druid connection pool to fail to detect disconnections. -->
     <property name="timeBetweenEvictionRunsMillis" value="2000" />

     <!--Specify whether to check the validity of a connection whose idle time is larger than the value of timeBetweenEvictionRunsMillis each time you obtain the connection from the connection pool. A value of true indicates that the validity of the connection is checked. A value of false indicates that the validity of the connection is not checked. -->
     <property name="testWhileIdle" value="true" />

     <!--Specify whether to check the validity of a connection each time you obtain the connection from and return the connection to the connection pool. -->
     <!--Specify these parameters only in highly important or unreliable network scenarios. In other cases, we recommend that you replace these parameters with testWhileIdle and timeBetweenEvictionRunsMillis. -->
     <property name="testOnBorrow" value="false" />
     <property name="testOnReturn" value="false" />

     <!--Specify whether to configure regular keepalive operations and set the keepalive interval. -->
     <property name="keepAlive" value="true" />
     <property name="keepAliveBetweenTimeMillis" value="30000" />  <!--The default value of keepalive interval is 120000. Unit: milliseconds. -->

     <!--Specify the maximum number of times a connection can be used. This prevents unbalanced loads on the server caused by long-term use of a connection. -->
     <property name="phyMaxUseCount" value="1000" />

     <!--Specify the query that is used to test and verify the connection validity. In new versions of Druid, this parameter is replaced with mysqlPing by default.-->
     <property name="validationQuery"  value="select 1" />
     
     <!--Specify the filters that are used for monitoring statistics. -->
     <property name="filters" value="stat" /> 
 </bean>     

ValidationQuery

In new versions of Druid, the validationQuery parameter is replaced with mysqlPing by default. To use the validationQuery parameter, perform the following settings:
...
druidDataSource.init();

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

Obtain information of the connection pool

You can call dataSource.dump() to obtain basic information of the connection pool and connections.
{
    CreateTime:"2022-06-01 15:28:10",   # The time when the connection pool is created.
    ActiveCount:0,      # The number of connections that are obtained from the connection pool and are being used.
    PoolingCount:2,     # The number of connections that remain in the connection pool.
    CreateCount:2,      # The number of created connections, including connections that are recreated after being destroyed.
    DestroyCount:0,     # The number of destroyed connections, including connections that are destroyed due to connection exception or expiration.
    CloseCount:4,       # The number of connections that are returned to the connection pool by calling conn.close().
    ConnectCount:4,     # The number of times connections are obtained by calling dataSource.getConnection().
    Connections:[
        {
            ID:525571,     # The ID of a connection.
            ConnectTime:"2022-06-01 15:28:11",        # The time when a connection is created.
            UseCount:0,                     # The number of times a connection is obtained for use. The sum of UseCount values for all connections is equal to the value of ConnectCount.
            LastActiveTime:"2022-06-01 15:28:11"    # The time when a connection is last obtained for use. If this connection is never used, the value of LastActiveTime is the same as that of ConnectTime.
        },
        {ID:1263877414, ConnectTime:"2022-06-01 15:28:11", UseCount:4, LastActiveTime:"2022-06-01 15:28:11"}
    ]
}

Obtain the connection pool configurations that are in effect

You can call dataSource.getStatData() to obtain the connection pool configurations that are in effect.
{
    Identity=85777802,
    Name=DataSource-85777802,
    DbType=mysql,
    DriverClassName=com.mysql.jdbc.Driver,
    URL=jdbc:mysql://host:port/db_name,
    UserName=haicen,
    FilterClassNames=[],
    WaitThreadCount=0,
    NotEmptyWaitCount=0,
    NotEmptyWaitMillis=0,
    PoolingCount=2,
    PoolingPeak=2,
    PoolingPeakTime=Wed Jun 01 16:08:15 CST 2022,
    ActiveCount=0,
    ActivePeak=1,
    ActivePeakTime=Wed Jun 01 16:08:15 CST 2022,
    InitialSize=1,
    MinIdle=2,
    MaxActive=3,
    QueryTimeout=0,
    TransactionQueryTimeout=0,
    LoginTimeout=0,
    ValidConnectionCheckerClassName=com.alibaba.druid.pool.vendor.MySqlValidConnectionChecker,
    ExceptionSorterClassName=com.alibaba.druid.pool.vendor.MySqlExceptionSorter,
    TestOnBorrow=true,
    TestOnReturn=true,
    TestWhileIdle=true,
    DefaultAutoCommit=true,
    DefaultReadOnly=null,
    DefaultTransactionIsolation=null,
    LogicConnectCount=14,
    LogicCloseCount=14,
    LogicConnectErrorCount=0,
    PhysicalConnectCount=6,
    PhysicalCloseCount=4,
    PhysicalConnectErrorCount=0,
    DiscardCount=0,
    ExecuteCount=14,
    ExecuteUpdateCount=0,
    ExecuteQueryCount=14,
    ExecuteBatchCount=0,
    ErrorCount=0,
    CommitCount=0,
    RollbackCount=0,
    PSCacheAccessCount=0,
    PSCacheHitCount=0,
    PSCacheMissCount=0,
    StartTransactionCount=0,
    TransactionHistogram=[
        J@6a472554,
        ConnectionHoldTimeHistogram=[
            J@7ff2a664,
            RemoveAbandoned=true,
            ClobOpenCount=0,
            BlobOpenCount=0,
            KeepAliveCheckCount=332,
            KeepAlive=true,
            FailFast=false,
            MaxWait=6000,
            MaxWaitThreadCount=-1,
            PoolPreparedStatements=false,
            MaxPoolPreparedStatementPerConnectionSize=10,
            MinEvictableIdleTimeMillis=600000,
            MaxEvictableIdleTimeMillis=900000,
            LogDifferentThread=true,
            RecycleErrorCount=0,
            PreparedStatementOpenCount=0,
            PreparedStatementClosedCount=0,
            UseUnfairLock=false,
            InitGlobalVariants=false,
            InitVariants=false
}