Three key parameters for the Tomcat connection pool (for handling connection pool full issues)

three important parameters for Tomcat connection pool

The three key parameters of the Tomcat connection pool, the configuration of the connection pool is very important, the configuration is not easy to cause the operation of the program is stable, the following common problems summed up under:

A. If set to true Tomcat automatically checks for recovery reuse and does not have a properly closed connection. (default is False)
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
B. Set the connection that is considered to be discarded in the number of seconds it is to be used for recovery.
<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>
C. Output recycled log, you can print out the exception in detail so that there was a leak
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>

Experimental environment, Tomcat Configuration connection pool, the maximum number of connections is 5.
The code is as follows:

<parameter>
<name>maxActive</name>
<value>5</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>1</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>20000</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
Experiment with the following code (each time the connection is not closed):

try {
Connection con = Getjdbcdao (). Getdatasource (). getconnection ();
ResultSet rs = con.createstatement (). ExecuteQuery ("select * from K_ms ... B_mspbxx ");
while (Rs.next ()) {
System.out.println (rs.getstring (1));
}
catch (SQLException e) {
E.printstacktrace ();
}

    When this is done 5 consecutive times, the backend reports a full connection pool error:
2008-09-06 14:31:02,471 [org.hibernate.util.JDBCExceptionReporter ]-[warn] SQL error:0, sqlstate:null
2008-09-06 14:31:02,471 [Org.hibernate.util.jdbcexceptionreporter]-[error] Cannot get a connection, pool exhausted
2008-09-06 14:31:02,580 [Org.hibernate.util.jdbcexceptionreporter]-[warn] SQL error:0, Sqlstate:null
2008-09-06 14:31:02,580 [org.hibernate.util.jdbcexceptionreporter]-[error] Cannot get a Connection, pool exhausted
    check the database connection according to the following script

DECLARE cur_spid cursor
For
Select spid from sysprocesses where ipaddr= ' 172.16.16.145 ' and program_name <> ' Sql_advantage '
Go
DECLARE @spid Integer
Open Cur_spid
Fetch cur_spid into @spid
While @ @sqlstatus =0
Begin
print '%1! ', @spid
DBCC TRACEON (3604)
DBCC SQLTEXT (@spid)
Fetch cur_spid into @spid
End
Close Cur_spid

Get five records similar to the following: 5 Connections not released

Reference:
95
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
SQL Text:select * from K_ms. B_mspbxx
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
If you continue to execute the code, the background will report the following error, indicating where the code does not close the connection (already specific to that line of code to get the connection is not closed, this is important ... The following red color callout's exception point information is the specific connection does not release the code information.

DBCP object created 2008-09-06 14:27:32 by the following code is never closed:
Java.lang.Exception
At Org.apache.commons.dbcp.AbandonedTrace.init (abandonedtrace.java:96)
At Org.apache.commons.dbcp.abandonedtrace.<init> (abandonedtrace.java:79)
At Org.apache.commons.dbcp.delegatingresultset.<init> (delegatingresultset.java:71)
At Org.apache.commons.dbcp.DelegatingResultSet.wrapResultSet (delegatingresultset.java:80)
At Org.apache.commons.dbcp.DelegatingStatement.executeQuery (delegatingstatement.java:205)
At Com.thunisoft.fy.spxt.SpxtBaseLogic.createPbxx (spxtbaselogic.java:5772)
If the time exceeds the time set by Removeabandonedtimeout, then using the above SQL script directly to view the database connection discovery has been released, and if another database operation has not reported the connection pool full problem. After 60 seconds, Tomcat releases the connections that it believes are not released.

And then the same Java code, after the three parameters of the Tomcat connection pool are removed, the connection pool is full after 5 executions, but the new connection is not available for execution again, and the log in the background is the full information of the connection pool, and no exception information is released for the connection without the specific line of code.

Because the actual environment of Daxing is connected with the pool full of problems (basically two days), ready to put the three parameters in the actual environment to try and then look at the background log, the first is to want to specify where the code is not released, the second is if you set the Removeabandonedtimeout parameters, You can avoid problems where the connection is not released. Of course, personally think that the final deployment environment of phase Three code does not require this parameter, in the program to release the connection is the most fundamental solution to the problem, and also do not know what these three parameters will affect Tomcat performance, should not be small.