Hello, my application in production is having intermittent performance
problems. My app shows db queries are taking too long. The db team says
the database is running fine. The network is between us and it has had
problems in the past.
We saw this pool exhausted exception at one point.
Can someone give me some insight to this message? size is 100, busy is 66,
what are the other 44 connections doing?
Message:
org.apache.tomcat.jdbc.pool.PoolExhaustedException: [qtp334173295-1099]
Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none
available[size:100; busy:66; idle:0; lastwait:30000].
Config:
<!-- database configuration -->
<!-- depends on ecpProperties but you could put dbHost etc in a prop
file and delete ecpProperties-->
<bean id="ecpDataSource"
class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close"
primary="true">
<property name="name" value="ecp_stage"/>
<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@//${db.host}:1521/${db.sid}"/>
<property name="username" value="${db.user}"/>
<property name="password" value="${db.password}"/>
<property name="maxWait" value="30000"/>
<property name="testOnReturn" value="true"/>
<property name="testWhileIdle" value="true"/>
<property name="defaultAutoCommit" value="false"/>
<property name="defaultTransactionIsolation" value="2"/>
<property name="connectionProperties"
value="oracle.jdbc.ReadTimeout=7200000"/>
<property name="initialSize" value="50"/>
<property name="maxActive" value="100"/>
<property name="maxIdle" value="75"/>
<property name="minIdle" value="50"/>
<!-- Pool sweeper is enabled if timeBetweenEvictionRunsMillis > 0 AND
removeAbandoned = true AND removeAbandonedTimeout > 0. Then pool
sweeper runs every X millis-->
<property name="timeBetweenEvictionRunsMillis" value="30000"/>
<!-- Pool will shrink to minIdle if connection is idle for X millis -->
<property name="minEvictableIdleTimeMillis" value="60000"/>
<!-- Before we use connection, validate it -->
<property name="testOnBorrow" value="true"/>
<!-- Query used to test connection -->
<property name="validationQuery" value="SELECT 1 FROM dual"/>
<!-- Avoid excess validation, only run validation at most at this
frequency - time in milliseconds.
If a connection is due for validation, but has been validated
previously within this interval,
it will not be validated again. The default value is 30000 (30
seconds). The lower the number,
the more validation is done, performance is impacted-->
<property name="validationInterval" value="30000"/>
<!-- If you set suspectTimeout, do not set Remove Abandoned Section -->
<!-- Works in the exact same way as the removeAbandonedTimeout except that
instead of closing the connection, it simply logs a warning and
issues a JMX notification
with the information. This way, you can find out about these
leaks or long running queries
without changing the behavior of your system.-->
<!--<property name="suspectTimeout" value="60"/>-->
<!-- If you set maxAge, do not set Remove Abandoned Section -->
<!--Defines the time in milliseconds that a connection can be
open/established.
When a connection is returned to the pool, if the connection
has been connected
and the time it was first connected is longer than the
maxAge value, it will be closed -->
<!-- Production firewall closes connections at 30 minutes, so
maxAge is 15 min -->
<property name="maxAge" value="900000"/>
<!-- Removed Abandoned Section start -->
<!-- Detect leaked connections -->
<property name="removeAbandoned" value="false"/>
<!-- Time from connection creation to when it is considered abandoned -->
<property name="removeAbandonedTimeout" value="60"/>
<!-- Log that a connection was abandoned. Impacts performance poorly -->
<property name="logAbandoned" value="false"/>
<!-- Connection must meet the threshold removeAbandonedTimeout AND
the number of open connections must exceed the percentage of this value -->
<property name="abandonWhenPercentageFull" value="50"/>
<!-- Reset the timeout timer when the connection is still being used.
Useful for long running batch job using a single connection -->
<!-- For troubleshooting set SlowQueryReport, but performance is impacted -->
<property name="jdbcInterceptors" value="ResetAbandonedTimer"/>
<!-- Removed Abandoned Section end -->
</bean>
