As an administrator, you need to edit some advanced properties to customize your database configuration. Many properties, however, don’t need to be edited.
The advanced database configuration properties are categorized into two groups based on their relevance:
- properties that you SHOULD edit
- properties that you COULD edit
The following table describes the properties that you SHOULD edit:
| Property | Description |
|---|---|
| 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. The default value is -1 |
| db.pool.initial | The number of connections opened when the pool is initialized. The default value is 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.
|
The following table describes the properties that you COULD edit:
| Property | Description |
|---|---|
| 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. The default value is true |
| db.pool.statements.max | The maximum number of pre-compiled statements to cache for each connection. Note that Oracle doesn’t allow more that 50 by default. The default value is 40 |
| db.pool.idle | The maximum number of connections that aren’t in use but kept open. The default value is 10 |
| db.pool.max | The maximum number of connections in the pool. See the note below for more information on this property. The default value is 275 |
| db.pool.min | The minimum number of connections in the pool. The default value is 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 shouldn’t be generated. The default value is 5000 |
| db.pool.validate.borrow | A Boolean property. When set to true it indicates that connections will be validated before being borrowed from the pool. The default value is 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. The default value is 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 won’t be evicted in the background. The default value is 600000 |
| db.pool.evict.idle.min | The minimum number of milliseconds that a connection may remain idle before it’s eligible for eviction. The default value is 1800000 |
| db.pool.evict.validate | A Boolean property. When set to true it indicates that the idle connections will be validated during eviction runs. The default value is 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’s been idle longer than the db.pool.abandoned.time. The default value is false |
| db.pool.abandoned.time | The time in seconds before an abandoned connection can be removed. The default value is 300 |
The db.pool.max property is the most important. By default, each Content Services instance is configured to use up to a maximum of 275. All operations require a database connection, which places an upper limit on the amount of concurrent requests a single instance can service from all protocols.
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 Content Services (non-HTTP protocol threads, background jobs, and so on) this can quickly result in excessive contention for database connections, manifesting as poor performance for users.
If you’re using Content Services in anything other than a single-user evaluation mode, increase the maximum size of the database connection pool to at least the following setting.
[number of application server worker threads] + 75
For a Tomcat default HTTP worker thread configuration, and with all other thread pools left at the defaults, this means this property should be set to at least 275.
To increase the database connection pool, add the db.pool.max property to the alfresco.global.properties file, and set it to the recommended value of 275, for example:
db.pool.max=275
For 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’re using. Contact your DBA for configuration details.