First of all, Sorry for messing up the emails. By mistake I had my question
posted in some other thread.
Thanks for responding John. It was a typo in the email i sent. I checked to
make sure there was nothing wrong with the numbers passed:
connectionPool.setMaxActive(20);
connectionPool.setMinIdle(5);
connectionPool.setMaxIdle(20);
Also the output shows
getNumActive/getMaxActive 15/20 getNumIdle/getMaxIdle0/20.
So, the getMaxActive does return 20. while at the same time Oracle does not
seem to have that many connections.
The problem might have been that I was calling a static test method from a
servlet, but was creating a pool object in the test method. That local pool
object might have been cleaned up after the method execution was complete???
Anyway, now I have cleaned all the mess and am trying to set proper values
for my minIdle, maxIdle, maxActive and am a little confused even after
reading explanations for these in the mail archives. Could someone confirm
the following??
+ When i start my application I want to initially start up 's' connections.
==> setMinIdle(s)
+ I never want the application to run out of connections. ==> Do not set
maxActive, or set it to some value and set
connectionPool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_GROW)
+ If during some peak time the number of connections go up to 'p', then I
want to retain all of them for say the next 10 minutes, after which I want
the number to go down back to 's' or whatever number is currently being
used.
==> connectionPool.setMinEvictableIdleTimeMillis(10*60*1000) OR
connection.timeBetweenEvictionRunsMillis()
I set s to be 5 and started my application in which I call a method that
requests for 25 connections in a loop, not closing any of them. After the
method call returns, I see 12 connections in oracle. Why 12 and not 25 that
shud have been maintained ?
Status after 25 connections were requested:
PRE: getNumActive/getMaxActive24/8 getNumIdle/getMaxIdle0/8
POST: getNumActive/getMaxActive25/8 getNumIdle/getMaxIdle0/8
Connection_24 required 141 millisecs to be obtained.
Thanks,
Arun N
============================================================================
======================================
getNumActive will return the number of connections you have borrowed but
> >not yet returned.
> >getMaxActive will return the maximum number of connections you should be
> >able to borrow at the same time.
> >In other words, it should be the case that getNumActive() <=
> >getMaxActive() at all times
> >getNumIdle will return the number of connections sitting idle in the
pool,
> >ready to be borrowed.
> >It is possible to borrow connections when getNumIdle is 0, it just means
a
> >new connection will need to be created.
> >getMaxIdle will return the maximum number of connections that can be
idle
> >in the pool at any time.
> >If you return (close) a connection when
> >getNumIdle() == getMaxIdle(), then an idle connection will be discarded.
> >As with num/maxActive, it should always be the case that getNumIdle() <=
> >getMaxIdle() at all times.
==========================================
Hi,
I am using DBCP for a web application. I have a class that initializes
the
connection parameters(min/max values for idle/active) and also provides
a
getConnection() method. In getConnection() I have debug statements that
print the numActive & maxActive connections and also the numIdle &
maxIdle
connection values. I have a test method that requests for 15 connections
in
a loop and executes a simple qry; i am not closing any of the
connections.
At the end of the loop this is the debug message:
getNumActive/getMaxActive 15/20 getNumIdle/getMaxIdle0/20.
The parameters I had set are:
connectionPool.setMaxActive(20);
connectionPool.setMinIdle(5);
connectionPool.setMaxIdle(20);
When i look at the connections in oracle, I see only 4 connections from
the
java user. Should I not be seeing the 15 active connections? Am i
missing
some other parameters ?
Your code differs from what you say :)
connectionPool = new GenericObjectPool(null);
connectionPool.setMaxActive(maxConn);//20
connectionPool.setMinIdle(minConn);//5
connectionPool.setMaxIdle(maxConn);//20
If you are passing 5 to MaxIdle, the results of Oracle are in line with the
DBCP settings.
--
John Zoetebier
Web site: http://www.transparent.co.nz
Thanks in advance for any help.
Arun N
Here's the output
*****************
Database Drivers were loaded successfuly
Creating conn pool, max:20 min:5
Data source inited successfuly
Starting to get connections
Requesting connection0 at 1070472656437
getNumActive/getMaxActive0/20 getNumIdle/getMaxIdle5/20
Status after returning a connection
getNumActive/getMaxActive1/20 getNumIdle/getMaxIdle4/20
Obtained connection0 at 1070472656500
Requesting connection1 at 1070472656515
getNumActive/getMaxActive1/20 getNumIdle/getMaxIdle4/20
Status after returning a connection
getNumActive/getMaxActive2/20 getNumIdle/getMaxIdle3/20
Obtained connection1 at 1070472656515
Requesting connection2 at 1070472656531
getNumActive/getMaxActive2/20 getNumIdle/getMaxIdle3/20
Status after returning a connection
getNumActive/getMaxActive3/20 getNumIdle/getMaxIdle2/20
Obtained connection2 at 1070472656531
Requesting connection3 at 1070472656531
getNumActive/getMaxActive3/20 getNumIdle/getMaxIdle2/20
Status after returning a connection
getNumActive/getMaxActive4/20 getNumIdle/getMaxIdle1/20
Obtained connection3 at 1070472656546
Requesting connection4 at 1070472656546
getNumActive/getMaxActive4/20 getNumIdle/getMaxIdle1/20
Status after returning a connection
getNumActive/getMaxActive5/20 getNumIdle/getMaxIdle0/20
Obtained connection4 at 1070472656562
Requesting connection5 at 1070472656562
getNumActive/getMaxActive5/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive6/20 getNumIdle/getMaxIdle0/20
Obtained connection5 at 1070472656625
Requesting connection6 at 1070472656625
getNumActive/getMaxActive6/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive7/20 getNumIdle/getMaxIdle0/20
Obtained connection6 at 1070472656687
Requesting connection7 at 1070472656687
getNumActive/getMaxActive7/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive8/20 getNumIdle/getMaxIdle0/20
Obtained connection7 at 1070472656765
Requesting connection8 at 1070472656765
getNumActive/getMaxActive8/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive9/20 getNumIdle/getMaxIdle0/20
Obtained connection8 at 1070472656843
Requesting connection9 at 1070472656843
getNumActive/getMaxActive9/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive10/20 getNumIdle/getMaxIdle0/20
Obtained connection9 at 1070472656921
Requesting connection10 at 1070472656921
getNumActive/getMaxActive10/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive11/20 getNumIdle/getMaxIdle0/20
Obtained connection10 at 1070472656984
Requesting connection11 at 1070472656984
getNumActive/getMaxActive11/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive12/20 getNumIdle/getMaxIdle0/20
Obtained connection11 at 1070472657046
Requesting connection12 at 1070472657046
getNumActive/getMaxActive12/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive13/20 getNumIdle/getMaxIdle0/20
Obtained connection12 at 1070472657109
Requesting connection13 at 1070472657109
getNumActive/getMaxActive13/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive14/20 getNumIdle/getMaxIdle0/20
Obtained connection13 at 1070472657234
Requesting connection14 at 1070472657234
getNumActive/getMaxActive14/20 getNumIdle/getMaxIdle0/20
Status after returning a connection
getNumActive/getMaxActive15/20 getNumIdle/getMaxIdle0/20
Obtained connection14 at 1070472657312
Out of loop
Here's the code that initializes the pool
******************************************
//Load the Drivers.
try {
Class.forName(driver).newInstance();
System.out.println("Database Drivers were loaded successfuly");
} catch (Exception e) {
System.out.println("Database Drivers could not be loaded: Class for name
failed" + e);
}
//Set conn pool parameters.
System.out.println("Creating conn pool, max:" + maxConn + " min:" +
minConn);
connectionPool = new GenericObjectPool(null);
connectionPool.setMaxActive(maxConn);//5
connectionPool.setMinIdle(minConn);//20
connectionPool.setMaxIdle(maxConn);//20
ConnectionFactory connectionFactory = new
DriverManagerConnectionFactory(URL,null);
PoolableConnectionFactory poolableConnectionFactory = new
PoolableConnectionFactory
(connectionFactory,connectionPool,null,null,false,true);
ds = new PoolingDataSource(connectionPool);
try {
//Just making sure the min Connections are created
on startup
for(int i=0; i<minConn; i++){
connectionPool.addObject();
}
} catch(Exception e) {
System.out.println(" Could not create conn pool
objects"+e);
return false;
}
System.out.println("Data source inited successfuly");
return true;
******************************************
Code that tests the conn pool
public static boolean testPool() {
PoolDataSource ds = new PoolDataSource();
System.out.println("in the test method");
ds.initDataSource("jdbc:oracle:thin:rtm/[EMAIL
PROTECTED]:1521:arun","oracle.jdb
c.driver.OracleDriver",20,5);
try {
System.out.println("Starting to get connections");
String sql = "Select * from tab";
for(int i=0; i<15; i++) {
System.out.println("Requesting connection" +
i + " at
" +
Calendar.getInstance().getTimeInMillis());
Connection conn1 = ds.getConnection();
System.out.println("Obtained connection" + i
+ " at "
+
Calendar.getInstance().getTimeInMillis());
Statement st = conn1.createStatement();
st.executeQuery(sql);
//PoolDataSource.CloseConnection(conn1);
}
System.out.println("Out of loop");
} catch (Exception e) { System.out.println("DS " + e);
return false;}
return true;
}
******************************************
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
