I am working on an web application hosted on Tomcat 5.5.7 and MySQL 4.1.7 (Linux OS).
We are using DBCP Datasource provide by Tomcat.
The application has some utility code and it provides two interfaces - web application and web service. Both interfaces provide same functionality. Web service interface is used by a standalone simple utility program that users can use from command line.
Recently we are facing severe issues with application, reason being heavy usage of utility program. From the logs we found that single user is using it heavily.
We see that application (datasource connections) is serving mostly to this user and that is why other users are facing issues as they keep getting errors.
Our major problem is with datasource connections as all get consumed by this particular user. We get following errors,
1. org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
2. java.sql.SQLException: Connection is closed
I believe both these errors occur because datasource connection numbers are insufficient for this high usage. I have checked code to see if connections are not released, but that does not seem to be a problem.
Here is our datasource configuration,
Can we improve this configuration? I know maxActive no is very less (we actually increased it from 15 to 30, 15 used to serve well for us before), but we are not sure if increasing this no will help us. If we make it 50 then 50 will be consumed, if we make it 100 then 100 will be consumed.
Do we need to remove maxWait? Or do we need to increase removeAbandonedTimeout? Please provide your suggestions.
Also, I was wondering why web service calls put such a load on system. Why system is serving only one user who is consuming web service through utility code? Do we need to use some thread pool where we make call to utility code like,
Any help is highly appreciated.
## Some more error logs
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
##DBCP object created 2009-04-06 09:31:09 by the following code was never closed:
I checked this code and it releases connection properly, not sure why above error is thrown, Here is code,
We get exception while closing though,
05-04-2009 00:06:40|WARNING|SomeClass.someMethod()|Error closing connection. java.sql.SQLException: Already closed
If you're passing the connection around, perhaps your closing it in another method. The best thing to do is to track that down. You might also consider checking if the connection is closed before closing it in your finally block to avoid getting the exception (though I don't know if this is a best practice). :-)