Sounds like you're not explicitly killing off the connections you set in the first place.
-----Original Message----- From: sinoea kaabi [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2008 4:24 AM To: users@tomcat.apache.org Subject: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing.... Dear all, I seem to have problems with connection pooling. I have tried so many ways, before I use to get the exhausted scenario, where there were no connections left to use, and now I am getting a different problem. I have been digging in this issue for too long, and I am not sure if I understand the depth of the connection pooling concept. I have set the max active connections to 40. My active connections keep increasing, they never seem to return back to the pool, eventhough when no-one is visiting the site. (Well, I have had up to 3 idle connections and that is the most I have ever had) After a few days, the active connections reach to 37, and then afterwards the active connections are reset to 0. It basically starts from 0 to 37 and then again 0 to 37, and so on.... My understanding is that: 1. An active connection is a connection that is currently used, and not yet returned back to the pool 2. An active connection will be returned back to the pool straight after its usage and become an idle connection The active connection is returned back to the pool as soon as you call the connection.close() method (assuming that you have configured for connection pooling) 3. An idle connection can only be idle for an X amount of time and then it will be removed from the pool and get destroyed 4. An idle connection will become an active connection when it is required and then returned back to the pool as an idle connection when calling connection.close() ------------------------------------------------------------------------ ---- If that is all correct then why do my active connections keep increasing? ------------------------------------------------------------------------ ---- Am I closing all the connections? Well, I have checked every single line of code, and yes I am closing result sets, statements and connections in a finally block: [code] } finally { results.close(); } } finally { statement.close(); } } finally { connection.close(); } [/code] Please have a look at my code and configuration below: My environment: JDK 1.5.0_12 Tomcat 5.5.27 MySQL 5 My Web apps context.xml under the META-INF folder: [code] <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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
