FarragoMedJdbcDataSourceSupport
From Eigenpedia
Contents |
Overview
This page described enhancements to the SQL/MED JDBC foreign data wrapper for supporting JNDI-stored data sources and connection pooling.
JNDI-stored data sources are useful for situations where Farrago is executing inside the context of an application container. Connection pooling resolves issues with concurrent execution of multiple statements against a single data server. Some JDBC drivers, notable MySQL's, do not support multiple streaming result sets on a single connection which prevents joining two foreign tables from the same JDBC data server.
TODO: update the medjdbc document with new configuration parameters
New Configuration Parameters
New configuration parameters are introduced as follows:
| Option Name | Description | Default Value |
|---|---|---|
| JNDI_NAME | Name of the JNDI resource that contains a javax.sql.DataSource suitable for use with the SQL/MED JDBC foreign data wrapper. The DataSource must not block when connections are requested. This option is mutually exclusive with the existing DRIVER_CLASS, URL, USER_NAME, PASSWORD, and EXTENDED_OPTIONS parameters. The following parameters are ignored if JNDI_NAME is specified: LOGIN_TIMEOUT, VALIDATION_QUERY. | None |
| MAX_IDLE_CONNECTIONS | When creating a connection pool implicitly, this many connections are kept open. If a connection is returned to the pool and this many idle connections are already in the pool, the newly returned connection is immediately closed. Each MedJdbcDataServer instance may hold this many connections open. Ignored if JNDI_NAME is specified. | 1 |
| EVICTION_TIMER_PERIOD_MILLIS | Controls how frequently idle connections are pruned from the pool. If set to a value N > 0 (zero), idle connections are removed from the pool and closed every N milliseconds. Ignored if JNDI_NAME is specified. | -1 (disabled) |
| MIN_EVICTION_IDLE_MILLIS | Controls how long a connection must be idle before it is evicted. If EVICTION_TIMER_PERIOD_MILLIS is disabled, this setting has no effect. Otherwise, when the eviction timer fires, idle connections are only closed if they have been idle for at least this many milliseconds. Ignored if JNDI_NAME is specified. | -1 (disabled) |
| VALIDATION_TIMING | Controls when connections are validated. Multiple values may be specified if separated by commas. The values are case insensitive. Possible values and their meanings: ON_BORROW (validate when connection is borrowed from the connection pool), ON_RETURN (validate when connection is returned to the connection pool), or WHILE_IDLE (validate idle connections when the eviction timer fires and the connection has not been idle long enough to warrant eviction). The WHILE_IDLE value only has effect when both EVICTION_TIMER_PERIOD_MILLIS and MIN_EVICTION_IDLE_MILLIS is positive. This parameter is ignored if JNDI_NAME is specified. This parameter has no effect if VALIDATION_QUERY is not specified. | ON_BORROW |
| DISABLE_CONNECTION_POOL | Reverts connection management behavior to pre-connection pool semantics. A single connection is re-used for all purposes. | false |
Connection Pool Configuration
The interaction between the idle connection parameters is somewhat complex. The MAX_IDLE_CONNECTIONS parameter dictates the connection pool's behavior when a connection is returned to the pool: the connection is closed immediately if there are already at least this many idle connections in the pool. When EVICTION_TIMER_PERIOD_MILLIS is set, the pool is also periodically polled and its idle connections are closed. This means that if all connections are idle when the eviction timer fires, all connections will be closed. The MIN_EVICTION_IDLE_MILLIS parameter modifies the eviction timer to only close connections that have been idle for a given amount of time. Finally, the WHILE_IDLE setting for VALIDATION_TIMING depends on the eviction timer being activated and a minimum eviction idle time: connections are validated "while idle" only if they are not being evicted for other reasons.
Here's an example with the settings MAX_IDLE_CONNECTIONS = 2, EVICTION_TIMER_PERIOD_MILLIS = 10000, MIN_EVICTION_IDLE_TIME = 15000.
| Time (ms) | Event | Open Connections | Number of Idle Connections in Pool | Notes |
|---|---|---|---|---|
| 0 | Connection A Requested | 1 | 0 | New connection created |
| 100 | Connection B Requested | 2 | 0 | New connection created |
| 200 | Connection C Requested | 3 | 0 | New connection created |
| 300 | Connection D Requested | 4 | 0 | New connection created |
| 1000 | Connection B Released | 4 | 1 | |
| 6000 | Connection A Released | 4 | 2 | |
| 6500 | Connection C Released | 3 | 2 | Connection C closed due to MAX_IDLE_CONNECTIONS |
| 10000 | Eviction Timer Event | 3 | 2 | No action: idle connections have not been idle long enough |
| 20000 | Eviction Timer Event | 2 | 1 | Connection B closed: idle > 15000 ms |
| 24000 | Connection D Released | 2 | 2 | |
| 30000 | Eviction Timer Event | 1 | 1 | Connection A closed: idle > 15000 ms |
| 40000 | Eviction Timer Event | 0 | 0 | Connection D closed: idle > 15000 ms |
A separate connection pool is instantiated per FarragoMedDataServer instance. Therefore, the connection pool should be configured with the requirements of a single user session in mind, rather than the requirements of the data server across all sessions. A future enhancement would be to re-use a single connection pool across FarragoMedDataServer instances.
MedJdbcDataServer Changes
initialize Method
During the initialize method, the data server's parameters are validated. Incompatible options (as described above) will cause an exception to be thrown.
A DataSource instance is obtained if JNDI_NAME is set.
Otherwise, a DBCP connection pooling data source is created:
import org.apache.commons.dbcp.*;
import org.apache.commons.pool.impl.GenericObjectPool;
...
ConnectionFactory connFactory =
new DriverManagerConnectionFactory(
url,
username,
password);
GenericObjectPool connPool = new GenericObjectPool();
connPool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_GROW);
connPool.setMaxActive(-1);
// Configurable options:
connPool.setMaxIdle(maxIdle);
connPool.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRuns);
connPool.setMinEvictableIdleTimeMillis(minEvictableTime);
// Registers the PoolableConnectionFactory with the connection
// pool.
new PoolableConnectionFactory(
connFactory,
connPool,
null,
null,
false,
true);
DataSource ds = new PoolingDataSource(connPool);
The auto-commit default (PoolableConnectionFactory argument) will be set to true, and createConnection will set the auto-commit mode as usual. The login timeout is set as before.
Connection Management Methods
The createConnection() method will be removed.
Instead, DBCP is used to open connections and perform validation queries as required.
The logic for configuring connections is moved to initialize.
The getConnection() method will obtain a Connection from the DataSource and will hold the connection until releaseResources() is invoked. In addition, getConnection will obtain a DatabaseMetaData instance for its connection. A method getDatabaseMetaData is introduced to insure that getConnection() is invoked before database metadata is accessed. The getConnection method will be made protected and should only be used by methods associated with DDL. This prevents a connection from being repeatedly borrowed from the connection pool and re-validated during CREATE OR REPLACE SERVER. Assuming ON_BORROW validation timing, this logic produces the same number of validations per prepare/execute cycle as the old wrapper.
During query execution one or more Connection objects are obtained via getRuntimeSupport() as necessary. That method will retrieve connections directly from the data source.
closeAllocation Method
The closeAllocation method will close the connection pool created in initialize. If a JNDI DataSource was used, no action is taken.
Other MedJdbc Changes
Other JDBC plugin classes that require access to the data server's connection or DatabaseMetaData will be modified as needed.
FarragoStatementAllocation
Extend FarragoStatementAllocation to optionally manage a Connection object in addition to the Statement and ResultSet objects it already deals with. This allows pooled Connection objects to be returned to the connection pool when the Farrago statement finishes executing.
