Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the strategy and practice of database connection configuration, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

A preface

Database connection plays an important role in the process of application executing SQL request completion. Especially when it comes to the sudden surge of traffic, the need to create a large number of connections, or network anomalies lead to reconnection, from the business side, sql execution is slow, and sql execution is not really slow. This article is based on our own production environment Durid practice, for your reference only, of course, different companies may have different link / business pressures.

2. Concrete practice

From the point of view of the overall system, we have to consider several points, how to configure the number of database connections properly, for idle connections, the abnormal timeout of the network, how to reuse connections efficiently, and the selection of druid version.

2.1 how to 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: here QPS and RT are the statistics of a single application. It is assumed that the number of requests that the client can handle increases linearly as the number of connections increases.

For instance

For example, if the time-consuming rt=2ms of a request, the number of requests that can be processed by each connection is S = 1000Universe 2 = 500, and the total number of requests in the business layer is MIMO 5000, then the reasonable number of connections is M/S=5000/500=10. In order to prevent the number of connections from being full, we will add N to the number of connections above, and the final number of connections is 10 million N.

Calculate the minIdle by counting the maximum QPS and RT at this time, and set initialSize = minIdle.

Calculate the maxActive by counting the QPS at the peak and the RT at this time.

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

Java-XX:+UseConcMarkSweepGC-XX:+CMSClassUnloadingEnabled-XX:+TieredCompilation-XX:TieredStopAtLevel=1-Xverify:none-client- jar / PATH/cmdline-jmxclient-0.10.3.jar-127.0.0.1 XX:+CMSClassUnloadingEnabled 7777 'com.alibaba.druid:type=DruidDataSourceStat' DataSourceList | & grep-E' ActiveCount | PoolingCount'

2.2 how to set the timeout

The main timeouts in the connection pool are:

Timeout for connectTimeout to establish a TCP connection

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

SocketTimeout timeout for waiting for a response after sending a request

Among them, connectTimeout is recommended not to be less than 1200ms. When TCP establishes a connection, the timeout retransmission time of SYN packet is 1s. The connectTimeout setting is too short, which is likely to cause the initialization of the connection pool to fail due to network jitter or packet loss trigger timeout during the initialization of the connection pool when the application is released.

SocketTimeout can be set according to the return time of the query with the longest application. Too long will cause health network problems, or an avalanche when there is a problem with the database service; too short will also cause frequent requests to time out. No shorter than 300ms. The minimum RTO of TCP is 200ms and is dynamically adjusted according to latency. A too short timeout will result in a single packet loss and a request timeout. All production environment databases are configured with SQL Killer, which automatically kills requests that take too long to execute. Therefore, there is no point in setting an overly long socketTimeout.

MaxWait can be set according to the waiting time expected by the application. To avoid avalanches in the event of network problems or database service problems, this time setting should not be too large. The default value 800ms below is a conservative setting. Applications can be set for a shorter time, such as 300ms. Too short a time can also result in an insufficient number of connections in the connection pool and a large number of timeouts when new connections are needed. It is recommended that it not be lower than 100ms.

2.3 how to set the connection hold time

Setting the time for a connection to remain active requires consideration of whether to connect directly or through the database middleware proxy. Generally speaking, the current production environment is mostly as follows:

App-> LVS-> Proxy-> DB

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

Among them, the LVS idle connection retention time is 90s. In order to avoid accessing connections that have been closed, Proxy keeps its own idle connections for [70,85) s. Therefore, in order to avoid getting connections that have been closed from the connection pool, the application should set itself to keep idle connections for no more than 70s. The effect after opening KeepAlive

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

Connections within the number of minIdle in the connection pool, idle time exceeding

MinEvictableIdleTimeMillis, the keepAlive operation is performed.

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

TimeBetweenEvictionRunsMillis=10000, minEvictableIdleTimeMillis=44000, maxEvictableIdleTimeMillis=55000 .

2.4 required configuration

The following default configuration can be adjusted according to the actual situation.

After version 1.0.28, the keepAlive configuration is added and turned off by default. Using the keepAlive feature, version 1.1.16 or later is recommended. General business does not need to be opened, unless the number of requests per minute is in single digits or the startup time is too long, which leads to the expiration of the initial connection.

Version 2.5 druid

It is recommended to use the latest version, do not use the old version, so as not to encounter bug.

E.g. Maven configuration:

Com.alibaba druid 1.0.27 after reading the above, do you have any further understanding of the strategy and practice of database connection configuration? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.