commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bill Davidson (JIRA)" <>
Subject [jira] Created: (DBCP-301) maxOpenPreparedStatements needs a better default or at least better documentation.
Date Thu, 22 Oct 2009 21:51:59 GMT
maxOpenPreparedStatements needs a better default or at least better documentation.

                 Key: DBCP-301
             Project: Commons Dbcp
          Issue Type: Improvement
    Affects Versions: 1.2.2
         Environment: Tomcat 6.0.20, Sun Java 1.6.0_16
            Reporter: Bill Davidson

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

I'm sure that it could probably be worded better.  I'm just trying to get the idea across.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message