Projects:DB Pool Logging Utilities/QA

The use of external connection pools is available from 3.0PR14Q2

This module was sponsored through Agility ERP, and is applicable for managing Openbravo workloads that may generate a large number of concurrent queries to the database.

Introduction

Openbravo uses connection pools to reuse existing connections/prepared statements, avoiding the cost of initiating a connection, parsing SQL etc. By default Openbravo uses two different connection pools:

• Hibernate default connection pool for DAL-related queries
• Apache DBCP for the connections provided by the ConnectionProviderImpl.

From 3.0PR14Q2 it is possible to specify an external connection provider to replace these two connection pools. Openbravo provides a free commercial module that implements the Tomcat JDBC Connection Pool.

Apache JDBC Connection Pool module is included within Openbravo 3.0 distribution as free starting from 3.0PR15Q4

Advantages of using JDBC Connection Pool

• It merges the current two connection pools into one logical pool.
• It provides advanced pool capabilities that if properly configured will improve the database performance for high concurrent environments.
• It allows the addition of custom interceptors. Interceptors allows to act whenever a connection is requested from the pool and whenever any operation is invoked on a connection provided by the pool. They can be used to enhance the functionality in several ways:
  • Add database monitoring
  • Retry queries
  • Cache query results
  • ...

Inclusion in Openbravo.properties

Openbravo will not start using the external connection pool until the module has been installed and the following line has been added to Openbravo.properties:

db.externalPoolClassName=org.openbravo.apachejdbcconnectionpool.JdbcExternalConnectionPool

Pool Configuration

The following default properties configure the behaviour of the pool:

<source lang="java">........

2. DB connection pool #

db.externalPoolClassName=org.openbravo.apachejdbcconnectionpool.JdbcExternalConnectionPool

1. Documentation at http://wiki.openbravo.com/wiki/Modules:Apache_JDBC_Connection_Pool#Pool_Configuration

db.pool.initialSize=1 db.pool.minIdle=5 db.pool.maxActive=10000 db.pool.timeBetweenEvictionRunsMillis=60000 db.pool.minEvictableIdleTimeMillis=120000 db.pool.removeAbandoned=false db.pool.testOnBorrow=true db.pool.testWhileIdle=false db.pool.testOnReturn=false db.pool.validationQuery=SELECT 1 FROM DUAL db.pool.validationInterval=30000

........ </source>

The Apache JDBC Connection Pool can be configured to meet your environment needs. These are the different configured properties in Openbravo.properties:

• db.pool.initialSize: the number of connections that will be established when the connection pool is started. If this value exceeds db.pool.maxActive it will automatically be lowered. Default value is 1.
• db.pool.minIdle: The minimum number of established connections that should be kept in the pool at all times. The idle pool will not shrink below this value during an eviction run, hence the number of actual connections can be between db.pool.minIdle and maximum number of connections limited by database. Default value is 5.
  • Note 1: The connection pool can shrink below this number if db.pool.validationQuery fail and connections get closed.
  • Note 2: The pool can shrink to a smaller number of connections if the db.pool.maxAge attribute is used and the connection that should have gone to the idle pool ends up being closed since it has been connected too long.
• db.pool.maxActive: The maximum number of active connections that can be allocated from this pool at the same time. The default value is 10000.
  • Note 1: This value is so high because the capacity planning is delegated to the database so the value db.pool.maxActive must be very high. At least higher than maximum number of connections of the database.
  • Note 2: If the capacity planning is delegated to the pool (db.pool.maxActive < max_connection) the main advantage is to use the db.pool.maxWait property that are the maximum number of milliseconds that the pool will wait (when there are no available connections and the db.pool.maxActive has been reached) for a connection to be returned before throwing an exception.
• db.pool.timeBetweenEvictionRunsMillis: The number of milliseconds to sleep between runs of the idle connection validation/cleaner thread called sweeper. (See How does the sweeper thread work?). It dictates how often the pool check for idle, abandoned connections, and how often the pool validate idle connection and resize the idle pool. The default value is 60000 (60 seconds) and note than this value should not be set under 1 second.
• db.pool.minEvictableIdleTimeMillis: the minimum amount of time a connection may sit idle in the pool before it is eligible for eviction. Idle connections are checked periodically (if sweeper enabled) and connections that have been idle for longer than db.pool.minEvictableIdleTimeMillis will be released.The default value is 120000 (120 seconds).
• db.pool.removeAbandoned: Flag to remove abandoned connections if they exceed the db.pool.removeAbandonedTimeout property. A connection is considered abandoned and eligible for removal if it has been in use longer than the removeAbandonedTimeout. The default value is false.
  • Note 1: Setting this value to true is very dangerous because any transactions that have been in any state for longer than db.pool.removeAbandonedTimeout will be abandoned and then removed. For example if a background process takes to run 60 minutes and the value of db.pool.removeAbandonedTimeout is 30 minutes and db.pool.removeAbandoned is true, this process will not end because the connection will be abandoned and then removed.
• db.pool.testOnBorrow: The indication of whether connections will be validated before being borrowed from the pool. If the connection fails to validate, it will be dropped from the pool, and the pool will attempt to borrow another one. The default value is true.
  • Note 1: for a true value to have any effect, the db.pool.validationQuery parameter must be set to a non-null string. Idem for the following two tests.
• db.pool.testWhileIdle: The indication of whether connections will be validated while connections are idle. The default value is false.
• db.pool.testOnReturn: The indication of whether connections will be validated after being returned to the pool. If the connections fails to validate, it will be dropped from the pool. The default value is false.
• db.pool.validationQuery: The SQL query that will be used to validate connections from this pool before returning them to the caller or pool. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is “SELECT 1 FROM DUAL”.
• db.pool.validationInterval: avoids excess validation, only run validation at most at this frequency. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The default value is 30000 (30 seconds).
  • Note: If the pool has activated db.pool.testOnReturn or db.pool.testWhileIdle besides the pool already has activated db.pool.testWhileIdle the db.pool.validationInterval becomes more important. For example if a connection is due for validation before being borrowed from the pool (db.pool.testOnBorrow = true) but has been validated previously (db.pool.testWhileIdle=true) within this db.pool.validationInterval interval, it will not be validated.
• db.pool.jmxEnabled (available from PR17Q2): exposes pool information as jmx.
• db.pool.logAbandoned and db.pool.suspectTimeout (available from PR17Q3): when db.pool.logAbandoned is true, connections borrowed from pool and not returned in db.pool.suspectTimeout seconds will be logged together with the stack trace of the code that borrowed them. Regardless db.pool.suspectTimeout, db.pool.timeBetweenEvictionRunsMillis dictates how often the pool looks for abandoned connections. Note that logging of abandoned Connections adds overhead for every Connection borrow because a stack trace has to be generated.

The full list of configurable properties can be found here. This link provides information about about how to configure these properties for high concurrent environments.

How does the Sweeper thread work?

It has already been explained than the sweeper runs every db.pool.timeBetweenEvictionRunsMillis milliseconds. This sweeper can be enabled or disabled:

• Pool sweeper disabled: If the idle pool is larger than maxIdle, the connection will be closed when returned to the pool. In other words, the number of idle connections in the pool can only be between maxIdle and maximum number of connections of the database.
• Pool sweeper enabled: Number of idle connections can grow beyond maxIdle but can shrink down to db.pool.minIdle if the connection has been idle for longer than db.pool.minEvictableIdleTimeMillis.

The sweeper is enabled when one of the following conditions is met:

• db.pool.timeBetweenEvictionRunsMillis>0 AND db.pool.removeAbandoned=true AND db.poolremoveAbandonedTimeout>0
• db.pool.timeBetweenEvictionRunsMillis>0 AND db.pool.suspectTimeout>0
• db.pool.timeBetweenEvictionRunsMillis>0 AND db.pool.testWhileIdle=true AND db.pool.validationQuery!=null
• db.pool.timeBetweenEvictionRunsMillis>0 AND db.pool.minEvictableIdleTimeMillis>0
  • Last condition has been added in version 1.0.9.

By default, the sweeper is enabled because the behavior of the pool provides a better adjustment to the conditions of each environment.

Adding Interceptors

Adding a custom interceptor is very easy. You just need to take the following in consideration:

• The interceptor class must be @ApplicationScoped. This way Openbravo will instantiate it using dependency injection. More about dependency injection here.
• The interceptor must be a subclass of JdbcInterceptor. Then the most useful methods to overwrite are reset and invoke:
  • The reset method is called each time a connection is borrowed from the pool. The method is called after borrowing the connection, and the connection is passed as an argument.
  • The invoke method is called each time an operation on a connection borrowed from the pool is invoked. The invoke method is called before the operation on the connection is actually invoked. Make sure to call to super.invoke to ensure that the operation is finally invoked, unless you want to explicitly prevent the invocation from happening.
• The interceptor must implement the PoolInterceptorProvider interface. This interface only has one method: getPoolInterceptorsClassName. This method must return the full name of the class.

<source lang="java">package org.openbravo.tomcatjdbcconnectionpool;

import java.lang.reflect.Method;

import javax.enterprise.context.ApplicationScoped;

import org.apache.tomcat.jdbc.pool.ConnectionPool; import org.apache.tomcat.jdbc.pool.JdbcInterceptor; import org.apache.tomcat.jdbc.pool.PooledConnection; import org.openbravo.database.PoolInterceptorProvider;

@ApplicationScoped public class TestInterceptor extends JdbcInterceptor implements PoolInterceptorProvider {

 public void reset(ConnectionPool parent, PooledConnection con) {
   // Actions after a connection has been borrowed from the pool
 }

 // Gets invoked each time an operation on Connection is invoked.
 public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
   // Actions before the method invocation
   Object object = super.invoke(proxy, method, args);
   // Actions before the method invocation
   return object;
 }

 @Override
 // Return the full name of the interceptor class
 public String getPoolInterceptorsClassName() {
   String fullClassName = this.getClass().getName();
   return fullClassName + ";";
 }

} </source>