JDBC connector - Configuring pool settings

[#1]

The JDBC Connector that ships with IdentityIQ does use Apache Database Connection Pool libraries to manage a pool of connections. Database connection pools are created for one pool for every combination of database user, password, and URL. So if you have several applications which utilize the same user, password, and url, then all of those applications will all utilize the same database connection pool.

If you wish to control how these are configured, you can add settings (prefixed with "pool.") in the Attributes section of the JDBC Application definition to override the defaults.

<Application connector="sailpoint.connector.JDBCConnector" name="Example JDBC App" type="JDBC">

  <Attributes>

    <Map>

     ...

      <entry key="pool.maxActive" value="10"/>

      <entry key="pool.maxIdle" value="5"/>

      <entry key="pool.maxWait" value="60000"/>

      <entry key="pool.evictRuns" value="300000"/>

      <entry key="pool.minEvictIdle" value="600000"/>

     ...

Here are the defaults used in IdentityIQ:

Setting	Value	Description
maxActive	10	How many active connections are allowed in the pool.  Defaults to 10 connections.  A value of -1 means unlimited.
maxIdle	5	Maximum connections which can sit in the pool idle.  Defaults to 5 connections.
maxWait	60000	Maximum time waiting for a thread.  Defaults to 1 minute (value is in milliseconds).
minEvictIdle	600000	Minimum time the connection can be idle before it gets evicted.  Defaults to 10 minutes (value is in milliseconds).
evictRuns	300000	How often to run the eviction thread.  Defaults to 5 minutes (value is in milliseconds).

For more information on what these settings mean, visit: DBCP - Configuration

If you wish to disable connection pooling, you can add the following:

<Application connector="sailpoint.connector.JDBCConnector" name="Example JDBC App" type="JDBC">

  <Attributes>

    <Map>

     ...

      <entry key="pool.disablePooling" value="true"/>

Connection pooling is on by default.

[daniel_neubert]

Hi Neil McGlennon.

Thanks for the insightful explanation of the details about the application pool settings.

Just one more question: If there are many JDBC applications in an IIQ instance does any of these applications

maintain it's own connection pool or is the connection pool shared between the applications of the (same) type JDBC?

Thanks,

Daniel

[neil_mcglennon]

Good question.  I just updated the document to reflect this answer. 

If there are many JDBC applications it creates one pool for every combination of database user, password, and url.  So if you have 3 applications, which all connect using the same username, password, and url, then those 3 applications will all use the same database connection pool.

Hope this helps.

[martintduffy]

If you add the settings for each application that has the same database use, password and url, does each application definition share the same pool or does it have it own pool? What if I put different settings in different application definitions?

[victor_dimare]

Hi Neil,

The link above to the Apache site does not seem to show a property for maxActive, but it does show one for maxTotal. Did maxTotal replace maxActive?

Thanks

[mike_klug]

Looks like tomcat still uses maxActive:

Apache Tomcat 8 (8.0.53) - The Tomcat JDBC Connection Pool

[victor_dimare]

Thanks Mike. 

I was going off what was on this link - DBCP – BasicDataSource Configuration .  Is the JDBC application using Tomcat for pooling no matter what the underlying app server is that IIQ runs on?  We run on JBoss currently.

[joshua_malavolti]

@victor_dimare  maxTotal is a dbcp2 property that  replaced maxActive.  Currently IIQ (7.3p1) uses dbcp 1.4 (Copyright end is 2010) which still utilizes maxActive.

[jems_team]

IdentityIQ 8.0 uses DBCP 2.5, which recognizes maxTotal vs maxActive.

We changed iiq.properties to use maxTotal. 

Should we also change the attributes section of the JDBC Application definition to use maxTotal?

[david_jones]

In addition to the above, I believe these will work as well for checking the threads before use. 

testOnConnect	(boolean) The indication of whether objects will be validated when a connection is first created. If an object fails to validate, it will be throw SQLException. Default value is false
testOnReturn	(boolean) The indication of whether objects will be validated before being returned to the pool. The default value is false.
testWhileIdle	(boolean) The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool. The default value is false and this property has to be set in order for the pool cleaner/test thread is to run (also see timeBetweenEvictionRunsMillis)
validationQuery	(String) The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is null. If not specified, connections will be validation by the isValid() method. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server)

      <entry key="pool.testOnConnect" value="true"/>

      <entry key="pool.testOnReturn" value="true"/>

      <entry key="pool.testWhileIdle" value="true"/>

      <entry key="pool.validationQuery" value="See Below"/>

Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server)