ESW / Discussion / Open Discussion: Preferred Size of Connection Pool

I got your email with the test class. Im posting it here for reference.

package test;

import java.io.*;
import java.util.*;
import java.sql.*;

import net.sourceforge.esw.util.database.*;

/**
*
* @author Anand Reddy
* @version 1.0
*/
public class ESWTest {

     private static int thrdCnt = 1;
     private static int intPoolSz = 5;
     private static int maxPoolSz = 10;
     private static int slDelta = 100;

     static int connectionCount = 0;
     static final String dbConStr = "myDbConStr";
     static final String dbUser = "myUser";
     static final String dbPwd = "myPwd";

       private void init() {

       try{

           DBConnectionPoolManager.initializeDriver(
"com.inet.tds.TdsDriver" );

           // The DBConnectionPoolManager governs access
           DBConnectionPoolManager poolManager =
DBConnectionPoolManager.getInstance();

        // To use JDBC Database Connections, we will need a connection
pool to
        // get connections from. We can create a pool directly to set
properties
        // on before anyone uses it.
           DBConnectionPool pool = new DBConnectionPool( dbConStr,
dbUser, dbPwd );

         // If we want to keep a certain number of available when the
pool is
        // not in use, we have to set the preferred size on the pool.
Then,
        // whenever connections are returned, the pool will discard any
extras
        // once it already has its preferred number of connections
available.
           pool.setPreferredSize(intPoolSz);
//         System.out.println(" PREFERRED POOL SIZE = " +
pool.getPreferredSize());

        // If we want to limit the maximum number of conncurrent
database
        // connections, then we need to set the maximum size on the
        // DBConnectionPool.
           pool.setMaxSize(maxPoolSz);

          pool.setHoldUntilConnectionAvailable(true);

        // When we set Database Logging to true, all statements that
are
        // executed against the pooled connections are output to the
        // DriverManager's LogWriter. To have the DriverManager output
to
         // System.out, we must set the parameter.
          pool.setDatabaseLogging( true );
          DriverManager.setLogWriter( new PrintWriter( new
OutputStreamWriter( System.out ) ) );

        // Inorder for other clients to access this DBConnectionPool,
we must
        // add it to the DBConnectionPool manager. Then all other
clients can
        // access the pool by asking for it.
           poolManager.addPool( pool );

       }
       catch(Exception ex) {
           ex.printStackTrace();
       }

    }

    private static void runThreads() {
        ArrayList daaralu = new ArrayList();
        Daaramu daaramu = null;

        try {

            for(int i=0;i<thrdCnt;i++) {
              daaramu = new Daaramu(i);
              daaralu.add(daaramu);
              daaramu.start();
              Thread.sleep(slDelta);
            }

            Thread.sleep(5000);

            for(int i=0; i<thrdCnt; i++) {
              daaramu = (Daaramu) daaralu.get(i);
              daaramu.join();
            }

        }
        catch(Exception ex) {
          ex.printStackTrace();
        }
    }


    public static void main (String[] args) {

        ESWTest test = new ESWTest();

        if (args.length <= 0)
        {
            System.out.println(" Supply the main class for this app");
            System.exit(0);
        }

       // initial pool size (preferred pool size) of the con pool -
esw.
           intPoolSz = Integer.parseInt(args[0]);
           System.out.println(" Initial Pool Size = " + intPoolSz);

       // max pool size
           maxPoolSz = Integer.parseInt(args[1]);
           System.out.println(" Maximum Pool Size = " + maxPoolSz);

        // no. of concurrent threads
          thrdCnt = Integer.parseInt(args[2]);
          System.out.println(" Thread count = " + thrdCnt);

         // sleep time between spawning each thread
           slDelta = Integer.parseInt(args[3]);
           System.out.println("Sleep Time between spawning threads = "
+ slDelta);

        test.init();

        try {
              runThreads();
        }
        catch(Exception ex) {
          ex.printStackTrace();
        }

        System.out.println("Program finished execution");

        System.out.println("\n");

    }


     /**
     * inner class for thread
     *
     */
    private static class Daaramu extends Thread {

        private int id = 0;

        // custom constructor
        public Daaramu(int thId) {
            super();
            id = thId;
        }

        public int getId() {

         return id;
        }

        public void run() {

            IDBConnectionPool conPool = null;
            Connection con = null;
            PreparedStatement pstmt = null;
            int updateCnt = 0;

             DBConnectionPoolManager pm =
DBConnectionPoolManager.getInstance();

             for(int k=0; k<9; k++) {

                 try {
                        conPool = pm.getPool(dbConStr,dbUser);
                       con = conPool.getDBConnection();

                       pstmt = con.prepareStatement("insert into Maez
(timepass) values (?)");
                       pstmt.setString(1,"with esw CP + inet driver :
Hello World");

                       pstmt.executeUpdate();

                       if(pstmt != null)
                         pstmt.close();
                 }
                 catch(SQLException sqlEx){
                        System.out.println(" in HERE 1");
                        sqlEx.printStackTrace();
                 }
                 finally {
                   try{
                      if(con != null)
                        con.close();
                   }
                   catch(SQLException sEx) {
                         System.out.println(" in HERE 2");
                         sEx.printStackTrace();
                   }
                 } // end of try

            } // end of for loop

} // end of execute

    } // end of inner class


   } // end of outer class

with a usage of:

ESWTest <preferredPoolSize> <maxPoolSize> <No.of Concurrent threads> <sleeptime between spawning each thread in millis >

Arand:

I've uploaded a patch in the esw patch section. It is a jar that contains the fixed class files. It was only the DBConnectionPool class that was modified. That modification is checked into CVS. If you wish to see what the changes were, please feel free to look at the file via the Web CVS interface. Get the patch (esw-dbconnpool-704281-patch.jar), put it in your classpath before the esw.jar, and retest. Let me know what you find. If it works for you, we will do a full esw build that will include the changes in the patch.

As a side note, make sure that you do not open more connections than your database can handle. I did the testing against PostgreSQL which has a default max connections of 32. If I tried to open more than that, those calls to the database failed.

Please let us know what you find!

Thanks.

Preferred Size of Connection Pool

Forums

Help

Preferred Size of Connection Pool

Preferred Size of Connection Pool

Forums

Help

Preferred Size of Connection Pool document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

Preferred Size of Connection Pool