JDBC Tuning- Part 1

Before we start please note all R&D should be done in integration/staging environment and not in production , expectation is all bugs/app leaks are closed before you move to production.

Sometime (particularly when you have too large DB/too many DB connected to a single jBoss instance) jBoss throws   No Managed Connections available and timeout error.

This means that all connections to the database are saturated or a thread has timed-out waiting for a connection to open up. Now this could happen due to multiple reasons.

1. Predominantly we forget to close connection in code

Solution- Stop leaking any open database connection in app. Go to $deploy/jbossjca-service.xml

and add  <attribute name="Debug">true</attribute>, this will allow jBoss to throw a message like

“Closing a statement you left open, please do your own housekeeping”

1. Connection pool size

Solution- Increase your connection pool size in datasource file

<max-pool-size>20</max-pool-size>

(Default is 20)

1. Thread time out

Solution- Increase thread wait time just add below script in  your datasource file

<blocking-timeout-millis>xxx</blocking-timeout-millis>

Note: xxx should be much more than timeout value

1. Old JDBC Driver

Solution- update driver, it is a JAR file, download and copy into  JBoss  jboss-as/server/myinstance/lib

Happy Tuning :)