How to find a reasonable size for a database connection pool and how to verify it?

Question

I am wondering what would be a reasonable number for my connection.pool_size? to what aspects is it related? Also need to know how to test the application once a size is defined for it.

My application is going to be used by AT LEAST 100 users concurrently, it has more than 20 tables in its database. My database is MySQL and AT LEAST 12 systems are using my application at the same time. Please let me know if you need to know more.

I have also found the following which helps to define the connection pool size but still not sure what the reasonable number is.

    Hibernate's own connection pooling algorithm is, however, quite rudimentary.
    It is intended to help you get started and is not intended for use in a production 
    system, or even for performance testing. You should use a third party pool for 
    best performance and stability. Just replace the hibernate.connection.pool_size 
    property with connection pool specific settings. This will turn off Hibernate's 
    internal pool. For example, you might like to use c3p0.

    connection.pool_size indicates the maximum number of pooled connections. So it is 
    better to keep it at a logical count. It depends on your application and DB how 
    much it can handle. 10 is a reasonable count that will typically used as it is 
    sufficient for most cases.

My hibernateUtil is as following

    import org.hibernate.HibernateException;
    import org.hibernate.Session;
    import org.hibernate.SessionFactory;
    import org.hibernate.cfg.Configuration;
    import org.hibernate.service.ServiceRegistry;
    import org.hibernate.service.ServiceRegistryBuilder;

    public class HibernateUtil {

       private static ServiceRegistry serviceRegistry;
       private static final ThreadLocal<Session> threadLocal = new ThreadLocal();
       private static SessionFactory sessionFactory;
        private static SessionFactory configureSessionFactory() {
            try {
                Configuration configuration = new Configuration();
                configuration.configure();
                serviceRegistry = new
ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry( );
                sessionFactory = configuration.buildSessionFactory(serviceRegistry);
                return sessionFactory;
            } catch (HibernateException e) {
                System.out.append("** Exception in SessionFactory **");
                e.printStackTrace();
            }
           return sessionFactory;
      }

      static {
        try {
          sessionFactory = configureSessionFactory();
        } catch (Exception e) {
          System.err.println("%%%% Error Creating SessionFactory %%%%");
          e.printStackTrace();
        }
      }

      private HibernateUtil() {
      }

      public static SessionFactory getSessionFactory() {
        return sessionFactory;
      }

      public static Session getSession() throws HibernateException {
        Session session = threadLocal.get();

        if (session == null || !session.isOpen()) {
          if (sessionFactory == null) {
            rebuildSessionFactory();
          }
          session = (sessionFactory != null) ? sessionFactory.openSession() : null;
          threadLocal.set(session);
        }

        return session;
      }

      public static void rebuildSessionFactory() {
        try {
          sessionFactory = configureSessionFactory();
        } catch (Exception e) {
          System.err.println("%%%% Error Creating SessionFactory %%%%");
          e.printStackTrace();
        }
      }

      public static void closeSession() throws HibernateException {
        Session session = (Session) threadLocal.get();
        threadLocal.set(null);

        if (session != null) {
          session.close();
        }
      }
    }

Answer 1

you must test it with your actual framework how much minimum and maximum connection pool you will use. according to this article :

Small Connection pool :

Will have faster access on the connection table. But may not have enough connections to satisfy requests and requests may spend more time in the queue.

Large Connection pool:

Will have more connections to fulfill requests and requests will spend less (or no) time in the queue at the cost of slower access on the connection table.

so you must test with some connection pool, do some load testing. Also consider getting performance/resource usage information on the current load, and doing some transaction cost based analysis.

And by the result of the analysis, if the access to the connection table are too slow then you can decrease the connection pool, or if the connection is not enough you can add more connection pool. Balance those factor to get optimal time lapse.

Answer 2

If you are using some application server (Jboss, Weblogic, Glassfish, etc...), this guy can show you some statistics on your pool usage. Analise some of this data (max queue time, max connections in use, etc) and run some tests to find what numbers fit your case best.