TomcatExpert

Configuring jdbc-pool for high-concurrency

posted by fhanik on April 1, 2010 03:19 PM

In this article we will focus on configuration of the high-concurrency connection pool. For ease of migration for Tomcat users, the configuration has been written to mimic that of Commons DBCP.

The documentation of jdbc-pool covers all the attributes. Please note that these attributes are also available as direct setters on the org.apache.tomcat.jdbc.pool.DataSource bean if you're using a dependency injection framework. So in this article we will focus on use cases, and different configurations for Tomcat.

Simple Connection Pool for MySQL

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
/>

The first thing we notice is the factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" attribute.
When Tomcat reads the type="javax.sql.DataSource" it will automatically configure its repackaged DBCP, unless you specify a different factory. The factory object is what creates and configures the connection pool itself.

There are two ways to configure Resource elements in Apache Tomcat.

Configure a global connection pool

File: conf/server.xml

<GlobalNamingResources>
  <Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
/>
 </GlobalNamingResources>

You then create a ResourceLink element to make the pool available to the web applications. If you want the pool available to all applications under the same name, the easiest way is to edit the File: conf/context.xml

<Context>
  <ResourceLink type="javax.sql.DataSource"
                name="jdbc/LocalTestDB"
                global="jdbc/TestDB"
/>
 <Context>

Note, that if you don't want a global pool, move the Resource element from server.xml into your context.xml file for the web application.

And to retrieve a connection from this configuration, the simple Java code looks like

Context initContext = new
 InitialContext();
   Context envContext  = (Context)initContext.lookup("java:/comp/env");
   DataSource datasource = (DataSource)envContext.lookup("jdbc/LocalTestDB");
   Connection con = datasource.getConnection();
Simple in Java

We can achieve the same configuration using just Java syntax.

DataSource ds = new DataSource();
   ds.setDriverClassName("com.mysql.jdbc.Driver");
   ds.setUrl("jdbc:mysql://localhost:3306/mysql");
   ds.setUsername("root");
   ds.setPassword("password");

Or to separate out the pool properties

PoolProperties pp = new PoolProperties();
   pp.setDriverClassName("com.mysql.jdbc.Driver");
   pp.setUrl("jdbc:mysql://localhost:3306/mysql");
   pp.setUsername("root");
   pp.setPassword("password");
   DataSource ds = new DataSource(pp);

All properties that we make available in XML through the object factory are also available directly on the PoolProperties or the DataSource objects.

Sizing the connection pool

We will work with the following attributes to size the connection pool

  • initialSize
  • maxActive
  • maxIdle
  • minIdle

It's important to understand these attributes, as they do seem quite obvious but there are some secrets. Let's nail it down.

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            initialSize="10"
            maxActive="100"
            maxIdle="50"
            minIdle="10"
            />

The initialSize=10 is the number of connections that will be established when the connection pool is created

  • When defined in GlobalNamingResources the pool is created upon Tomcat startup
  • When defined in Context the pool is created when it's first looked up in JNDI

The maxActive=100 is the maximum number of established connections to the database. This attribute is used to limit the number of connections a pool can have open so that capacity planning can be done on the database side.

The minIdle=10 is the minimum number of connections always established after the connection pool has reached this size. The pool can shrink to a smaller number of connections if the maxAge attribute is used and the connection that should have gone to the idle pool ends up being closed since it has been connected too long. However, typically we see that the number of open connections does not go below this value.

The maxIdle attribute is a little bit trickier. It behaves differently depending on if the pool sweeper is enabled. The pool sweeper is a background thread that can test idle connections and resize the pool while the pool is active. The sweeper is also responsible for connection leak detection. The pool sweeper is defined by

public boolean isPoolSweeperEnabled() {
        boolean timer = getTimeBetweenEvictionRunsMillis()>0;
        boolean result = timer && (isRemoveAbandoned() && getRemoveAbandonedTimeout()>0);
        result = result || (timer && getSuspectTimeout()>0); 
        result = result || (timer && isTestWhileIdle() && getValidationQuery()!=null);
        return result;
    }

The sweeper runs every timeBetweenEvictionRunsMillis milliseconds.
The maxIdle attribute is defined as follows:

  • Pool sweeper disabled - If the idle pool is larger than maxIdle, the connection will be closed when returned to the pool
  • Pool sweeper enabled - Number of idle connections can grow beyond maxIdle but can shrink down to minIdle if the connection has been idle for longer than minEvictableIdleTimeMillis. This may sounds strange that the pool can will not close connections even if the idle pool is larger than maxIdle. It is actually optimal behavior. Imagine the following scenario:
  1. 100 parallel requests served by 100 threads
  2. Each thread borrows a connection 3 times during a request

In this scenario, if we had maxIdle="50" then we could end up closing and opening 50x3 connections. This taxes the database and slows down the application. During peak traffic spikes like this, we want to be able to utilize all the pooled connections. So we definitely want to have the pool sweeper enabled. We will get to that in the next section. There is an additional attribute we mentioned here, maxAge. maxAge defines the time in milliseconds that a connection can be open/established. When a connection is returned to the pool, if the connection has been connected and the time it was first connected is longer than the maxAge value, it will be closed.

As we saw by the isPoolSweeper enabled algorithm, the sweeper is enabled when one of the following conditions is met

  • timeBetweenEvictionRunsMillis>0 AND removeAbandoned=true AND removeAbandonedTimeout>0
  • timeBetweenEvictionRunsMillis>0 AND suspectTimeout>0
  • timeBetweenEvictionRunsMillis>0 AND testWhileIdle=true AND validationQuery!=null
    As of version 1.0.9 the following condition has been added
  • timeBetweenEvictionRunsMillis>0 AND minEvictableIdleTimeMillis>0
    (timer && getMinEvictableIdleTimeMillis()>0);

So in order to get optimal pool sizing, we'd like to modify our configuration to meet one of these conditions

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            initialSize="10"
            maxActive="100"
            maxIdle="50"
            minIdle="10"
            suspectTimeout="60"
            timeBetweenEvictionRunsMillis="30000"
            minEvictableIdleTimeMillis="60000"
            />

Validating Connections

Pooling database connections presents a challenge, since pooled connections can become stale. It's often the case that either the database, or perhaps a device in between the pool and the database, timeout the connection. The only way to truly validate a connection is to make a round trip to the database, to ensure the session is still active. In Java 6, the JDBC API addressed this by supplying a isValid call on the java.sql.Connection interface. Prior to that, pools had to resort to executing a query, such as SELECT 1 on MySQL. This query is easy for the database to parse, doesn't require any disk access. The isValid call is scheduled to be implemented but the pool, intended to be used with Apache Tomcat 6, must also preserve Java 5 compatibility.

Validation Queries

Validation queries present a few challenges

  1. If called too often, they can degrade the performance of the system
  2. If called too far apart, they can result in stale connections
  3. If the application calls setTransactionIsolation with autoCommit=false, it can yield a SQLException if the application tries to call setTransactionIsolation again, since the validation query might have already initiated a new transaction in the DB.

Let's look at the most typical configuration:

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            testOnBorrow="true"
            validationQuery="SELECT 1"
            />

With this configuration, the query SELECT 1 is executed each time the Java code calls Connection con = dataSource.getConnection();.

This guarantees that the connection has been tested before it's handed to the application. However, for applications using connections very frequently for short periods of time, this has a severe impact on performance. The two other configuration options:

  • testWhileIdle
  • testOnReturn

are not really that helpful, as they do test the connection, but at the wrong time.

Not having validation is not really a choice for a lot of applications. Some applications get around it by setting minIdle=0 and and a low minEvictableIdleTimeMillis value so that if connections sit idle long enough to where the database session would time out, the pool will time them out as idle before that happens.

The better solution is to test connections that have not been tested for a while.

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            testOnBorrow="true"
            validationQuery="SELECT 1"
            validationInterval="30000"
            />

In this configuration, connections would be validated, but no more than every 30 seconds. It's a compromise between performance and connection validation. And as mentioned, if we want to get away with validation all together we could configure the pool to timeout idle connections

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            timeBetweenEvictionRunsMillis="5000"
            minEvictableIdleTimeMillis="5000"
            minIdle="0"
            />

Setting up a custom database session

In some use cases it is required to perform some tasks when a new database session is initialized. This could involve executing a simple SQL statement or calling a stored procedure.
This is typically done at the database level, where you can create triggers.

create or replace trigger logon_alter_session after logon on database
  begin
    if sys_context('USERENV', 'SESSION_USER') = 'TEMP' then
      EXECUTE IMMEDIATE 'alter session ....';
    end if;
  end;
  /

This would however affect all users, and in the situations where this is not sufficient and we want a custom query to be executed when a new session is created.

<Resource name="jdbc/TestDB" auth="Container"
            type="javax.sql.DataSource"
            description="Oracle Datasource"
            url="jdbc:oracle:thin:@//localhost:1521/orcl"
            driverClassName="oracle.jdbc.driver.OracleDriver"
            username="default_user"
            password="password"
            maxActive="100"
            validationQuery="select 1 from dual"
            validationInterval="30000"
            testOnBorrow="true"
            initSQL="ALTER SESSION SET NLS_DATE_FORMAT = &apos;YYYY MM DD HH24:MI:SS&apos;"/>

The initSQL is executed exactly once per connection, and that is when the connection is established.

Connection pool leaks and long running queries

Connection pool also contain some diagnostics. Both jdbc-pool and Commons DBCP are able to detect and mitigate connections that are not being returned to the pool. These are referred to as abandoned to leaked connections as demonstrated here.

Connection con = dataSource.getConnection();
  Statement st = con.createStatement();
  st.executeUpdate("insert into id(value) values (1'); //SQLException here
  con.close();

There are five configuration settings that are used to detect these type of error conditions, the first three shared with Common DBCP

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            maxActive="100"
            timeBetweenEvictionRunsMillis="30000"
            removeAbandoned="true"
            removeAbandonedTimeout="60"
            logAbandoned="true"
            />
  • removeAbandoned - set to true if we want to detect leaked connections
  • removeAbandonedTimeout - the number of seconds from when dataSource.getConnection was called to when we consider it abandoned
  • logAbandoned - set to true if we should log that a connection was abandoned. If this option is set to true, a stack trace is recorded during the dataSource.getConnection call and is printed when a connection is not returned.

There are of course use cases when we want this type of diagnostics, but we are also running batch jobs that hold a connection for minutes at a time. How do we handle that?
Two additional options/features have been added to support these

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            maxActive="100"
            timeBetweenEvictionRunsMillis="30000"
            removeAbandoned="true"
            removeAbandonedTimeout="60"
            logAbandoned="true"
            abandonWhenPercentageFull="50"
            />
  • abandonWhenPercentageFull - A connection must meet the threshold removeAbandonedTimeout AND the number of open connections must exceed the percentage of this value.

Using this property will give connections that would have otherwised been considered abandoned, possibly during a false positive. Setting the value to 100 would mean that connections are not considered abandoned unless we've reached our maxActive limit. This gives the pool a bit more flexibility, but it doesn't address our 5 minute batch job using a single connection. In that case, we want to make sure that when we detect that the connection is still being used, we reset the timeout timer, so that the connection wont be considered abandoned. We do this by inserting an interceptor.

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            maxActive="100"
            timeBetweenEvictionRunsMillis="30000"
            removeAbandoned="true"
            removeAbandonedTimeout="60"
            logAbandoned="true"
            abandonWhenPercentageFull="50"
            jdbcInterceptors="ResetAbandonedTimer"
            />

Interceptor—org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer—can be specified by its fully qualified name or if it lives in the org.apache.tomcat.jdbc.pool.interceptor package, by its short class name.
Each time a statement is prepared or a query is executed, the timer will reset the abandon timer on the connection pool. This way, the 5 minute batch job, doing lots of queries and updates, will not timeout.

There are of course situations where you want to know about these scenarios, but you don't want to kill or reclaim the connection, since you are not aware of what impact that will have on your system.

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            maxActive="100"
            timeBetweenEvictionRunsMillis="30000"
            logAbandoned="true"
            suspectTimeout="60"
            jdbcInterceptors="ResetAbandonedTimer"
            />

The suspectTimeout attribute works in the exact same way as the removeAbandonedTimeout except that instead of closing the connection, it simply logs a warning and issues a JMX notification with the information. This way, you can find out about these leaks or long running queries without changing the behavior of your system.

Pooling connections from other data sources

So far we have been dealing with connection pooling around connections acquired using the java.sql.Driver interface. Hence we used the attributes

  • driverClassName
  • url

However, some connection configurations are done using the javax.sql.DataSource or even the javax.sql.XADataSource interfaces, and we need to be able to support those configurations.
In plain Java this is relatively easy.

PoolProperties pp = new PoolProperties();
   pp.setDataSource(myOtherDataSource);
   DataSource ds = new DataSource(pp);
   Connection con = ds.getConnection();

Or

DataSource ds = new DataSource();
   ds.setDataSource(myOtherDataSource);
   Connection con = ds.getConnection();

We are able to inject another javax.sql.DataSource or javax.sql.XADataSource object and use that for connection retrieval.
This comes in handy when we deal with XA connections.

For XML configuration, the jdbc-pool comes with a org.apache.tomcat.jdbc.naming.GenericNamingResourcesFactory class, a simple class to allow configuration of any type of named resource. To setup a Apache Derby XADataSource we can create this snippet:

<Resource factory="org.apache.tomcat.jdbc.naming.GenericNamingResourcesFactory" 
            name="jdbc/DerbyXA1"
            type="org.apache.derby.jdbc.ClientXADataSource"
            databaseName="sample1"
            createDatabase="create"
            serverName="localhost"
            portNumber="1527"
            user="sample1"
            password="password"/>

This is a simple XADataSource that connects to a networked Derby instance on port 1527.

And if you would want to pool the XA connections from this data source we can create the connection pool element next to it.

<Resource factory="org.apache.tomcat.jdbc.naming.GenericNamingResourcesFactory"
            name="jdbc/DerbyXA1"
            type="org.apache.derby.jdbc.ClientXADataSource"
            databaseName="sample1"
            createDatabase="create"
            serverName="localhost"
            portNumber="1527"
            user="sample1"
            password="password"/>
            <Resource factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            dataSourceJNDI="DerbyXA1"<!--Links to the Derby XADataSource-->
            name="jdbc/TestDB1"
            auth="Container"
            type="javax.sql.XADataSource"
            testWhileIdle="true"
            testOnBorrow="true"
            testOnReturn="false"
            validationQuery="SELECT 1"
            validationInterval="30000"
            timeBetweenEvictionRunsMillis="5000"
            maxActive="100"
            minIdle="10"
            maxIdle="20"
            maxWait="10000"
            initialSize="10"
            removeAbandonedTimeout="60"
            removeAbandoned="true"
            logAbandoned="true"
            minEvictableIdleTimeMillis="30000"
            jmxEnabled="true"
            jdbcInterceptors="ConnectionState;StatementFinalizer;SlowQueryReportJmx(threshold=10000)"
            abandonWhenPercentageFull="75"/>

Note how the type=javax.sql.XADataSource is set, this will create a org.apache.tomcat.jdbc.pool.XADataSource instead of org.apache.tomcat.jdbc.pool.DataSource
Here we are linking the two data sources using the dataSourceJNDI=DerbyXA1 attribute. The two data sources both have to exist in the same namespace, in our example, the jdbc namespace.
Currently JNDI lookup through DataSource.setDataSourceJNDI(...) is not supported, only through the factory object.

If you inject a

  • javax.sql.DataSource object - the pool will invoke javax.sql.DataSource.getConnection) method
  • javax.sql.DataSource object but specify username/password in the pool- the pool will invoke javax.sql.DataSource.getConnection(String username, String password) method
  • javax.sql.XADataSource object - the pool will invoke javax.sql.XADataSource.getXAConnection() method
  • javax.sql.XADataSource object but specify username/password in the pool- the pool will invoke javax.sql.DataSource.getXAConnection(String username, String password) method

Here is an interesting phenomenon that comes up when you deal with XADataSources. You can cast the returning object as either a java.sql.Connection or a javax.sql.XAConnection and invoke methods for both interfaces on the same object.

DataSource ds = new DataSource();
   ds.setDataSource(myOtherDataSource);
   Connection con = ds.getConnection();
   if (con instanceof XAConnection) {
     XAConnection xacon = (XAConnection)con;
     transactionManager.enlistResource(xacon.getXAResource());
   }
   Statement st = con.createStatement();
   ResultSet rs = st.executeQuery(SELECT 1);

JDBC Interceptors

To make the implementation flexible the concept of JDBC interceptors was created. The javax.sql.PooledConnection that wraps the java.sql.Connection/javax.sql.XAConnection from the underlying driver or data source is itself an interceptor. The interceptors are based on the java.lang.reflect.InvocationHandler interface. An interceptor is a class that extends the org.apache.tomcat.pool.jdbc.JdbcInterceptor class.

In this article, we'll cover how interceptors are configured. In our next article, we will go over how to implement custom interceptors and their life cycle.

<Resource factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
 
            ...
            jdbcInterceptors="ConnectionState;StatementFinalizer;SlowQueryReportJmx(threshold=10000)"
 
  />

Is the same as

<Resource factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
 
            ...
           jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
           org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;
           org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx(threshold=10000)"
  />

A short class name, such as ConnectionState, can be used if the interceptor is defined in the org.apache.tomcat.jdbc.pool.interceptor package. Otherwise, a fully qualified name is required.
Interceptors are defined in semi colon ; separated string. Interceptors can have zero or more parameters that are defined within parenthesis. Parameters are comma separated simple key-value pairs.

Connection State

The java.sql.Connection interface exposes a few attributes

  • autoCommit
  • readOnly
  • transactionIsolation
  • catalog

The default value of these attributes can be configured using the following properties for the connection pool.

  • defaultAutoCommit
  • defaultReadOnly
  • defaultTransactionIsolation
  • defaultCatalog

If set, the connection is configured for this when the connection is established to the database. If the ConnectionState interceptor is not configured, setting these properties is a one time operation only taking place during connection establishment. If the ConnectionState interceptor is configured, the connection is reset to the desired state each time its borrowed from the pool.

Some of these methods result in round trips to the database when queries. For example, a call to Connection.getTransactionIsolation() will result in the driver querying the transaction isolation level of the current session. Such round trips can have severe performance impacts for applications that use connections very frequently for very short/fast operations. The ConnectionState interceptor caches these values and intercepts calls to the methods that query them to avoid these round trips.

Statement Finalizer

Java code using the java.sql objects should do proper cleanup and closure of resources after they have been used.
An example of code cleanup

Connection con = null;
   Statement st = null;
   ResultSet rs = null;
   try {
     con = ds.getConnection();
     ...
 
   } finally {
     if (rs!=null) try  { rs.close(); } catch (Exception ignore){}
     if (st!=null) try  { st.close(); } catch (Exception ignore){}
     if (con!=null) try { con.close();} catch (Exception ignore){}
   }

Some applications are not always written in this way. We previously showed how to configure the pool to diagnose and warn when connections were not closed properly.
The StatementFinalizer interceptor makes sure that the java.sql.Statement and its subclasses are properly closed when a connection is returned to the pool.

Getting hold of the actual JDBC connection

The connection proxy that is returned implements the javax.sql.PooledConnection so retrieving the connection is pretty straight forward, and no need to cast to a specific class in order to do so.
The same applies if you've configured the pool to handle javax.sql.XAConnection.

Another interesting way of retrieving the underlying connection is

Connection con = ds.getConnection();
  Connection underlyingconnection = con.createStatement().getConnection();

This is because jdbc-pool does not proxy statements by default. There is of course an interceptor that protects against this use case.

And that's it for now folks. Stay tuned for more in depth articles that will take us under the hood of some neat concurrency traps and tricks.

Filip Hanik is a Senior Software Engineer for the SpringSource Division of VMware, Inc. (NYSE: VMW) and a key participant in the company's Apache Tomcat initiatives. Filip brings 15 years of extensive experience in architecture, design and development of distributed application frameworks and containers and is recognized for his top-quality system development skills and continuous participation of Open Source development projects. Filip is an Apache Software Foundation member and a committer to the Apache Tomcat project where he is a leading authority on Tomcat clustering and a key contributor to the core of the platform. Filip has made contributions to software initiatives for Walmart.com, Sony Music, France Telecom and has held a variety of senior software engineering positions with technology companies in both the United States and Sweden. He received his education at Chalmers University of Technology in Gothenburg, Sweden where he majored in Computer Science and Computer Engineering.

Comments

Formatting problem in isPoolSweeperEnabled() snippet

It looks like a few HTML span tags made it into the pre section for your isPoolSweeperEnabled() code snippet. It's not a big deal, but makes it a bit harder to read the code.

Speaking of the readability, don't you think it would be easier to read that method body if it were phrased as follows?
cond1 && (cond2 || cond3 || cond4)

rather than
(cond1 && cond2) || (cond1 && cond3) || (cond1 && cond4)

Thanks,
Matt

Great catch

Thanks - those span tags are annoying when copying the post over to publish. They are now fixed. Appreciate the heads up. I'll leave Filip to comment on the code structure.

Stacey Schneider is the managing editor for TomcatExpert.com.

No problem

Thanks for the fix. That sort of thing comes up occasionally, especially on a site like this where you might often *want* to show HTML tags. :)

Version 1.0.9?

Great post Filip,

Where can I get hold of version 1.0.9? And where is the official place I should be obtaining the source, or binary? I'm currently getting the source from, and building local to distribute to our internal maven repo:

- http://svn.apache.org/viewvc/tomcat/tags/

But there is only 1.0.8.4 in there. However, I see 1.0.8.5 binary here:

- http://people.apache.org/~fhanik/jdbc-pool/v1.0.8.5/

I'm keen to enable the poolSweeper by default.
Thanks in advance.

Cheers,
/dom

Where to get releases

Hi,

There have, as yet, been no official releases of jdbc-pool from the ASF. The binaries from Filip's personal area should be viewed as development snapshots for testing purposes.

I can't find a tag for 1.0.8.5 either, so I'm guessing Filip built that for himself and didn't tag it.

You are already in the right place to get the latest source code. As an aside, you should be OK building from trunk. If you do spot any issues feel free to raise a Bugzilla ticket and someone will take a look.

Cheers,

Mark

Problems with "Pool-Timeout before Database-Timeout" example

Hi, thanks for the informative post!

I followed your advice under "Validating Connections" to make the pool timeout connections before the database does, using:

minIdle = "0"
timeBetweenEvictionRunsMillis = "30000"
minEvictableIdleTimeMillis = "60000"

For testing purposes I have set the mysql wait_timeout = 180, but looking at the mysql open connections, I see 10 connections being generated at the beginning, but they idle well beyond 60s and are dropped by mysql at 180s. No eviction happening.. shouldn't the max time a connection is idle be less than [eviction interval] + [evictable idle time]?

Or is there any dependency on the other validation* test* options that I also have to set?

Thanks in advance,
Adrian

Questions

This is work in tomcat 6 ? I mean...Can I override old jar pool ? Or is..just work in tomcat 7 ?
Regards

Fernando Franzini - Java Blog

Alternate providers

Great article. This information is sometimes hard to find all in one place. Thanks a lot!
I was wondering if you could give your thoughts on how the new JDBC pool weighs against C3P0 and BoneCP.

Jed Wesley-Smith

The linked jdbc-pool docs say: "commons-dbcp uses static interfaces. This means you can't compile it with JDK 1.6, or if you run on JDK 1.6/1.7 you will get NoSuchMethodException for all the methods not implemented, even if the driver supports it.".

We struggling to try and work out what this actually means? Uses "static interfaces"?

NullPointerException in 1.0.8.5

Build 1.0.8.5 throws a NullPointerException when there's no username or password configured. This may be by design.

My scenario requires multiple databases to which I'd like to have a one-to-one correspondence with pools. Here's an email I sent to the Tomcat mailing list, describing the issue in DBCP.

http://tomcat.markmail.org/message/llkooxglqqk4vrcs?q=connection+pooling+basicdatasource

Can JDBC-pool accomplish multiple pools with connections to multiple databases?

Andrew

java.lang.ClassNotFoundException: oracle.jdbc.driver.oracleDrive

hey,
iam new to jdbc.i hav added ojdbc14.jar in "\apache-tomcat-7.0.8\lib" folder .please help.

oracle driver

>java.lang.ClassNotFoundException: oracle.jdbc.driver.oracleDrive

check for typos in your configuration, try
oracle.jdbc.driver.OracleDriver

Note the capital "O" and driver ends with "r"

best
Filip

Current availability of jdbc-pool

As of tomcat 7.0.22 is jdbc-pool included with the distribution of tomcat in the tomcat-jdbc.jar ? jdbc-pool is mentioned frequently in the Tomcat changelog and the confusion is crazy. If not are binaries available anywhere ?

Is there a Spring example that shows basic usage in any form ? It kinda hard to test /learn when you are unsure of the basics...

Is it possible to put all the config elements into a bean in the spring-servlet.xml and the inject that into the DAOs ?

thanks

Confusion

hi Stimpy, not sure what the confusion is.
The connection pool is included as of 7.0.22, so it ships with Apache Tomcat. It is also available in Apache Tomcat's maven repository.

http://tomcat.apache.org/tomcat-7.0-doc/maven-jars.html

Configure it using Spring, simply means configuring it as a spring bean.
Here is a pojo example http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Code%20Example
Turning this example into a spring bean is rather straight orward.

The first source of confusion

The first source of confusion was weather or not jdbc-pool was included in shipping tomcat. Searching was retrieving conflicting results : )

I looked at the POJO example. Sadly translating that to spring was not proving successful for me . I will keep trying .

The remaining question is " Is it possible to do away with the web.xml configuration steps as referred to in the documentation" ? Can all the configuration be done in the context ( in a bean or otherwise ? ).

thanks very much for the pool and the help !

Spring Example

Stimpy, please find a full Maven project example.
http://people.apache.org/~fhanik/jdbc-pool/spring-jdbc-pool.zip

It will download all dependencies for you, if you wish to run the App.java example

best
Filip

how to buy support for tomcat-jdbc-pool

Hi How can we buy the support of tomcat-jdbc-pool team , in case any exceptions.

support

support is available through VMware and possibly other vendors. just send an email to sales@springsource.com with your contact information and someone will reach out to you.

Injecting transaction manager on MYSQL XA datasource which is us

I am not using any application containers

I am using multiple resources - database(mysql) and cache(infinispan)

Hence i have to use XA. And I use jboss transaction manager.

I am confused , about how to set the transaction manager reference to Mysql datasource backed by a conenction pool (tomcat jdbc pool)

Mysql - xa - datasource

Data source baced by tomcat-jdbc-pooling

transaction manager configurations using spring

My application seems to run fine with this configurations , But i realize that datasource is not governed by the transaction manager .

But i am not sure how to connect b/n transaction manager and datasource.

I think this is more of a

I think this is more of a Spring question. See here for more details.

http://forum.springsource.org/showthread.php?127192-Injecting-transaction-manager-on-MYSQL-XA-datasource-which-is-using-Tomcat-Jdbc-Pool

best pure java example

For all those wishing to try this and who have little understanding of how to go about it, he best example I found is here:

http://kahimyang.info/kauswagan/howto_blogs/1017-using_tomcat_jdbc_connection_pool_as_standalone_instantiated_bean

Works like a treat.

autoReconnect=true

Found having autoreconnect=true within the url connection line produce exceedingly high time in sql queries.

A page of 240 records was taking 12-23 seconds

With it removed 300 miliseconds to 1.2 seconds.

Is this some form of conflict?

jdbc resource with server.xml VS java class DB pool connector

My last curosity and most important if anyone can answer. I was reading somewhere that configuring a db connector via server.xml as a resource is a better way than letting java handle it.

If a java is then configured to read in its own config file to me it would be quite a similar setup except the config file is app independant.
Is there any truth in this i.e. if a db connection is made via tomcat's resource then tomcat would manage this any better more efficiently than java code doing this ?

I found after debugging the .info url i posted above that the listener would make a connection as tomcat is started and then the site would just use this open connection so in essence the java code checking if connection was on found it was and proceeded.

Hi, I configured jdbc-pool

Hi, I configured jdbc-pool with dataSourceJNDI per your example.
I'm able to use the DB connection, however I get a warning during Tomcat startup.

I've tried to ask on tomcat-users, but got no anwser, the details are here http://tomcat.10.n6.nabble.com/Warnings-when-using-jdbc-pool-dataSourceJNDI-td4988137.html

Do you have an idea what is causing the warning? (Obviously the JNDI binding is not available, but why is it needed at that stage?)

minIdle, maxIdle and

minIdle, maxIdle and maxActive are really important configuration parameters for any JDBC or tomcat connection pool.

Pool Cleaner blocked! need help

Hi, I configured jdbc-pool with JNDI and all works great, except if I use removeAbandoned="true". To test it I used following parameters:
- removeAbandoned="true"
- removeAbandonedTimeout="5"
- abandonWhenPercentageFull="0"
- logAbandoned="true".
After the timeout I can se that the connection is marked as abandoned and then nothing happens.
Using a profiler I noticed that the thread associated to PoolCleaner is in the state "BLOCKED".

Any help? Thank you
Daniele

Pool Cleaner

From Mark Thomas:

You have not provided enough information to diagnose the issue you are seeing. Based on the minimal information you have provided this might be bug 53367 that is fixed in 7.0.28 onwards. If you are using an earlier version, try upgrading.

If it is still a problem, we suggest hitting the official tomcat user lists for additional solutions.

Stacey Schneider is the managing editor for TomcatExpert.com.

Pool Cleaner

This is my complete context.xml:

name="jdbc/aaaa"
scope="Unshareable"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
username="myusername"
password="mypassword" driverClassName="oracle.jdbc.OracleDriver"
url="url"
maxActive="100"
maxWait="5000"
initialSize="1"
minIdle="1"
maxIdle="5"
timeBetweenEvictionRunsMillis="30000"
minEvictableIdleTimeMillis="60000"
testOnBorrow="true"
validationQuery="SELECT 1 FROM DUAL"
validationInterval="30000"
removeAbandoned="true"
removeAbandonedTimeout="5"
abandonWhenPercentageFull="0"
logAbandoned="true"
jdbcInterceptors="ConnectionState;StatementFinalizer;SlowQueryReportJmx(threshold=10000)

To test it I ran a long-time query. After removeAbandonedTimeout(5 sec) the connection is marked as abandoned. Then, at the Pool Cleaner execution time, I can see that the connection has been released but the Pool Cleaner thread remains in the status BLOCKED for a long time. Only after several minutes it returns in the TIME_WAITING status and the log shows out the closed connection exception.
Using a profiler I can see the lines below when the thread is blocked:

oracle.jdbc.driver.PhysicalConnection.close ( PhysicalConnection.java:1151 )
org.apache.tomcat.jdbc.pool.PooledConnection.disconnect ( PooledConnection.java:331 )
org.apache.tomcat.jdbc.pool.PooledConnection.release ( PooledConnection.java:490 )
org.apache.tomcat.jdbc.pool.ConnectionPool.release ( ConnectionPool.java:581 )
org.apache.tomcat.jdbc.pool.ConnectionPool.abandon ( ConnectionPool.java:540 )
org.apache.tomcat.jdbc.pool.ConnectionPool.checkAbandoned ( ConnectionPool.java:958 )
org.apache.tomcat.jdbc.pool.ConnectionPool$PoolCleaner.run ( ConnectionPool.java:1347 )
java.util.TimerThread.mainLoop ( unknown source )
java.util.TimerThread.run ( unknown source )

The main problem is that there is a long time between the abandon signal and the connection closed exception. This results in a long wait for the user on the web application before the error is shown.

Hope I provided useful information, thank you.
Regards,
Daniele

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.