A preface

The database connection is an important part of the application’s process of executing SQL requests. SQL execution is slow from the perspective of the service side, especially when a large number of connections need to be created due to sudden surge of traffic, or network anomalies cause reconnection. At this time, SQL execution is not really slow. This article is based on Durid best practices in our own production environment and is for your reference only, although link/business pressures may vary from company to company. Specific to individual parameters need to be treated differently.

Ii. Concrete Practice

From an overall system perspective, we need to consider the number of connections to the database, how to configure idle connections, timeout times for network exceptions, how to efficiently reuse connections, and druid version selection.

2.1 How Do I Set the Connection Pool Size

Appropriate connection pool size and QPS for business requests and RT for individual requests in milliseconds. Basic formula:

Number of connections = QPS /(1000/RT) + N = QPS * RT /1000 + N

Note: QPS and RT are calculated for a single application. Assume that as the number of connections increases, the number of requests a client can handle increases linearly.

For example

For example, the time of a request rt=2ms, the number of requests processed by each connection S= 1000/2 =500, and the total number of requests of the service layer M=5000, the reasonable number of connections is M/S=5000/500=10 to avoid the connection number being occupied. We're going to add N to the number of connections above, and we're going to end up with 10+N.Copy the code

Calculate the maximum QPS and RT at this time to calculate minIdle, and set initialSize = minIdle.

Calculate the QPS at the peak and RT at this time to calculate maxActive.

You can observe Druid’s actual connection pool status using JMX, focusing on ActiveCount: number of active connections, PoolingCount: number of connections in the pool. And consider adjustment according to the actual situation.

java -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled -XX:+TieredCompilation -XX:TieredStopAtLevel=1 -Xverify:none Druid :type=DruidDataSourceStat’ – PATH/cmdline-jmxclient-0.10.3.jar – 127.0.0.1:7777 ‘com.alibaba. DataSourceList |& grep -E ‘ActiveCount|PoolingCount’

2.2 How Do I Set the Timeout Period

The timeout times in the connection pool are:

ConnectTimeout Timeout period for establishing a TCP connection

MaxWait gets the maximum waiting time for a connection from the connection pool

SocketTimeout Timeout period for waiting for a response after sending a request

The recommended value of connectTimeout is not less than 1200ms. When a TCP connection is established, the timeout retransmission time of SYN packets is 1s. If the value of connectTimeout is too short, the connection pool initialization may fail during application publication due to network jitter or packet loss triggered by timeout during the initialization of intermediate network devices.

SocketTimeout can be set based on the longest application query return time. Too long can cause network problems, or database service problems when the avalanche; Too short can also cause frequent requests to time out. Not less than 300ms. TCP has a minimum RTO of 200ms, which is dynamically adjusted based on latency. If the timeout period is too short, a single packet loss will cause a request timeout. SQL Killer is configured in all production databases to automatically kill requests that take a long time to execute. Therefore, it makes no sense to set a long socketTimeout.

MaxWait can be set based on the waiting time expected by the application. To avoid an avalanche in the event of network problems or database service problems, this time setting should not be too large. The following default value of 800ms is conservative. Applications can be set to a shorter time, such as 300ms. Too short a time can also result in insufficient connections in the connection pool, resulting in a large number of timeouts when new connections need to be created. It is recommended not to be less than 100ms.

2.3 How Do I Set the Connection Holdtime

Setting the time for a connection to remain active requires consideration of whether it is connected directly or through a database middleware proxy. Generally, the current production environment is:

App -> LVS -> Proxy -> DB

The RDS access path is App -> LVS -> Proxy.

The retention time of LVS idle connection is 90s. To prevent the Proxy from accessing closed connections, its own idle connection retention time is set to [70, 85) s. Therefore, to prevent the application from obtaining closed connections from the connection pool, it should set its own idle connection retention time to no more than 70s

The number of minIdle is filled when the connection pool is initialized.

The number of minIdle connections in the connection pool exceeds the idle time limit

MinEvictableIdleTimeMillis, can perform keepAlive operations.

When the dead connections detected by ExceptionSorter caused by network disconnection are cleared, the number of connections to minIdle is automatically added.

TimeBetweenEvictionRunsMillis = 10000, minEvictableIdleTimeMillis = 44000, maxEvictableIdleTimeMillis = 55000.Copy the code

2.4 Mandatory Configuration Items

The following default configurations can be adjusted as required.

<bean id="cartDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="url" value="${cluster.jdbc.url}"/> <property name="username" value="${cluster.jdbc.username}"/> <property name="password" value="${cluster.jdbc.password}"/> <property name="connectionInitSqls" value="set names utf8mb4"/> <! --> <property name="initialSize" value="5" /> <! Property name="maxActive" value="20" /> <! Property name="minIdle" value="5" /> <! -- The maximum wait time to get a connection from the pool is 800 ms; Property name="maxWait" value="800" /> <! <property name="phyTimeoutMillis" value="7200000"/> <! <property name="removeAbandoned" value="true"/> <! <property name="removeAbandonedTimeout" value="180"/> <! <property name="testWhileIdle" value="true"/> <! <property name="testOnBorrow" value="false"/> <! <property name="testOnReturn" value="false"/> <! <property name="validationQuery" value="SELECT 1"/> <! < Property name="validationQueryTimeout" value="1"/> <! - periodic eliminate long stay unused idle connections in the pool, 10 seconds at a time -- - > < property name = "timeBetweenEvictionRunsMillis" value = "10000" / > <! - how long can be thought of as free spare time is too long and need to weed out 44 seconds - > < property name = "minEvictableIdleTimeMillis" value = "44000" / > <! - if the idle time is too long even if left to the connection pool connection < minIdle will be weed out 55 seconds - > < property name = "maxEvictableIdleTimeMillis" value = "55000" / > <! <property name="defaultAutoCommit" value="true"/> <! Property name="logAbandoned" value="true"/> <! -- Network read timeout, network connection timeout socketTimeout: <property name="connectionProperties" value="socketTimeout=3000; connectTimeout=1200"/> <property name="proxyFilters"> <list> <ref bean="log-filter"/> </list> </property> </bean>Copy the code

After version 1.0.28, the keepAlive configuration is added and disabled by default. To use the keepAlive function, 1.1.16 or later is recommended. Services do not need to be enabled unless the number of requests per minute is in single digits or the initial connection expires due to a long startup time.

Druid version 2.5

You are advised to use the latest version. Do not use an old version to avoid bugs.

Maven configuration:

  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.27</version>
  </dependency>
Copy the code

Three summary

This article is a supplement, hope to need to pay attention to the database connection configuration of friends help.

This public number long-term focus on database technology and performance optimization, failure case analysis, database operation and maintenance technology knowledge sharing, personal growth and self-management and other topics, welcome to scan code attention.