Shulou(Shulou.com)06/02 Report--

This article focuses on "how to solve the problem of connection failure in dbcp connection pool". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the problem of connection failure in dbcp connection pool.

First, problems arise

A.log 's error log reported a connection problem with mysql, around 16:35

[2016-09-14 16 ms ago 35 Last packet sent to the server was 03.643] Nested exception is com.ibatis.common.jdbc.exception.NestedSQLException: [2016-09-14 16V 35Check the FriendsInvite.getLastInvited-InlineParameterMap 03.643]-The error occurred in sqlmaps/friend/FriendsInviteSQL.xml. [2016-09-14 16V 35V 03.643]-The error occurred while applying a parameter map. [2016-09-14 16V 35R 03.643]-Check the FriendsInvite.getLastInvited-InlineParameterMap. [2016-09-14 16V 35V 353.643]-Check the statement (query failed). [2016-09-14 16 Last packet sent to the server was 35 Last packet sent to the server was 03.643]-Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure [2016-09-14 1615 15 35 Communications link failure 03.643] [2016-09-14 16 15 15 35 ms ago 03.643] Last packet sent to the server was 8 ms ago. [2016-09-14 16 Switzerland 3515 351 03.643] at com.xx.core.dao.msroute.MasterSlaveDatasourceInteceptor.invoke (MasterSlaveDatasourceInteceptor.java:61) ~ [MasterSlaveDatasourceInteceptor.class:na] [ 2016-09-14 16 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed 35 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke 03.643] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:172) ~ [ReflectiveMethodInvocation.class:3.1.2.RELEASE] [2016-09-14 16 15 15 15 40 03.643] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke (JdkDynamicAopProxy.java:202) ~ [JdkDynamicAopProxy.class:3.1.2.RELEASE] [2016-09-14 16 15 15 15 315 03.643] at com.sun .proxy. $Proxy101.getLastInvited (Unknown Source) ~ [na:na] [2016-09-14 16 Proxy101.getLastInvited 35 at com.xx.user.friend.manager.FriendsManager.getLastInviter 03.643] at com.xx.user.friend.manager.FriendsManager.getLastInviter (FriendsManager.java:160) ~ [classes:na] [2016-09-14 16 14 35 at com.xx.user.friend.manager.FriendsManager.getLastInviter 03.643] at com.xx.user.friend.manager.FriendsManager$$FastClassByCGLIB$$1076743a.invoke () ~ [classes:na] [2016-09-14 16 at com.xx.user.friend.manager.FriendsManager.getLastInviter 3515 03.643] at net.sf .cglib.proxy.MethodProxy.invoke (MethodProxy.java:204) ~ [MethodProxy.class:na] [2016-09-14 16 Cglib2AopProxy.java:618 35Cglib2AopProxy.java:618 03.643] at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept (Cglib2AopProxy.java:618) ~ [Cglib2AopProxy$DynamicAdvisedInterceptor.class:3.1.2.RELEASE] [2016-09-14 16 Cglib2AopProxy$DynamicAdvisedInterceptor.class:3.1.2.RELEASE] at com.xx.user.friend.manager.FriendsManager$$EnhancerByCGLIB$$8999470e.getLastInviter () ~ [classes:na] [2016-09-14 16: 35 at com.xx.user.controller.newm.NewUserMobileAPIController.getNewTipMsg 03.643] at com.xx.user.controller.newm.NewUserMobileAPIController.getNewTipMsg (NewUserMobileAPIController.java:1977) ~ [classes:na] [2016-09-14 16 at com.xx.user.controller.newm.NewUserMobileAPIController.getNewTipMsg 35 Unknown Source] at sun.reflect.GeneratedMethodAccessor104.invoke (Unknown Source) ~ [na:na] [2016-09-14 16 V 35 at com.xx.user.controller.newm.NewUserMobileAPIController.getNewTipMsg 03.643] at sun.reflect.DelegatingMethodAccessorImpl.invoke (DelegatingMethodAccessorImpl.java:43) ~ [na:1.7.0_71] [2016-09- 14 16 na:1.7.0_71 35 na:1.7.0_71 03.643] at java.lang.reflect.Method.invoke (Method.java:606)

Then the same anomalies were reported again and again, and the time interval was increasing, and it was 12175 seconds by 19:35.

[2016-09-14 19 The last packet successfully received from the server was12175 seconds ago.The last packet sent successfully to the server was12175 seconds ago 35 which is longer than the server configured value of 52.934]-Cause: The last packet successfully received from the server was12175 seconds ago.The last packet sent successfully to the server was12175 seconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException: 2. Analyze and troubleshoot 1. View existing configuration parameters.

At first it was thought that only a database had a problem, but later found that b database also reported this problem. The judgment should be that the connection in the connection pool has failed.

(1) configuration of dbcp connection pool; (2) configuration of mysql database

The timeout on the dba side of the mysql database is set to 1800 (30 minutes) seconds. If a connection is not used for 1800 seconds, the database will be broken.

2. Interpretation of dbcp parameters:

TestOnBorrow=false means that connections in the connection pool are not detected, which is also recommended. If set to true, it will affect the performance of the database.

TestWhileIdle=true indicates whether the connection is verified by the free connection collector, and if the test fails, the connection is removed from the pool and used in conjunction with timeBetweenEvictionRunsMillis.

ValidationQuery=select 1 SQL query, used to verify connections taken from the connection pool and valid connections, must be a SQL SELECT and must return at least one row of records.

The time timeBetweenEvictionRunsMillis=100000 sleeps while the idle connection recycler thread is running, in milliseconds, that is, the idle connection is detected every 100 seconds, and the detection statement is validationQuery.

MinEvictableIdleTimeMillis=600000 connections remain idle in the pool without being idle connection recycler threads, that is, idle connections are saved for 600 seconds (10 minutes)

Feel no problem, every 100 seconds will do idle thread detection, the application detection time of 600s is also less than the database set time of 1800 seconds, why is there a connection failure exception? And the reported connection failure time is getting longer and longer.

3. Analysis

Looking at the dbcp source code, you can see that there is also a parameter numTestsPerEvictionRun, the number of connections checked each time the idle connection recycler thread runs, which configures the number of IDL threads detected every 100 seconds. We did not configure this parameter, but took the default value.

Protected int numTestsPerEvictionRun = 3

The source code is configured with 3, that is, only 3 idle threads are detected by default.

According to the worst-case scenario, the largest idle thread is 800. it takes 3 idle threads every 100s to judge the state of all IDL threads (800x3) * 100s = 26666 seconds, while the database timeout is 1800 seconds, which means that if the pool is full (or there are a large number of connections in the pool), by the time all idle threads are detected, there is a high probability that the application will get the connection that has failed. The earliest timeout connection should be 30 minutes later, that is, 1800 seconds of database configuration.

4. The cause

Look at the access log of nginx. At that time, there was no such a large number of sudden visits that would fill up the connection pool (or increase a lot of connections in the pool), and the number of visits was normal.

(1) there is a restart operation for the application around 16:00, which is not supposed to happen. It is often restarted later before, and the number of connection pools will not suddenly increase.

(2) think of an operation that checked the running status of activeMq at about 16:05. When you hit jstack, activeMq hung up and restarted. At this time, the amount of application is increased, and the number of threads is increased.

(3) the first entry of the exception log is around 16:35, which is basically in line with this phenomenon. The shortest failure thread is 1800 seconds configured in the database, and the failure was used exactly half an hour later, and the above exception was reported.

5. Solve

The recommended configuration on the Internet is to set numTestsPerEvictionRun=maxIdle, so that all the failed idle can be removed from the thread pool at one time, avoiding the problem that the connection of the connection pool has failed after a peak.

In view of the fact that the invalid connection will be slowly removed, no configuration modification is made at that time, and then the numTestsPerEvictionRun parameter will be configured to the same value as maxIdle, which can avoid this problem.

At this point, I believe you have a deeper understanding of "how to solve the problem of connection failure in dbcp connection pool". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.