Details
-
Improvement
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.2.2
-
None
-
Tomcat 6.0.20, Sun Java 1.6.0_16
Description
If you set poolPreparedStatements="true", it is important to set the maxOpenPreparedStatements to something reasonable. The default of unlimited is very likely to cause many applications to run out of cursors, since pooled PreparedStatement objects are not actually closed, which means they keep cursors open. I ran out of cursors because of this using Oracle.
I realize that setting the right default is problematic, and the best choice almost certainly varies with which DBMS is being used, how much memory it has available and the nature of the application. Perhaps better warnings in the documentation would make more sense.
The current configuration documentation for these options contains only this warning:
"NOTE - Make sure your connection has some resources left for the other statements."
That really doesn't make it clear that poolPreparedStatements is going to eat up all of your database cursors after a while if you keep connections open for very long (as you might if you have a very active system).
It would be nice to have some sort of warning there letting people know that using poolPreparedStatements but not setting an appropriate maxOpenPreparedStatements has a high probability of causing problems on busy systems. Maybe something like this:
NOTE - When setting poolPreparedStatements="true" it is highly recommended that you also set maxOpenPreparedStatements to a value appropriate to your DBMS and application. Pooled PreparedStatement objects are not closed, and so can keep to many database cursors open on busy systems which can consume all of your cursor resources. The optimal value of maxOpenPreparedStatements will depend upon your DBMS, the application, and your hardware resources but it must be set to something less than the maximum number of open cursors allowed on a Connection by your DBMS.
I'm sure that it could probably be worded better. I'm just trying to get the idea across.