4.3 Setting Up a High-Concurrency JDBC Datasource

4.3 Setting Up a High-Concurrency JDBC Datasource
Prev	4. Configuring a tc Runtime Instance Manually	Next

A datasource defines a pool of JDBC connections for a specific database using a URL, username, and so on. JDBC datasources make it easy for an application to access data in a database server.

Comparing the DBCP Datasource and the tc Runtime Datasource

In a tc Runtime instance, you can create the following two types of JDBC datasources:

Database connection pool (DBCP) datasource
tc Runtime datasource

The DBCP datasource is the standard datasource provided by tc Runtime; it uses the org.apache.commons.dbcp package. Although this datasource is adequate for simple applications, it is single-threaded, which means that in order to be thread-safe, the tc Runtime instance must lock the entire pool, even during query validation. Thus it is not suitable for highly concurrent environments. Additionally, it can be slow, which in turn can negatively affect the performance of Web applications.

The tc Runtime datasource includes all the functionality of the DBCP datasource, but adds additional features to support highly-concurrent environments and multiple core/cpu systems. The tc Runtime datasource typically performs much better than the DBCP datasource. Additional features include:

Dynamic implementation of the interfaces, which means that the datasource supports the java.sql and javax.sql interfaces for your runtime environment (as long as your JDBC driver supports it), even when compiled with a lower version of the JDK.
Validation intervals so that tc Runtime doesn't have to validate every single time the application uses the connection, which improves performance.
Run-Once query, which is a configurable query that tc Runtime runs only once when the connection to the database is established. This is very useful to set up session settings that you want to exist during the entire time the connection is established.
Ability to configure custom interceptors to enhance the functionality of the datasource. You can use interceptors to gather query stats, cache session states, reconnect the connection upon failures, retry queries, cache query results, and so on. The interceptors are dynamic and not tied to a JDK version of a java.sql/javax.sql interface.
Asynchronous connection retrieval - you can queue your request for a connection and receive a Future<Connection> back.

Configuring the tc Runtime High-Concurrency JDBC Datasource

As with any tc Runtime resource, you configure the high-concurrency datasource (that is, the tc Runtime datasource) using a <Resource> child element of <GlobalNamingResource>. Most attributes are common to the standard DBCP and the tc Runtime datasources; however, the following new attributes apply only to the new tc Runtime datasource.

initSQL
jdbcInterceptors
validationInterval
jmxEnabled
fairQueue
useEquals

Use the factory attribute of the <Resource> element to specify the type of datasource:

Set the factory attribute to org.apache.tomcat.jdbc.pool.DataSourceFactory to use the tc Runtime high-concurrency datasource. This is also the default value of the factory attribute for tc Runtime, so you will automatically use the high-concurrency datasource if you do not specify this attribute at all.
Set the factory attribute to org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory to use the standard DBCP datasource.

IBM JVM USERS ONLY: If you are using an IBM JVM, see useEquals for important information.

The following table lists the attributes for configuring either the high-concurrency datasource or the standard DBCP datasource. Most attributes are valid for both of the datasources, but some are only valid for one datasource. These exceptions are noted in the table. The default values shown are for the high-concurrency datasource, which is the default datasource for tc Server. Default values for the DBCP datasource may be different. See the Apache DBCP documentation for details.

Table 4.1. Connection Pool Configuration Attributes

Attribute	Default	Description
username (required)		The username to pass to the JDBC driver to establish a connection with the database.
password (required)		The password to pass to the JDBC driver to establish a connection with the database.
url (required)		The connection URL to pass to the JDBC driver to establish a connection.
driverClassName (required)		The fully qualified Java class name of the JDBC driver to use. The driver must be accessible from the same classloader as `tomcat-jdbc.jar`
connectionProperties		Connection properties to send to the JDBC driver when establishing a new database connection. The syntax for this string is [propertyName=value;]* The "user" and "password" properties are passed explicitly, so do not include them here.
defaultAutoCommit	`true`	The default auto-commit state of connections created by this pool. If it is not set, the JDBC driver's default setting is active.
defaultReadOnly	driver default	The default read-only state of connections created by this pool. If not set, the `setReadOnly` method will not be called. (Some drivers do not support read only mode, for example Informix.)
defaultTransactionIsolation	driver default	The default TransactionIsolation state of connections created by this pool. One of the following: `NONE` `READ_COMMITTED` `READ_UNCOMMITTED` `REPEATABLE_READ` `SERIALIZABLE` (see Javadoc). If not set, the default is the JDBC driver's default.
defaultCatalog		The default catalog of connections created by this pool.
initialSize	`10`	The initial number of connections to create when the pool is started.
maxActive	`100`	The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
maxIdle	`maxActive` (100)	The maximum number of connections that should be kept in the pool at all times. Idle connections are checked periodically (if enabled) and connections that have been idle for longer than `minEvictableIdleTimeMillis` are released. See also `testWhileIdle`.
minIdle	`10`	The minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number if validation queries fail. The default value is derived from `initialSize`.
maxWait	`30000`	The maximum milliseconds a pool with no available connections will wait for a connection to be returned before throwing an exception, or `-1` to wait indefinitely.
validationQuery		The SQL query to use to validate connections from this pool before returning them to the caller. If specified, the query must be an SQL SELECT statement that returns at least one row.
testOnBorrow	`false`	Indicates whether objects are validated before borrowed from the pool. If the object fails to validate, it is dropped from the pool, and an attempt is made to borrow another. A `true` value has no effect unless the `validationQuery` parameter is set to a non-null string.
testOnReturn	`false`	Indicates if objects are validated before they are returned to the pool. A true value has no effect unless the `validationQuery` parameter is set to a non-null string.
testWhileIdle	`false`	Indicates whether objects are validated by the idle object evictor (if any). If an object fails to validate, it is dropped from the pool. A `true` value has no effect unless the `validationQuery` parameter is set to a non-null string. This parameter must be set to activate the pool test/cleaner thread.
timeBetweenEvictionRunsMillis	`5000`	The number of milliseconds to sleep between runs of the idle object evictor thread. The thread checks for idle, abandoned connections and validates idle connections. The value should not be set below 1 second (1000).
numTestsPerEvictionRun		Not used by the Tomcat JDBC pool. The number of objects to examine during each run of the idle object evictor thread, if any.
minEvictableIdleTimeMillis	`60000`	The minimum amount of time an object may sit idle in the pool before it is eligible for eviction by the idle object evictor, if any.
connectionInitSqls	`null`	A Collection of SQL statements used to initialize physical connections when they are first created. These statements are executed only once, when the connection factory creates the connection. DBCP Versions 1.3 and 1.4 of incorrectly use "`initConnectionSqls`" as the name of this property for JNDI object factory configuration. Until 1.3.1/1.4.1 are released, "`initConnectionSqls`" must be used as the name for this property when using BasicDataSourceFactory to create BasicDataSource instances via JNDI.
poolPreparedStatements	`false`	This property is not used.
maxOpenPreparedStatements		This property is not used.
accessToUnderlyingConnectionAllowed		Not used. Access can be achieved by calling `unwrap` on the pooled connection. See `javax.sql.DataSource` interface, or call `getConnection` through reflection, or cast the object as `javax.sql.PooledConnection`.
removeAbandoned	`false`	Set to `true` to remove abandoned connections if they exceed the `removeAbandonedTimeout`. Setting this to `true` can recover database connections from poorly written applications that fail to close a connection. A connection is considered abandoned and eligible for removal if it has been idle longer than the `removeAbandonedTimeout`.
removeAbandonedTimeout	`60`	Timeout in seconds before an abandoned connection can be removed. The value should be set to the longest running query your applications might have.
logAbandoned	`false`	Set to `true` to log stack traces for application code that abandons a Connection. Logging an abandoned Connection adds overhead for every Connection open because a stack trace has to be generated.
initSQL (high concurrency JDBC datasource only)		Initial SQL statement that is run only when a connection is first created. Use this feature to set up session settings that should exist during the entire time the connection is established.
jdbcInterceptors (high concurrency JDBC datasource only)	`null`	Semicolon-separated list of classnames extending `org.apache.tomcat.jdbc.pool.JdbcInterceptor`. tc Runtime inserts interceptors in the chain of operations on the `java.sql.Connection` object. Warning: Be sure you do not include any white space (such as spaces or tabs) in the value of this attribute, or the classes will not be found. Predefined interceptors: `org.apache.tomcat.jdbc.pool.interceptor`. `ConnectionState` - keeps track of auto commit, read only, catalog and transaction isolation level. `org.apache.tomcat.jdbc.pool.interceptor. StatementFinalizer` - keeps track of opened statements, and closes them when the connection is returned to the pool.
validationInterval (high concurrency JDBC datasource only)	`30000` (30 seconds)	Number of milliseconds tc Runtime waits before running a validation check to ensure that the JDBC connection is still valid. A connection that has been validated within this interval is not revalidated. Running validation checks too frequently can slow performance.
jmxEnabled (high concurrency JDBC datasource only)	`true`	Specifies whether the connection pool is registered with the JMX server.
fairQueue (high concurrency JDBC datasource only)	`true`	Specifies whether calls to `getConnection()` should be treated fairly in a true FIFO (first in, first out) fashion. This ensures that threads receive connections in the order they arrive. It uses the `org.apache.tomcat.jdbc.pool.` `FairBlockingQueue` implementation to manage the list of idle connections. This feature must be enabled (that is, set the attribute to `true`) to use the asynchronous connection retrieval feature, which is the ability to queue your connection request. Note: When `fairQueue=true` and the operating system is Linux, there is a very large performance difference in how locks and lock waiting is implemented. To disable this Linux-specific behavior and still use the fair queue, add the property `org.apache.tomcat.jdbc.pool.` `FairBlockingQueue.ignoreOS=true` to your system properties before the connection pool classes are loaded.
abandonWhenPercentageFull	`0`	Connections that have been abandoned (timed out) are not closed and reported up unless the number of connections in use is above the percentage defined by this parameter. The value should be between 0 and 100. The default value is 0, which implies that connections are eligible for closure as soon as `removeAbandonedTimeout` has been reached.
maxAge	`0`	Time in milliseconds to keep this connection. When a connection is returned to the pool, the pool checks to see if the `now -time-when-connected > maxAge` has been reached, and if so, it closes the connection rather than returning it to the pool. The default value is 0, which implies that connections are left open and no age check is done upon returning the connection to the pool.
useEquals (high concurrency JDBC datasource only)	`false`	Specifies whether the ProxyConnection class should use String.equals() instead of "==" when comparing method names. Does not apply to added interceptors as those are configured individually. NOTE FOR IBM JVM USERS: If you are running tc Runtime on a platform that uses the IBM JVM (such as AIX), always set the `useEquals` attribute to `true` if you want a high-concurrency connection pool to work correctly. IBM JVMs do not use String literal pools for method names, which means you always want to use `String.equals()` when comparing method names in this case.
suspectTimeout	`0`	Timeout value in seconds. Similar to `removeAbandonedTimeout` but instead of treating the connection as abandoned and potentially closing the connection, this simply logs the warning if `logAbandoned` is set to true. If this value is equal or less than 0, no suspect checking will be performed. Suspect checking only takes place if the timeout value is larger than 0 and the connection was not abandoned or if abandon check is disabled. If a connection is suspect a WARN message is logged and a JMX notification is sent once.
alternateUsernameAllowed	`false`	For performance reasons, by default the JDBC pool ignores the `DataSource.getConnection(username, password)` call and returns a previously pooled connection established using the globally configured properties `username` and `password`. The pool can, however, be used with different credentials each time a connection is used. If you request a connection with the credentials user1/password1 and the connection was previously connected using user2/password2, the connection is closed, and reopened with the requested credentials. This way, the pool size is still managed on a global level, and not on a per schema level. To enable the functionality described in `DataSource.getConnection(username,password)`, set the property `alternateUsernameAllowed` to `true`.

The following server.xml snippet shows how to configure the high-concurrency JDBC datasource for your tc Runtime instance. You can add this datasource to a tc Server Runtime instance by including the diagnostics template in the tcruntime-instance create command line. For an explanation of the following example, see Description of the High Concurrency JDBC Datasource.

<?xml version='1.0' encoding='utf-8'?>
<Server port="-1" shutdown="SHUTDOWN">

 ...

  <GlobalNamingResources>

    <Resource name="jdbc/TestDB"
              auth="Container"
              type="javax.sql.DataSource"
              username="root"
              password="password"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mysql?autoReconnect=true"

              testWhileIdle="true"
              testOnBorrow="true"
              testOnReturn="false"
              validationQuery="SELECT 1"
              validationInterval="30000"
              timeBetweenEvictionRunsMillis="5000"
              maxActive="100"
              minIdle="10"
              maxWait="10000"
              initialSize="10"
              removeAbandonedTimeout="60"
              removeAbandoned="true"
              logAbandoned="true"
              minEvictableIdleTimeMillis="30000"
              jmxEnabled="true"
              jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
                 org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;
                 org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx(threshold=10000)"/>

  </GlobalNamingResources>
  ...
  <Service name="Catalina">
  ...
  </Service>
</Server>

Description of the High Concurrency JDBC Datasource

In the preceding sample server.xml, the <Resource> element does not include a factory attribute, which means that the resource is using the default value, org.apache.tomcat.jdbc.pool.DataSourceFactory, the tc Runtime high-concurrency datasource. The <Resource> element attributes in the example function as follows:

name. JNDI name of this JDBC resource is jdbc/TestDB.
auth. The container signs on to the resource manager on behalf of the application.
type. This resource is a JDBC datasource.
username, password. Name and password of the database user who connects to the database.
driverClassName. tc Runtime should use the com.mysql.jdbc.Driver JDBC driver to connect to the database, in this case a MySQL database.
url. URL that the JDBC driver uses to connect to a MySQL database. The format of this URL is specified by JDBC.
testXXX attributes. tc Runtime validates objects before it borrows them from the connection pool and those objects are validated by the idle object evictor, but that tc Runtime does not validate objects when it returns them to the pool.
validationQuery. tc Runtime runs the very simple SQL query SELECT 1 when it validates connections from the pool before returning a connection to a user upon request. Because this query should always return a value, if it returns an exception then tc Runtime knows there is a problem with the connection.
validationInterval. tc Runtime waits at least 30 seconds before running a validation query.
timeBetweenEvictionRunsMillis. tc Runtime sleeps 5000 milliseconds between runs of the idle connection validation/cleaner thread.
maxActive. tc Runtime allocates a maximum of 100 active connections from this pool at the same time
minIdle. tc Runtime keeps a minimum of 10 established connections in the pool at all times.
maxWait. Where no connections are available, tc Runtime waits a maximum of 10,000 milliseconds for a connection to be returned before throwing an exception.
initialSize. tc Runtime creates 10 connections when it initially starts the connection pool.
removeAbandonedTimeout. tc Runtime waits 60 seconds before it removes an abandoned, but still in use, connection.
removeAbandoned. tc Runtime removes abandoned connections after they have been idle for the removeAbandonedTimeout amount of time.
logAbandoned. tc Runtime flags to log stack traces for application code that abandoned a Connection.
minEvictableIdleTimeMillis. Minimum amount of time an object may sit idle in the pool before it is eligible for eviction on this tc Runtime is 30,000 milliseconds.
jmxEnabled. This tc Runtime can be monitored using JMX. You must set this attribute to true if you want HQ to monitor the resource.
jdbcInterceptors. List of interceptor classes associated with this datasource.

For complete documentation about the tc Runtime server.xml file and all the possible XML elements you can include, see Apache Tomcat Configuration Reference.

Prev	Up	Next
4.2 Simple tc Runtime Configuration	Home	4.4 Configuring SSL