Hi,
We're using a connection pool based on a DBCP 1.0 BasicDataSource, set up through our
Tomcat 4.0 server.xml file. Every so often the retrieval of connections from the pool
slows down to a snail's pace. We added some trace to the code which gets and releases
connections to time how long it takes to get a connection, and also to see how many
connections are active/idle from moment to moment.
The retrieval of connections sometimes takes far longer than the maxWait parameter
setting. That's a question in itself, but the more interesting thing is that at those
times the trace code indicates that there are plenty of connections idle (>150 out of
a pool of 275).
I've read accounts of people managing very large MySQL connection pools with DBCP and
BasicDataSource, even needing to use the autoReconnect parameter to restart the
connections after eight hours. We're lucky to get three hours out of the app before
needing to restart.
I would be the first to blame a simple resource leak, ie connections not being
properly closed; but the fact that the number of idle connections is so high suggests
that something else is afoot. What are we doing wrong?
Thanks in advance,
Jerry Gaines
PS Relevant excerpt from server.xml ResourceParams defining the data source. The
validation query "SELECT 1" is probably not ANSI SQL, but it works in the version of
MySQL we're using.
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>270</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>250</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>SELECT 1</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>120</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>org.gjt.mm.mysql.Driver</value>
</parameter>
