Apache Tomcat JDBC Connection Pool configuration for Oracle Real Application Cluster(RAC)


In this article, we 'll focus on creating a high-concurrency connection pool for Apache  Tomcat. You can check a  simple tomcat JDBC connection pool which added below. We create a TestDB connection pool that connects the Oracle  Database server. The first configuration that we notice "factory". When the tomcat process reads "javax.sql.DataSource"  it will automatically configure DBCP and the factory object will be used to create a connection pool. So you have to define factory object. It's required.

There are many ways to configure  JDBC connection in Apache Tomcat.I'll focus one of them at this post.

<Resource type="javax.sql.DataSource"
name="jdbc/TestDB"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = Rac1-vip)(PORT = 1251))(ADDRESS = (PROTOCOL = TCP)(HOST = Rac2-vip)(PORT = 1251)(FAILOVER=YES)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = SID.WORLD)))"
username="test"
password="test"
/>

Configure  Global  Connection  Pool 

File:conf/server.xml

First, you need to add your resource information to the Global resource tab.

<GlobalNamingResources>
<Resource type="javax.sql.DataSource"
name="jdbc/TestDB"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = Rac1-vip)(PORT = 1251))(ADDRESS = (PROTOCOL = TCP)(HOST = Rac2-vip)(PORT = 1251)(FAILOVER=YES)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = SID.WORLD)))"
username="test"
password="test"
/>
</GlobalNamingResources>

File:conf/context.xml

Then you have to create a ResourceLink to make the pool available to the web applications. If you want to make the pool available for all web applications you have to define this configuration in context.xml file.

<Context>
<ResourceLink type="javax.sql.DataSource"
name="jdbc/TestDB"
global="jdbc/TestDB"
/>

Sizing  Connection Pool

We'll work on this sizing parameters but you can get  all of them from the  link.(The Tomcat JDBC Connection Pool)

  • initialSize
  • maxActive
  • maxIdle
  • minIdle
  • timeBetweenEvictionRunsMillis
  • minEvictableIdleTimeMillis
  • validationQuery
  • validationInterval
  • testOnBorrow
  • removeAbandoned
  • removeAbandonedTimeout

It's quite easy to understand and configure this parameter but there are some key points that we need to talk about.

<Resource
name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
initialSize="10"
maxActive="377"
maxIdle="233"
minIdle="89"
timeBetweenEvictionRunsMillis="34000"
minEvictableIdleTimeMillis="55000"
validationQuery="SELECT 1 FROM DUAL"
validationInterval="34000"
testOnBorrow="true"
removeAbandoned="true"
removeAbandonedTimeout="55"
username="xxx"
password="yyy"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = Rac1-vip)(PORT = 1251))(ADDRESS = (PROTOCOL = TCP)(HOST = Rac2-vip)(PORT = 1251)(FAILOVER=YES)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = SID.WORLD)))"
/>

 

initialSize: The initial number of connections when the connection pool starts.As you can check it above we defined it on the Global Resource tab. So these connections will start when you start Tomcat service.

maxActive: The maximum number of active connections that can be allocated from this pool at the same time. The default value is 100. It's important for the database side when you need to make a capacity plan for Database servers.

minIdle: The minimum number of active connections that always established after pool created and connection has reached this size. In some cases, it depends on the maxAge parameter. If you set the maxAge parameter this can shrink your active connections.

maxIdle: The maximum number of connections that should be kept in the pool at all times. But it works differently when pool sweeper is enabled. Pool sweeper is a background process and manages and test idle connections when the pool is active. Pool sweeper has another critical mission responsible for connection leak detection. When pool sweeper is enabled it runs every timeBetweenEvictionRunsMillis miliseconds. If you don't define this parameters  defaul value  is  5000.

maxIdle is defined;

  • Pool Sweeper  disabled : (Idle Pool  >  MaxIdle ) : Close Connection which  return the  pool
  • Pool Sweeper enabled: Idle connection can grow beyond maxIdle but the minEvictableIdleTimeMillis parameter will be important for this case. The number of connections can shrink down to minIdle if the connection has been an idle state for longer then minEvictableIdleTimeMillisThis means that connection will not close althoug the idle pool is larger than maxIdle. If pool sweeper sets enable you should define the maxAge parameter. Then it'll check idle connections and will close connection which the last connection time longer then maxAge.
public boolean isPoolSweeperEnabled() {
boolean timer = getTimeBetweenEvictionRunsMillis()>0;
boolean result = timer && (isRemoveAbandoned() && getRemoveAbandonedTimeout()>0);
result = result || (timer && getSuspectTimeout()>0);
result = result || (timer && isTestWhileIdle() && getValidationQuery()!=null);
return result;
}

Validate Connections

Validate  query is an option that checks database status for all Database requests. If you set  validate "validationQuery="SELECT 1 FROM DUAL""  it means that  query  will be  executed each  java  calls for  database (con = dataSource.getConnection();)

So query validation present a few challenges;

  • It can degrade  database  performance if it calls to often. Validate  query  guarantees   that  connection has been tested before it  gets the connection  however  it will affect performance of  database and  application. So you should  set "validationInterval"  to minimize  this  effects. If  you set  validationInverval , connection will be validate until next cycle  of  validationInterval.(validationInterval="34000" for this case  it  will  validate connection every  34 seconds)
  • And  also if you set validate  query , you should set  testOnBorrow to. This parameter should set true to validate the connection and remove it if it's dropped to validate.

 

 

 

 

I'm a IT Infrastructure and Operations Architect with extensive experience and administration skills and works for Interbank Card Center Of Turkey(BKM). I provide hardware and software support for the IT Infrastructure and Operations tasks.

129 Total Posts
Follow Me

0 Comments

Leave a Reply