GeoServer

Unbounded prepared statement pooling can bring Oracle to its knees

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: None
  • Fix Version/s: 2.0.1
  • Component/s: None
  • Labels:
    None
  • Number of attachments :
    0

Description

For most databases prepared statement caching is sort of cheap. For Oracle, it's not, each prepared statement keeps a cursor open on the server side.
Cursors are usually limited by admins being expensive resources to keep around.

Expose a property to limit the number of cached prepared statements.
Limitless does not sound very good in general, we may want to provide this property for any database that uses prepared statements.

Issue Links

depends upon

Bug - A problem which impairs or prevents the functions of the product. GEOT-2893 Unbounded prepared statement pooling can bring Oracle to its knees

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Andrea Aime added a comment -

The default could be 50 per connection, if set to 0 or less than 0 we could stop caching them. Don't really think we need a way to express "limitless" in the configuration, one can always specify a very high number and get to the same result (e.g., 10000 per connection)

Show
Andrea Aime added a comment - The default could be 50 per connection, if set to 0 or less than 0 we could stop caching them. Don't really think we need a way to express "limitless" in the configuration, one can always specify a very high number and get to the same result (e.g., 10000 per connection)
Hide
Permalink
Andrea Aime added a comment -

Sarah, Kris, I just committed a change in GeoTools to expose the max cached prepared statements parameters and kicked the nightly builds to generate a new GS version with the param. You can download the build here:
http://gridlock.openplans.org/geoserver/2.0.x/

If you could download it and give it a quick try before Monday it would be very much appreciated

Show
Andrea Aime added a comment - Sarah, Kris, I just committed a change in GeoTools to expose the max cached prepared statements parameters and kicked the nightly builds to generate a new GS version with the param. You can download the build here: http://gridlock.openplans.org/geoserver/2.0.x/ If you could download it and give it a quick try before Monday it would be very much appreciated
Hide
Permalink
Sarah Haskins added a comment -

I just finished a quick test. I did not run out of cursors, so in that sense the fix worked. I was surprised though, at my "max" setting, a new (additional) connection was spawned instead of swapping statements in and out of the pool. But as long as that is intended, I would say this fix is working.

Show
Sarah Haskins added a comment - I just finished a quick test. I did not run out of cursors, so in that sense the fix worked. I was surprised though, at my "max" setting, a new (additional) connection was spawned instead of swapping statements in and out of the pool. But as long as that is intended, I would say this fix is working.
Hide
Permalink
Andrea Aime added a comment -

I honestly don't know what the expected behaviour is. I've just exposed one of the DBCP configuration parameters:
http://commons.apache.org/dbcp/configuration.html

Show
Andrea Aime added a comment - I honestly don't know what the expected behaviour is. I've just exposed one of the DBCP configuration parameters: http://commons.apache.org/dbcp/configuration.html
Hide
Permalink
Andrea Aime added a comment -

Forgot to close this one when I closed the GEOT issue. If the issue is still present please reopen

Show
Andrea Aime added a comment - Forgot to close this one when I closed the GEOT issue. If the issue is still present please reopen

People

  • Assignee:
    Andrea Aime
    Reporter:
    Andrea Aime
Vote (1)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: