SkyVault 2.0 5.0 supports Oracle, Microsoft SQL Server, DB2, as well as MySQL and PostgreSQL.
- properties that you SHOULD edit
- properties that you COULD edit
The following table describes the properties that you SHOULD edit:
| Property name | Description | Default value |
|---|---|---|
| db.txn.isolation | The JDBC code number for the transaction isolation level, corresponding to those in the java.sql.Connection class. The value of -1 indicates that the database's default transaction isolation level should be used. For the Microsoft SQL Server JDBC driver, the special value of 4096 should be used to enable snapshot isolation. | -1 |
| db.pool.initial | The number of connections opened when the pool is initialized. | 10 |
| db.pool.validate.query | The SQL query that is used to ensure that your connections are still alive. This is useful if your database closes long-running connections after periods of inactivity. | For Oracle database, use SELECT 1 from dual For MySQL database, use SELECT 1 For SQL Server database, use SELECT 1 For PostgreSQL database, use SELECT 1 |
The following table describes the properties that you COULD edit:
| Property name | Description | Default value |
|---|---|---|
| db.pool.statements.enable | A Boolean property. When set to true it indicates that all pre-compiled statements used on a connection will be kept open and cached for reuse. | true |
| db.pool.statements.max | The maximum number of pre-compiled statements to cache for each connection. The SkyVault default is 40. Note that Oracle does not allow more that 50 by default. | 40 |
| db.pool.idle | The maximum number of connections that are not in use kept open. | 10 |
| db.pool.max | The maximum number of connections in the pool. See the Note below for more information on this property. | 275 |
| db.pool.min | The minimum number of connections in the pool. | 10 |
| db.pool.wait.max | Time (in milliseconds) to wait for a connection to be returned before generating an exception when connections are unavailable. A value of 0 or -1 indicates that the exception should not be generated. | -1 |
| db.pool.validate.borrow | A Boolean property. When set to true it indicates that connections will be validated before being borrowed from the pool. | true |
| db.pool.validate.return | A Boolean property. When set to true it indicates that connections will be validated before being returned to the pool. | false |
| db.pool.evict.interval | Indicates the interval (in milliseconds) between eviction runs. If the value of this property is zero or less, idle objects will not be evicted in the background. | 600000 |
| db.pool.evict.idle.min | The minimum number of milliseconds that a connection may remain idle before it is eligible for eviction. | 1800000 |
| db.pool.evict.validate | A Boolean property. When set to true it indicates that the idle connections will be validated during eviction runs. | false |
| db.pool.abandoned.detect | A Boolean property. When set to true it indicates that a connection is considered abandoned and eligible for removal if it has been idle longer than the db.pool.abandoned.time. | false |
| db.pool.abandoned.time | The time in seconds before an abandoned connection can be removed. | 300 |
The db.pool.max property is the most important. By default, each SkyVault instance is configured to use up to a maximum of 275. All operations in SkyVault require a database connection, which places a hard upper limit on the amount of concurrent requests a single SkyVault instance can service (that is, 40), from all protocols, by default.
Most Java application servers have higher default settings for concurrent access (Tomcat allows up to 200 concurrent HTTP requests by default). Coupled with other threads in SkyVault (non-HTTP protocol threads, background jobs, and so on) this can quickly result in excessive contention for database connections within SkyVault, manifesting as poor performance for users.
[number of application server worker threads] + 75.For a Tomcat default HTTP worker thread configuration, and with all other SkyVault thread pools left at the defaults, this means this property should be set to at least 275.
db.pool.max=275For clarity, add this property immediately after the other database properties.
The precise mechanism for reconfiguring your database's connection limit depends on the relational database product you are using; contact your DBA for configuration details.