You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by sinoea kaabi <si...@msn.com> on 2008/09/16 11:23:54 UTC
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();
try {
final Statement statement = connection.createStatement();
try {
final ResultSet results = statement.executeQuery(sql);
try {
final Collection<Branch> branches = new LinkedList<Branch>();
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;
} 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/
Re: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
Posted by Johnny Kewl <jo...@kewlstuff.co.za>.
----- Original Message -----
From: "Johnny Kewl" <jo...@kewlstuff.co.za>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Tuesday, September 16, 2008 5:17 PM
Subject: Re: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
>
> ----- Original Message -----
> From: "sinoea kaabi" <si...@msn.com>
> To: "Tomcat Users List" <us...@tomcat.apache.org>
> Sent: Tuesday, September 16, 2008 5:10 PM
> Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
> increasing....
>
>
>
>>Yes, as I said in the first post, that I have checked through all the
>>code,
>>and I am closing all the connections (in a finally block) after they have
>>been used.
>
>
>>final Connection connection = datasource.getConnection();
>>try {
>
> ============================
> BUT you have a
> **** return branches; ****
> HERE
>
>
> It can never get to HERE
>
> Just put the return after the finally....
> ============================
>>blah .. blah
>>} finally {
>> connection.close();
>>}
sinoea
in the context of exceptions.... finally is always run... exception or
not...
BUT...
you still have to let the program get there....
... you returning too early.... the connections are not closing...
Have fun...
---------------------------------------------------------------------------
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
Posted by Johnny Kewl <jo...@kewlstuff.co.za>.
----- Original Message -----
From: "Brantley Hobbs" <sb...@uga.edu>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Tuesday, September 16, 2008 5:27 PM
Subject: Re: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
> "return" statements do not prevent the finally block from executing:
>
> http://java.sun.com/docs/books/tutorial/essential/exceptions/finally.html
I stand corrected...
Always understood as "during exception handling"... but you right it seems
I just cant bring myself to write code like that... feels un-natural...
I would still put return after the close... apologies...
---------------------------------------------------------------------------
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
Posted by Brantley Hobbs <sb...@uga.edu>.
"return" statements do not prevent the finally block from executing:
http://java.sun.com/docs/books/tutorial/essential/exceptions/finally.html
B.
Johnny Kewl wrote:
>
> ----- Original Message ----- From: "sinoea kaabi" <si...@msn.com>
> To: "Tomcat Users List" <us...@tomcat.apache.org>
> Sent: Tuesday, September 16, 2008 5:10 PM
> Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections
> keep increasing....
>
>
>
>> Yes, as I said in the first post, that I have checked through all the
>> code,
>> and I am closing all the connections (in a finally block) after they
>> have been used.
>
>
>> final Connection connection = datasource.getConnection();
>> try {
>
> ============================
> BUT you have a
> **** return branches; ****
> HERE
>
>
> It can never get to HERE
>
> Just put the return after the finally....
> ============================
>> blah .. blah
>> } finally {
>> connection.close();
>> }
>
>> Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections
>> keep increasing....
>> Date: Tue, 16 Sep 2008 11:02:46 -0400
>> From: barry.l.propes@citi.com
>> 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:sinoea@msn.com]
>> 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: barry.l.propes@citi.com
>>> 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:sinoea@msn.com]
>>> 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: users-unsubscribe@tomcat.apache.org
>>> For additional commands, e-mail: users-help@tomcat.apache.org
>>>
>>
>> _________________________________________________________________
>> 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: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To start a new topic, e-mail: users@tomcat.apache.org
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>
> _________________________________________________________________
> 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: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
Posted by Johnny Kewl <jo...@kewlstuff.co.za>.
----- Original Message -----
From: "sinoea kaabi" <si...@msn.com>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Tuesday, September 16, 2008 5:10 PM
Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
>Yes, as I said in the first post, that I have checked through all the code,
>and I am closing all the connections (in a finally block) after they have
>been used.
>final Connection connection = datasource.getConnection();
>try {
============================
BUT you have a
**** return branches; ****
HERE
It can never get to HERE
Just put the return after the finally....
============================
>blah .. blah
>} finally {
> connection.close();
>}
> Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
> increasing....
> Date: Tue, 16 Sep 2008 11:02:46 -0400
> From: barry.l.propes@citi.com
> 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:sinoea@msn.com]
> 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: barry.l.propes@citi.com
>> 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:sinoea@msn.com]
>> 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: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>
> _________________________________________________________________
> 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: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
_________________________________________________________________
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
Posted by "Propes, Barry L " <ba...@citi.com>.
Yeah, that should be closing it....would you be establishing the
connection(s) anywhere else though?
And do you have a ResultsSet that you're leaving open? You would need to
close that, too.
Same for any prepared or callable statement as well.
-----Original Message-----
From: sinoea kaabi [mailto:sinoea@msn.com]
Sent: Tuesday, September 16, 2008 10:10 AM
To: Tomcat Users List
Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections
keep increasing....
Yes, as I said in the first post, that I have checked through all the
code, and I am closing all the connections (in a finally block) after
they have been used.
final Connection connection = datasource.getConnection();
try {
...
..
blah .. blah
} finally {
connection.close();
}
> Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections
keep increasing....
> Date: Tue, 16 Sep 2008 11:02:46 -0400
> From: barry.l.propes@citi.com
> 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:sinoea@msn.com]
> 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: barry.l.propes@citi.com
>> 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:sinoea@msn.com]
>> 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: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>
> _________________________________________________________________
> 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: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
_________________________________________________________________
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
Posted by sinoea kaabi <si...@msn.com>.
Yes, as I said in the first post, that I have checked through all the code,
and I am closing all the connections (in a finally block) after they have been used.
final Connection connection = datasource.getConnection();
try {
...
..
blah .. blah
} finally {
connection.close();
}
> Subject: RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
> Date: Tue, 16 Sep 2008 11:02:46 -0400
> From: barry.l.propes@citi.com
> 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:sinoea@msn.com]
> 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: barry.l.propes@citi.com
>> 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:sinoea@msn.com]
>> 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: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>
> _________________________________________________________________
> 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: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
_________________________________________________________________
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
Posted by "Propes, Barry L " <ba...@citi.com>.
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:sinoea@msn.com]
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: barry.l.propes@citi.com
> 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:sinoea@msn.com]
> 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: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
_________________________________________________________________
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
Posted by sinoea kaabi <si...@msn.com>.
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: barry.l.propes@citi.com
> 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:sinoea@msn.com]
> 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: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
_________________________________________________________________
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
Posted by "Propes, Barry L " <ba...@citi.com>.
Sounds like you're not explicitly killing off the connections you set in
the first place.
-----Original Message-----
From: sinoea kaabi [mailto:sinoea@msn.com]
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]
<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();
try {
final Statement statement = connection.createStatement();
try {
final ResultSet results = statement.executeQuery(sql);
try {
final Collection<Branch> branches = new
LinkedList<Branch>();
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
Posted by "Propes, Barry L " <ba...@citi.com>.
Yeah, it sounds that way....almost like passing off one query to another
servlet or page, and then the leak occurs.
Do you have a monitoring tool that you can see the connections increase?
-----Original Message-----
From: Johnny Kewl [mailto:john@kewlstuff.co.za]
Sent: Tuesday, September 16, 2008 8:37 AM
To: Tomcat Users List
Subject: Re: Tomcat 5.5, JNDI Connection Pooling, Active connections
keep increasing....
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" <si...@msn.com>
To: <us...@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("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; // 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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
Posted by Johnny Kewl <jo...@kewlstuff.co.za>.
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" <si...@msn.com>
To: <us...@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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Sinoea,
Oh, and I always always /always/ set my connection pool size to a fixed
size of 1 (yes, a single connection) in development. This can help find
places where you are requesting two connections from the pool by a
single thread, which exposes you to a deadlock scenario.
If you want to test if your logAbandoned logging is working, here's a
simple JSP that will leak a connection for you ;)
- -chris
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<%@page language="Java"
isErrorPage="false"
import="
java.sql.*,
java.util.*,
java.io.PrintWriter,
javax.naming.Context,
javax.naming.InitialContext,
javax.naming.NamingException,
javax.sql.DataSource
"
%>
<%!
/**
* Gets a JDBC connection. This implementation uses JNDI to obtain a
* connection. Feel free to substitute your own.
*/
Connection getConnection()
throws SQLException, javax.naming.NamingException
{
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("(your JNDI name)");
if(null == ds)
throw new NamingException("Cannot obtain DataSource");
return ds.getConnection();
}
%>
<%
Connection conn = getConnection();
%>
<html>
<body>
<p>Got connection: <%= conn %></p>
<p>Now, I refuse to give it away!</p>
</body>
</html>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkjReDoACgkQ9CaO5/Lv0PC/FwCfZPybusC0jzBeKYoD93xMyTbI
3XUAn1HUJWUjrfoZipIVXubV7MqPYPB/
=qfKA
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tomcat 5.5, JNDI Connection Pooling, Active connections keep
increasing....
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Sinoea,
sinoea kaabi wrote:
> } finally {
> results.close();
> }
>
> } finally {
> statement.close();
> }
>
> } finally {
> connection.close();
> }
I typically put this all together so I don't have too many try/catch
blocks when they're really not required:
Connection conn = null;
Statement statement = null;
ResultSet results = null;
try
{
...
}
catch (SQLException ...)
{
...
}
... other exceptions ...
finally
{
if(null != results)
try { results.close(); } catch (SQLException sqle)
{ ... log exception ... }
if(null != statement)
try { statement.close(); } catch (SQLException sqle)
{ ... log exception ... }
if(null != connection)
try { connection.close(); } catch (SQLException sqle)
{ ... log exception ... }
}
Remember that it's important to put try/catch blocks around the "close"
invocations -- and make sure to log any errors you get. Otherwise, a
SQLException from closing your connection could mask a more serious
exception occurring elsewhere.
> removeAbandoned="true"
> removeAbandonedTimeout="60"
> logAbandoned="true"
You might also want to set:
validationQuery="SELECT 1"
Are you not seeing any log messages about abandoned connections?
"logAbandoned" should be enabling that.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkjRdsUACgkQ9CaO5/Lv0PDz5QCfXKQp7Koz/OFmEZm68exHTxFV
YMAAn2EPmXYtrS+eHFGx39Bp90TX4lOK
=8euf
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org