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>

Veroniqueverras

Are you sure you are using Tomcat JDBC? Without using a factory attribute, Tomcat's default is to use a repackaged version of DBCP2. – Bhatt

thanks. factory missed while typing. – Veroniqueverras

"multiple locations" -- Are you saying that the clients are on many machines but MySQL is on a single server? How many? – Archespore

@RickJames no Mysql also on same server. every application instance has its own DB. – Veroniqueverras

Answer 1

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>

Purifoy

this does not sound like programmatic modification, which was asked by the OP – Internuncial

Thank you very much for point that out @eis. Sorry, I missed that part of the question. – Purifoy

Thank you very much again @eis. I updated the answer with a runtime based solution. Sorry for missing the runtime requirement the first time. – Purifoy

I support the JMX variant. No problem to get the MBean from the registry for programmatic modification. On the other hand, i would let the connection pool implementation handle the number of connections. – Moneymaker

Answer 2

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.

Bhatt

Global Naming Resources could be accessed over Java Reflection. There are only few different data source implementations. One can iterate over multiple possible method names until it works... – Landward

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?

Archespore