Tomcat Connection Pool Concept & c3p0 connection pooling?

Question

I have two Java Web Applications running under Tomcat (6.0) and using the Tomcat & c3p0 connection pool as Tomcat Data-source.If I define two Resources (server.xml) for two different Oracle Connection and use c3p0 for connection pool as below which my we applications use, my questions are:

<Resource 
name="jdbc/OracleDB" 
auth="Container" 
type="com.mchange.v2.c3p0.ComboPooledDataSource"
driverClass="oracle.jdbc.OracleDriver"
factory="xxx"
jdbcUrl="jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx) (PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxx)))"
maxPoolSize="10" 
minPoolSize="0" 
maxIdleTime="60" 
maxConnectionAge="600"
acquireIncrement="1"
user="xxx="
password="xxx=" />


<Resource name="jdbc/xxx2DB" 
auth="Container"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
driverClass="oracle.jdbc.OracleDriver"  
factory="xxx"   
jdbcUrl="jdbc:oracle:thin:@xxx:1527:xxx"
maxPoolSize="10" 
minPoolSize="0" 
acquireIncrement="1"
maxIdleTime="60" 
maxConnectionAge="600"
user="xxx" 
password="xxx" 
/> 

Q1. Does the below in server.xml mean that two connection pools are existing in the Tomcat memory to two different Oracle Instances?

Q2. Do I have to specify any configuration properties (ref:http://www.mchange.com/projects/c3p0/index.html#configuration_properties) , in my case I have a connection to an Oracle RAC instance and another to a single instance of Oracle. Should I take into account any additional configuration properties in an enterprise environment ?

Q3. Are the below setting valid enough?

Q4. How do I enable c3p0 logging (i have only the jar in Tomcat lib and the above setting for now?

Any advice?

Thanks in advance.

Answer 1

A1: Yes

A2: Looks right at first glance. You need to be careful with RAC in case you would use distributed transactions - then I believe you would have to specify additional properties.

A3: Looks ok but depending on your task and estimated load I would play with maxIdleTime, acquireIncrement and maxPoolSize. For example with current settings after 60 seconds of inactivity your connection will be closed so next operation requesting connection after 60 seconds would incur penalty in waiting for new connection to open.

A4: http://www.mchange.com/projects/c3p0/index.html#configuring_logging

Advice:

1. There is a better connection pool from Tomcat 7, which could be used in Tomcat 6 as well. Consider it, especially if you need high performance

2. Consider connection testing so your application don't get (or get less frequently) exception when db connectivity goes down

3. It might be preferable to package connection pool and it's configuration within your application vs. in Tomcat. This way application WAR would be self contained so it could be just dropped into wabapps dir of tomcat.

Answer 2

A1: Yes.

A2: Not sure if I got the question right, but you should only specify those connection properties which defaults do not make sense to you. It's probably best to do this in the separate c3p0.properties file and specify which connection which property applies to. Answer to 'additional configuration properties' question requires to know the specifics of your environment. I would recommend looking at idle_test_period setting as these can typically have wrong defaults (make sure this matches relevant setting on the DB end).

A3: Yeah, this looks OK.

A4: You need to specify logging preferences in c3p0.properties or define them in dynamically as System properties. Refer to this chapter of the c3p0 manual.