sinoea I dont use the JNDI pools, but I've marked a possible issue below...
Dont think its getting to finally.... guess ;)
On the dB pools I use... connections will not increase... unless that many
threads are used at same time..
ie the connections represent a max activity level... otherwise it wont
increase...
... why it resets at 37 I dont know, but I think you are leaking
connections...
----- Original Message -----
From: "sinoea kaabi" <[EMAIL PROTECTED]>
To: <users@tomcat.apache.org>
Sent: Tuesday, September 16, 2008 11:23 AM
Subject: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
Dear all,
I seem to have problems with connection pooling.
I have tried so many ways, before I use to get the exhausted scenario, where
there
were no connections left to use, and now I am getting a different problem.
I have been digging in this issue for too long, and I am not sure if I
understand the depth of the connection pooling concept.
I have set the max active connections to 40.
My active connections keep increasing, they never seem to return back to the
pool,
eventhough when no-one is visiting the site.
(Well, I have had up to 3 idle connections and that is the most I have ever
had)
After a few days, the active connections reach to 37, and then afterwards
the active connections
are reset to 0.
It basically starts from 0 to 37 and then again 0 to 37, and so on....
My understanding is that:
1. An active connection is a connection that is currently used, and not
yet returned back to the pool
2. An active connection will be returned back to the pool
straight after its usage and become an idle connection
The active connection is returned back to the pool as soon as you
call the connection.close() method (assuming that you have configured for
connection pooling)
3. An idle connection can only be idle for an X amount of time and then
it will be removed from the pool and get destroyed
4. An idle connection will become an active connection when it is
required and then returned back to the pool as an idle connection
when calling connection.close()
----------------------------------------------------------------------------
If that is all correct then why do my active connections keep increasing?
----------------------------------------------------------------------------
Am I closing all the connections?
Well, I have checked every single line of code, and yes I am closing
result sets, statements and connections in a finally block:
[code]
} finally {
results.close();
}
} finally {
statement.close();
}
} finally {
connection.close();
}
[/code]
Please have a look at my code and configuration below:
My environment:
JDK 1.5.0_12
Tomcat 5.5.27
MySQL 5
My Web apps context.xml under the META-INF folder:
[code]
<Context>
<Resource
name="jdbc/myDB"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
auth="Container"
type="javax.sql.DataSource"
maxActive="40"
maxIdle="10"
maxWait="15000"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
username="username"
password="password"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mydb" />
</Context>
[/code]
My Host configuration in server.xml
[code]
<Host name="www.mysite.com" deployOnStartup="true" debug="0"
appBase="webapps/mysite" unpackWARs="true" autoDeploy="false"
xmlValidation="false" xmlNamespaceAware="false">
<Valve
className="org.apache.catalina.valves.FastCommonAccessLogValve"
prefix="mysite_access_log."
suffix=".txt"
pattern="common"
directory="C:/Program Files/Apache Software Foundation/Tomcat
5.5/webapps/mysite/logs"/>
<Alias>mysite.com</Alias>
</Host>
[/code]
Here is the class that I use the get the datasource
[code]
import...
public class Data {
private static final Logger SQL = Logger.getLogger("sql");
private static final Logger DATASOURCE = Logger.getLogger("datasource");
private static final Logger MANY_CONNECTIONS =
Logger.getLogger("manyconnections");
private static BasicDataSource ds = null;
public static DataSource getDataSource() throws SQLException {
if (ds == null) {
DATASOURCE.info("DataSource is NULL ");
MANY_CONNECTIONS.info("DataSource is NULL ");
try {
final Context initContext = new InitialContext();
ds =
(BasicDataSource)initContext.lookup("java:/comp/env/jdbc/myDB");
initContext.close();
logDataSource(ds);
return ds;
} catch (final NamingException e) {
e.printStackTrace();
throw new RuntimeException("Java naming exception when
getting connection from tomcat pool: " + e.getMessage());
}
} else {
logDataSource(ds);
return ds;
}
}
/**
* Logs the datasource.
* @param ds
*/
private static void logDataSource(final BasicDataSource ds) {
DATASOURCE.info("The max active connections are : " +
ds.getMaxActive());
DATASOURCE.info("The max idle connections are : " +
ds.getMaxIdle());
DATASOURCE.info("The max wait is : " + ds.getMaxWait());
DATASOURCE.info("The max opening prepared statements are : " +
ds.getMaxOpenPreparedStatements());
DATASOURCE.info("The number of active connections are : " +
ds.getNumActive());
DATASOURCE.info("The number of idle connections are : " +
ds.getNumIdle());
DATASOURCE.info("\n====================================\n");
if (ds.getNumActive() >= 20 || ds.getNumIdle() >= 10) {
MANY_CONNECTIONS.info("The max active connections are : " +
ds.getMaxActive());
MANY_CONNECTIONS.info("The max idle connections are : " +
ds.getMaxIdle());
MANY_CONNECTIONS.info("The max wait is : " + ds.getMaxWait());
MANY_CONNECTIONS.info("The max opening prepared statements are :
" + ds.getMaxOpenPreparedStatements());
MANY_CONNECTIONS.info("The number of active connections are : "
+ ds.getNumActive());
MANY_CONNECTIONS.info("The number of idle connections are : " +
ds.getNumIdle());
MANY_CONNECTIONS.info("\n====================================\n");
}
}
/**
* Checks if a connection is open or closed and logs the results.
* @param connection
* @param string
*/
public static void logConnection(final Connection connection, final
String string) {
try {
if (!connection.isClosed()) {
DATASOURCE.info("The connection is still open >> " +
string);
MANY_CONNECTIONS.info("The connection is still open >> " +
string);
} else {
DATASOURCE.info("The connection is CLOSED >> " + string);
MANY_CONNECTIONS.info("The connection is CLOSED >> " +
string);
}
} catch (final SQLException e) {
e.printStackTrace();
DATASOURCE.info(e.getMessage());
MANY_CONNECTIONS.info(e.getMessage());
}
}
}
[/code]
And yes, I am closing all the connections (I have checked everywhere, I
should be right) ,
here is a typical code example of how I use JDBC:
[code]
final Collection<Branch> branches =
BranchData.loadBranches(Data.getDataSource(), 1);
[/code]
[code]
import ...
public class BranchData {
private static final Logger LOG = Logger.getLogger(BranchData.class);
private static final Logger SQL = Logger.getLogger("sql");
/**
* Loads a collection of branches for the specified shop ID.
* @param datasource
* @param shopid
* @return Returns a collection of branches for the specified shop ID;
* or an <code>empty</code> collection if none are found.
* @throws SQLException
*/
public static Collection<Branch> loadBranches(final DataSource
datasource, final int shopid) throws SQLException {
final String sql = "select * from branch where fk_shop_id = " +
shopid;
SQL.info(sql);
final Connection connection = datasource.getConnection();
*********************************************************
final Collection<Branch> branches = new LinkedList<Branch>(); // to
here
try {
final Statement statement = connection.createStatement();
try {
final ResultSet results = statement.executeQuery(sql);
try {
********************************************************
//final Collection<Branch> branches = new
LinkedList<Branch>(); //out
while (results.next()) {
final Branch branch = new Branch();
branch.setId(results.getInt("pk_branch_id"));
branch.setName(results.getString("branchname"));
branch.setPhone(results.getString("phone"));
branch.setFax(results.getString("fax"));
branch.setDescription(results.getString("description"));
branch.setPostcode(results.getString("postcode"));
branch.setAddressline1(results.getString("addressline1"));
branch.setAddressline2(results.getString("addressline2"));
branch.setCity(results.getString("city"));
branch.setCounty(results.getString("county"));
branch.setCountry(results.getString("country"));
branch.setVatnumber(results.getString("vatnumber"));
branch.setMinimumdeliveryorder(results.getBigDecimal("minimumdeliveryorder"));
branch.setLogo(results.getString("logo"));
branch.setHoldtime(results.getInt("holdtime"));
branch.setSmtphost(results.getString("smtphost"));
branch.setEmailusername(results.getString("emailusername"));
branch.setEmailpassword(results.getString("emailpassword"));
branch.setEmailfrom(results.getString("emailfrom"));
branch.setEmailorder(results.getString("emailorder"));
branch.setPaymentValue(results.getBigDecimal("paymentvalue"));
branch.setPaymentOption(results.getString("paymentoption"));
branch.setShopid(results.getInt("fk_shop_id"));
branches.add(branch);
}
************************
///return branches; // move this out of the try catch
} finally {
results.close();
}
} finally {
statement.close();
}
} finally {
connection.close();
Data.logConnection(connection, "BranchData [loadBranches(final
DataSource datasource, final int shopid) throws SQLException]");
}
************************
return branches; // Somewhere here
}
}
[/code]
Seriously, I need help....
---------------------------------------------------------------------------
HARBOR : http://www.kewlstuff.co.za/index.htm
The most powerful application server on earth.
The only real POJO Application Server.
See it in Action : http://www.kewlstuff.co.za/cd_tut_swf/whatisejb1.htm
---------------------------------------------------------------------------
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
