Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
Date: Tue, 16 Sep 2008 11:02:46 -0400
From: [EMAIL PROTECTED]
To: users@tomcat.apache.org
At the end of the servlet or JSP or whichever, you need to kill off
connections created that you establish.
-----Original Message-----
From: sinoea kaabi [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 16, 2008 9:56 AM
To: Tomcat Users List
Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections
keep increasing....
How exaclt do you mean?
Anywhere in my code where you have seen that?
Thanks!
Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections
keep increasing....
Date: Tue, 16 Sep 2008 10:26:03 -0400
From: [EMAIL PROTECTED]
To: users@tomcat.apache.org
Sounds like you're not explicitly killing off the connections you set
in the first place.
-----Original Message-----
From: sinoea kaabi [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 16, 2008 4:24 AM
To: users@tomcat.apache.org
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]
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" />
[/code]
My Host configuration in server.xml
[code]
appBase="webapps/mysite" unpackWARs="true" autoDeploy="false"
xmlValidation="false" xmlNamespaceAware="false">
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"/> mysite.com
[/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 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 empty collection if none are found.
* @throws SQLException
*/
public static Collection 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();
try {
final Statement statement = connection.createStatement();
try {
final ResultSet results = statement.executeQuery(sql);
try {
final Collection branches = new
LinkedList();
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("minimumdeliveryord
er"));
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;
} finally {
results.close();
}
} finally {
statement.close();
}
} finally {
connection.close();
Data.logConnection(connection, "BranchData
[loadBranches(final DataSource datasource, final int shopid) throws
SQLException]");
}
}
}
[/code]
Seriously, I need help....
-------------------------------------
_________________________________________________________________
Make a mini you and download it into Windows Live Messenger
http://clk.atdmt.com/UKM/go/111354029/direct/01/
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
_________________________________________________________________
Win New York holidays with Kellogg's & Live Search
http://clk.atdmt.com/UKM/go/111354033/direct/01/
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
