AnalyticDB:Druid connection pool

Usage notes

• 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 the keepAlive parameter 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 the maxEvictableIdleTimeMillis parameter to a small value.

• Modify connection pool configurations based on your business requirements and make sure that the configurations take effect. You can call the DruidDataSource#getStatData() and DruidDataSource#dump() functions 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 the conn.close() function 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 status.

• 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 used to connect to the AnalyticDB for MySQL cluster. 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 database 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 database 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 interval 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 the timeBetweenEvictionRunsMillis parameter each time you obtain the connection from the connection pool. A value of true specifies that the validity of the connection is checked. A value of false specifies 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 the following parameters only in highly important or unreliable network scenarios. In other cases, we recommend that you replace the 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 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>     

Allow the ValidationQuery parameter to take effect

...
druidDataSource.init();

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

Obtain information about the connection pool

{
    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 the conn.close() function.
    ConnectCount:4,     # The number of times connections are obtained by calling the dataSource.getConnection() function.
    Connections:[
        {
            ID:525571,     # The connection ID.
            ConnectTime:"2022-06-01 15:28:11",        # The time when the connection is created.
            UseCount:0,                     # The number of times the connection is obtained for use. The sum of UseCount values of all connections is equal to the value of the ConnectCount parameter.
            LastActiveTime:"2022-06-01 15:28:11"    # The time when the connection is last obtained for use. If this connection is never used, the value of the LastActiveTime parameter is the same as that of the ConnectTime parameter.
        },
        {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

{
    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
}