Questions

Cannot get a connection, pool error Timeout waiting for idle object

Why

The maxwait configured in db.properties is the maximum waiting time for the connection pool to obtain the MySQL connection. This error will be reported if it is not obtained after this time

After in-depth analysis of why you can’t get the MySQL connection, you can use it manually to send browser requests. It is found that when the connection reaches maxactive, you can’t get it, and the remaining 50 are in sleep state. It turns out that they are not recycled after being used up

Reason 1

The application does not close the connection after use

Option 1:

Please check whether your application has closed the database connection correctly. Be sure to close it in finally（ Not recommended. Let the database connection pool manage the connections.)

Option 2:

If it is really impossible to find out which code has not closed the database connection, you can configure the parameters to complete the automatic recovery, and record the recovery log, so as to locate the problem code; The configuration parameters of connection pool in Tomcat are: removeabandoned, removeabandoned timeout and logabandoned（ (recommended)

#The SQL statement used for validation
validationQuery SELECT 1
#Connections in the pool are reclaimed after 30 minutes of inactivity
minEvictableIdleTimeMillis 18000
#Run the idle connection collector every 30 seconds
timeBetweenEvictionRunsMillis 10000
#Don't test when lending connections, otherwise it will affect performance
testOnBorrow false
testWhileIdle true

#Whether connections in the program are reclaimed by the connection pool after they are not used
#DBCP 2.0.1 (this version should use removeAbandonedOnMaintenance and removeAbandonedOnBorrow)
#removeAbandoned=true
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
#How long a database connection has been unused will be considered abandoned and retrieved from the connection pool (in seconds). (configured for 30 seconds only to match the test program) 
removeAbandonedTimeout=30

MySQL waiting time should be set to 8 hours, and all connections should be managed by connection pool

DBCP website: https://commons.apache.org/proper/commons-dbcp/configuration.html

Reason 2

The application pressure is too high to get idle connection

Option 1:

In this case, we can adjust the capacity and timeout limit of connection pools such as maxactive, maxidle and maxwait to get larger connection pool capacity and waiting time