We often get ourselves in a situation where we are not able to find the root cause of connection leak and connection timeout.

Below are some tips to monitor and tune JNDI HIKARI connection with Liferay.

Mostly we get two issues with HIKARI if database performance is not up to the mark.

1. connection is not available request timed out after ***** ms

2. lock wait timeout exceeded try restarting transaction

We need to analyze these points if there can’t be anything done from the Database side.

1. Increase pool size

2. Connection time increase

3. Check if there is any connection leak from code

4. Put proper connection configuration

Before doing the changes we need to enable connection monitoring in glowroot for the HIKARI as well.

To verify the problem is from database side we need to check the response time and slow traces

For example in the below scenario we can easily identify database query execution is eating up all the time not the JVM. 

1. Click on slow traces

2. Select any one of the slowest transaction

1. We can clearly see the JDBC query is taking the most time.

2. Next we can do is enable HIKARIconnection monitoring

1. To enable these go to the configuration

1. Go to the gauge and click on add new, search for HIKARIand select the one you want to monitor and enable the above parameters.

1. But it will be only be available for selecting if we have added below configuration true in server.xml JNDI configuration

registermbean=true

1. You will be able to monitor the JNDI connection now and identify connection leaks and make your decisions according to the utilization.

• To automatically remove abandoned connection from the pool:

 removeAbandoned=”true”

•  To increase the pool size 

 maximumPoolSize=”50”   (By default it is 10)

•  To set time for considering a connection as a leak connection, set leakDetectionThreshold property, removeAbandonedTimeout for removing the connection from pool after a certain time if it is idle for that much time. 

After doing all these steps your connection is not available and the issue will be solved.