Modifying a JNDI connection pool programmaticaly

Question

I am using Apache Tomcat JDBC connection pool library in my project and configured the context.xml file accordingly. My application instance needs to run at multiple locations, but load on the application will be different, so I want to modify the maxActive size and some other property based on the customer size at particular instance at runtime.

  <Context path="/abc"
             docBase="abc"
             debug="5"
             reloadable="false"
             crossContext="true">
       <Resource name="jdbc/abc"
          auth="Container"
          type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          driverClassName="xxxxx"
          url="xxxxxxx"
          username="xxxxx" password="xxxxxx"
          maxActive="20"
          initialSize="0"
          ...
          />
     </Context>

Answer 1

There is nothing special about a datasource created through JNDI: if you know its class (org.apache.tomcat.jdbc.pool.DataSource in your case), you can cast to that class and use the available setters to configure it:

    private void customizeDataSource(final DataSource ds) {
        if (ds instanceof PoolConfiguration) {
            final PoolConfiguration poolConfig = (PoolConfiguration) ds;
            poolConfig.setMaxActive(10);
        }
    }

(see the definition of PoolConfiguration). Implementations of javax.sql.DataSource also implement a very useful interface Wrapper, which may come handy if your code wraps the Tomcat JDBC datasource in something else:

    private void customizeDataSource(final DataSource ds) throws SQLException {
        if (ds.isWrapperFor(PoolConfiguration.class)) {
            final PoolConfiguration poolConfig = ds.unwrap(PoolConfiguration.class);
            poolConfig.setMaxActive(10);
        }
    }

There are however some problems that can arise from the programmatic approach above:

• if you bundle tomcat-jdbc.jar with your application, only JNDI resources configured in your context.xml will be recognized by your code. Those in GlobalNamingResources will use the copy of org.apache.tomcat.jdbc.pool.DataSource bundled with Tomcat and will not match the instanceof condition.
• if, on the other hand, you don't include tomcat-jdbc.jar into your WAR file, you must make sure that the parameters you set are supported by all versions of Tomcat on which your application will run.

Answer 2

You could try using standard JMX for this purpose.

As you can see in the documentation, Tomcat can expose the connection pool as a MBean object you can interact with using tools like JConsole, for instance.

The the MBean implementation basically delegates to the actual org.apache.tomcat.jdbc.pool.ConnectionPool the different operations that can be performed through the MBean interface and, AFAIK, ConnectionPool dynamically allocates connections based on current configuration.

---

ORIGINAL ANSWER BASED ON CONFIGURATION AND NOT RUNTIME BEHAVIOR

Please, consider review this related SO question and the self provided answer, I think it could be helpful.

Tomcat substitutes system provided environment variables in its configuration files:

Tomcat configuration files are formatted as schemaless XML; elements and attributes are case-sensitive. Apache Ant-style variable substitution is supported; a system property with the name propname may be used in a configuration file using the syntax ${propname}. All system properties are available including those set using the -D syntax, those automatically made available by the JVM and those configured in the $CATALINA_BASE/conf/catalina.properties file.

As indicated, the best way you could include the properties you need to dynamically being substituted by Tomcat is passing them as system properties using the -D option and probably in the JAVA_OPTS environment variable.

As indicated in the afore mentioned question, and advised as well in catalina.sh:

# Environment Variable Prerequisites
#
#   Do not set the variables in this script. Instead put them into a script
#   setenv.sh in CATALINA_BASE/bin to keep your customizations separate.
#

define them, for example, in a setenv.sh file located in the $CATALINA_BASE/bin directory.

For example:

#! /bin/sh

export MAX_ACTIVE_CONNECTIONS=20

export JAVA_OPTS="$JAVA_OPTS -DmaxActiveConnections=$MAX_ACTIVE_CONNECTIONS"

And use these properties in your XML configuration files:

<Context path="/abc"
         docBase="abc"
         debug="5"
         reloadable="false"
         crossContext="true">
    <Resource name="jdbc/abc"
              auth="Container"
              type="javax.sql.DataSource"
              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
              driverClassName="xxxxx"
              url="xxxxxxx"
              username="xxxxx" password="xxxxxx"
              maxActive="${maxActiveConnections}"
              initialSize="0"
              ...
    />
</Context>

Answer 3

MySQL connects rapidly, thereby making connection pooling of limited use.

Usually, if there is a performance problem it is better handled by other techniques -- composite indexes, reformulating queries, working around optimization limitations of MySQL, etc.

Would you care to back up a step and let's analyze the bottlenecks?