5.5. Balancing pool size values

The aim of adjusting the min-pool-size and max-pool-size pool values is to allow all possible concurrent database activity to execute, and so maximize throughput. To do this requires an understanding of how much concurrent activity in the server is occurring. As a guide of concurrent activity, look at the platform's thread pools to see how many threads are active. This is available through the administration console, as in the following screenshot:

In this example there were 81 active threads in the JBoss Web HTTP connector, which is the primary thread pool in the test used for this illustration. That number is to be expected because there were 80 users, with no think times between requests, and response times were between 18 and 20 ms on average. Having 81 threads active accounts for the continued use of the 80 users, plus the one user for the administration console itself. Most of the time the actual number of active threads was actually only 80, but when the refresh button is clicked, the administration console consumed a thread during its collection of the data. In this example, you may presume that the maximum size of the connection pool should at least be 80 but it may not be as simple as it appears.

In looking at the data source metrics in the administration console, the “Max Connections In Use Count” is reported as 80. With 80 concurrent users and each transaction performing database transactions, a value of 80 seems obvious. In various situations, though, this figure may be misleading. If “Max Connections in Use Count” equals the max-pool-size parameter then this value is meaningless. It's also possible that the thread pools have many more active threads than threads that will eventually hit the database. Also there may be multiple thread pools in use within the platform, each with their own data access characteristics. Finally, it's possible for an EJB 3 application to skew the number of connections needed.

It's a requirement of the EJB 3 specifications that each bean that has an entity manager injected into it also have a unique instance of the entity manager. This has an indirect influence on the database connection pool sizing. Since Hibernate is the JPA provider, a unique instance of an entity manager and a Hibernate session, this requires a database connection for the queries within that entity manager and Hibernate session. In the case of a simple application, where stateless session beans execute queries and never call other stateless session beans, a single database connection is consumed for that processing. In a more complex application, assume there's a stateless session bean that executes queries but also calls on other stateless sessions beans. Because that also execute queries, there are multiple entity managers, potentially consuming multiple database connections and all participating in the same transaction. In a more complex example again, assume there's a stateless session bean that puts a message on a JMS queue, then does some further processing that executes queries and asynchronously, a message driven bean de-queues that message and does some processing that executes queries. From these examples it's evident that the more complex the application structure, the harder it will be to determine just how many connections may be used. This is even further complicated by the speed at which connections can be returned to the pool for reuse, and how fast they can be enlisted in a transaction when reused from the pool. One technique you can use is to continue increasing the maximum pool size until the “Max Connections In Use Count” stays below the maximum size parameter of the pool. This may not be possible in a production environment so a test environment is recommended.

There's one more check that can be made to determine if the connection pool is too small and so might be throttling throughput. During peak loads you can use the JMX console to take a thread dump of the running platform. If in the output you see the following the connection pool is too small:

Thread: http-192.168.1.22-8080-2 : priority:5, demon:true, threadId:145, threadState:TIMED_WAITING
- waiting on <0x2222b715> (a java.util.concurrent.Semaphore$FairSync)
sun.misc.Unsafe.park(Native Method)
java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:226)
java.util.concurrent.locks.AbstractQueuedSynchronizer.doAcquireSharedNanos(AbstractQueuedSynchronizer.java:1037)
java.util.concurrent.locks.AbstractQueuedSynchronizer.tryAcquireSharedNanos(AbstractQueuedSynchronizer.java:1326)
java.util.concurrent.Semaphore.tryAcquire(Semaphore.java:410)
org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:193)
org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:747)
org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:404)
org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:424)
org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:496)
org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:941)
org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)
org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:47)
org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)

In this example thread dump from the ServerInfo section of the JMX Console's jboss.system category (where you can invoke a listThreadDump operation) you can see that the thread is in a TIMED_WAITING state and waiting on a semaphore. If you look closely at the stack trace you also see that the InternalManagedConnectionPool is executing its getConnection() method. In the connection pool, if no connection is available for use it waits on a semaphore, which has the same number of tickets available as the max-pool-size parameter. If a transaction is waiting for a connection to become available in the pool, it waits until one is returned to the pool. This has an advantage over continuing to increase the pool size because it's possible to see just how many threads are waiting on a connection from the pool. More accurate information means it takes fewer iterations to get the pool's size right.

In closing discussion on the pool size, a rule of thumb is to set the minimum to the size which allows for maximum throughput and the maximum to 10% to 20% higher than the minimum. Setting the maximum higher than the minimum allows a buffer for situations in which load unexpectedly increases, a situation almost guaranteed to occur in real life.