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 minEvictableIdleTimeMillis. This 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.
