tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Propes, Barry L " <barry.l.pro...@citi.com>
Subject RE: Tomcat 5.5, JNDI Connection Pooling, Active connections keep increasing....
Date Tue, 16 Sep 2008 14:26:03 GMT
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


Mime
View raw message