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

This article will explain in detail how to configure the database connection pool. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

I. connection pool configuration

1.1 maxWait

Parameter indicates the timeout wait time (in milliseconds) for getting a connection from the connection pool. Note that this parameter only manages the timeout for obtaining a connection. The direct reason for obtaining connection waiting is that there are no available connections in the pool, including: the connection pool is not initialized, the connection has not been used for a long time and has been released, a new connection is needed while the connection is in use, or the connection pool has been exhausted and needs to be returned after the connection is used up. A key point here is that when maxWait is not configured or configured to 0, it means no wait timeout (which may be inconsistent with some people's expectation that-1 means infinite wait, although the meaning of maxWait configured to-1 in druid is the same). Readers who are interested in the implementation details can start with the com.alibaba.druid.pool.DruidDataSource#getConnection method to see the logic of the parameters.

Recommended configuration: private network (good network condition) 800; when the network condition is not very good, it is recommended to be greater than or equal to 1200, because the retry of tcp connection usually takes 1 second.

What happens if you don't configure maxWait? Some apps may do this, and there are no exceptions, but Murphy's law will work eventually. Here are a few real examples:

Case one

/ / Parameter configuration

MaxWait=0

MaxActive=5

...

There is no problem with the business under normal traffic, but the sudden influx of heavy traffic causes the connection pool to be exhausted, and all new DB requests are waiting to obtain the connection. Since maxWait=0 means unlimited waiting, the waiting queue will be longer and longer when the request speed is greater than the processing speed. The final business performance is that a large number of service interfaces time out, and the higher the traffic, the lower the actual throughput.

Case two

MaxWait=0

RemoveAbandoned=true

RemoveAbandonedTimeout=180

...

Phenomenon: there is no message backlog in the normal operation of the business code for a long time, resulting in a large number of pressure test data after a full link stress test, resulting in the accumulation of MQ messages. Even if the service is restarted, it can only run normally for dozens of seconds, and then it will enter a state of stagnant consumption. Using jstack, it was found that it was stuck while obtaining the database connection, and an error occurred about 3 minutes later: abandon connection, owner thread: xxx. Finally, the business MQ messages are consumed normally by configuring maxWait=3000 (3-second timeout).

Reason analysis: the business relies on two data sources, datasource1 and datasource2, in which the transaction of both libraries is opened in some code snippets. As shown in figure 1, thread 1 acquires the last connection connection [n] in datasource1 and waits for a connection to datasource2, while thread 2 is performing a similar operation, resulting in a deadlock wait. You must be familiar with this kind of interlock, but this time it happened on DB. Why the program reports an error in abandon connection after a period of time is due to the configuration of {removeAbandoned:true and removeAbandonedTimeout:180}. This configuration means that if a connection has not been returned for 180 seconds, it is considered an abnormal connection (usually millisecond for OLTP business queries), and the connection needs to be closed. The reason why there is no problem under normal circumstances is that the water level of the connection pool is relatively low and sufficient resources do not cause waiting for each other.

Figure 1. Deadlock problem of double DB connection pool

1.2 connectionProperties

Parameters are strings represented by key-value pairs, where connectTimeout and socketTimeout can be configured, both in milliseconds, which are important in dealing with network exceptions. ConnectTimeout configures the timeout for establishing a TCP connection, and socketTimeout configures the timeout for waiting for a response after sending a request. These two parameters can also be configured by adding connectTimeout=xxx&socketTimeout=xxx in jdbc url. Try to configure both in connectinoProperties and jdbc url, and find that the configuration in connectionProperties takes precedence. If you do not set these two timeouts, the service will have a very high risk. The real case is that after a network exception, it is found that the application cannot connect to the DB, but it can access the DB normally after restart. Because the socket cannot detect a network error under a network exception, the connection has actually become a "dead connection". If the socket network timeout is not set, the connection will wait for the DB to return the result, resulting in new requests unable to get the connection.

Recommended configuration:

SocketTimeout=3000;connectTimeout=1200

1.3 keepAlive

Parameter indicates whether to keep the idle connection alive, Boolean type. Many people may think that the druid connection pool will maintain the heartbeat of DB connections by default and keep the connections in the pool alive. After configuring the parameter minIdle, many people think that minIdle should at least maintain so many idle connections. In fact, the parameter keepAlive is added after druid 1.0.28, and the default value is false, that is, no connection is saved.

So you need to keep the connection alive, so is it OK to configure keepAlive as true? Although true does turn on the survival mechanism, how many should be kept alive and what are the rules for heartbeat check, all of these need to be configured correctly, otherwise it may backfire. Here you need to know several relevant parameters: the minimum number of connection pools in minIdle, the number of connections kept alive, and the number of connections that will be retained during idle connection timeout (provided that the current number of connections is greater than or equal to minIdle). In fact, keepAlive only maintains existing connections and does not create new connections, even if the number of connections is less than minIdle MinEvictableIdleTimeMillis unit millisecond, the minimum time a connection remains idle without being expelled, keep alive heartbeat only for connections whose survival time exceeds this value; maxEvictableIdleTimeMillis unit millisecond, the maximum time a connection remains idle, if the connection performs any operation, the timer will be reset (including heartbeat protection activity); timeBetweenEvictionRunsMillis unit millisecond, the interval between Destroy threads to detect connections, will trigger the heartbeat during the detection process. For the detailed flow of keeping alive check, please see the source code com.alibaba.druid.pool.DruidDataSource.DestroyTask, where heartbeat check uses the check statement configured by ping or validationQuery according to configuration.

Recommended configuration: true is recommended if the network condition is poor, the program starts slowly, or sudden traffic occurs frequently.

Case one

KeepAlive=true

MinIdle=5

TimeBetweenEvictionRunsMillis=10000

MinEvictableIdleTimeMillis=100000

MaxEvictableIdleTimeMillis=100000

...

Can the above configuration connection survive successfully? No, because minEvictableIdleTimeMillis = = maxEvictableIdleTimeMillis, the connection is determined to exceed the maxEvictableIdleTimeMillis and needs to be discarded at the beginning of the test.

Case two

KeepAlive=true

MinIdle=5

TimeBetweenEvictionRunsMillis=10000

MinEvictableIdleTimeMillis=95000

MaxEvictableIdleTimeMillis=100000

...

Can the above configuration connection survive successfully? It is random, because maxEvictableIdleTimeMillis-minEvictableIdleTimeMillis < timeBetweenEvictionRunsMillis, it is possible that the Destroy thread detection task is not performed during this window, and there is no guarantee that the heartbeat will be performed.

1.4 maxActive

Maximum number of connection pools, maximum number of connections allowed to be in use at the same time. Here is a special nagging, do not be too happy to configure maxActive, although the configuration seems to be able to handle more requests after the business traffic soars, but switching to the DB perspective will find that in fact, the increase in the number of connections will reduce throughput in many scenarios. A very typical example is seconds kill. DB needs to add locks when updating hot spot data. At this time, allowing more connections to operate the DB is a bit like the rush of cars on the highway during the holidays, which will only add traffic to the DB.

Recommended configuration: 20. 20 is sufficient in most scenarios. Of course, this parameter is highly related to the usage scenarios, and is generally configured as 3-5 times the number of normal connections.

II. DB "slow check" investigation record

There are some configuration pits mentioned above, so whether everything will be all right according to the recommended configuration? in the real world, it is often not so simple. Let's share a case of "slow check" to learn about the train of thought of DB slow check.

Feedback from the application found that a large number of DB slow checks were found, and detailed execution time and SQL statements were recorded in the log. After receiving the problem, we immediately checked the DB and found no exception, no slow check record, and most of the SQL in the log can match the index, and the test execution is in millisecond level. So began to check whether the network is normal, whether there are packet loss, retransmission and other phenomena, query monitoring data found that it is also normal, and then packet capture analysis found that the actual request processing speed is very normal, so you can eliminate the DB problem.

So in-depth analysis, query DB can actually be divided into two stages: 1. Get the connection phase; 2. In the query phase, in most cases, the cost of obtaining a connection is very small, which can be obtained directly from the connection pool, even if a new connection is needed, so it is very easy to ignore the stage of obtaining a connection. Under what circumstances will it be a problem to get a connection? One situation is that it is slow to establish a connection, the other is that the connection pool has been exhausted, and then check against the above case, and then rule out these two cases in turn. Fortunately, the master was present and thought of using strace to track what was done before and after the SQL request. Finally, it was found that there was a log writing operation between the beginning and the end of recording the slow check log. The log writing operation here is synchronous and triggers another problem under certain circumstances, which causes the log writing to be very slow, and this log operation is encapsulated in the underlying layer, and even the business developer is not aware of such an operation. At this point, the truth came out, and after the problem of slow logging was finally fixed, there was no longer a similar "slow check".

This is the end of the article on "how to configure database connection pool". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.