commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Natarajan Arun" <natarajan_a...@bah.com>
Subject Re: DBCP oracle connections less than minActive ?
Date Thu, 04 Dec 2003 20:13:38 GMT
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: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message