View source for Projects:DB Pool Logging Utilities/QA

{{(!)| The use of external connection pools is available from '''3.0PR14Q2'''}}

{{(!)| This module was sponsored through [http://www.agilityerp.com 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
* [http://commons.apache.org/proper/commons-dbcp/ 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 [http://centralrepository.openbravo.com/heartbeat-server/org.openbravo.forge.ui/ForgeModuleDetail/Apache-External-Connection-Pool free commercial module] that implements the [http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html 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">........

######################
# DB connection pool #
######################

db.externalPoolClassName=org.openbravo.apachejdbcconnectionpool.JdbcExternalConnectionPool

# 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 [http://wiki.openbravo.com/wiki/Modules:Apache_JDBC_Connection_Pool#How_does_the_Sweeper_thread_work.3F 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 <code>true</code>, 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 [https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Common_Attributes here]. [http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency 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 [http://wiki.openbravo.com/wiki/Openbravo_3_Architecture#Introducing_Weld:_dependency_injection_and_more here]. 
* The interceptor must be a subclass of [https://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/pool/JdbcInterceptor.html 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>