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

The only reasonable way to know how many connections you need is by doing monitoring and adjustments. That's because the relation between the connection acquisition time, the pool size, and the incoming request throughput is given by Little's Law, therefore the pool size depends on how many request are coming and how long are you willing to wait prior to obtaining a connection.

FlexyPool is an open source framework that allows you to monitor connection usage, and it can even increase the pool size beyond the initial capacity.

FlexyPool collects the following metrics:

• concurrent connections histogram
• concurrent connection requests histogram
• data source connection acquiring time histogram
• connection lease time histogram
• maximum pool size histogram
• total connection acquiring time histogram
• overflow pool size histogram
• retry attempt histogram

It supports almost all major connection pooling solutions:

• Apache DBCP
• Apache DBCP2
• C3P0
• BoneCP
• HikariCP
• Tomcat CP
• Vibur DBCP
• Bitronix Transaction Manager
• Atomikos TransactionsEssentials
• Java EE DataSources

It uses Codahale/Dropwizard Metrics so you can integrate it with Graphana or Graphite.

So, back to your question. You can start with a small pool size (5 connections) and configure an overflow buffer of 5 extra connections. You can set up the timeout interval according to your application SLA (100 ms). Then, you can monitor the connection pool usage as explained in this article.

Answer 2

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 3

You have to use third party connection pool like c3p0. 20 to 30 connctions required for 100 concurrecnt user. You have to do the perfomance testing using some tool(like jmeter). Using perfomance tool you can send the n number of concurrecnt request. based on that report you may increse or decrese the connections size.

Answer 4

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.