GeoServer
  1. GeoServer
  2. GEOS-636

Scalability: Need to be able to set a maximum connection pool size for database-based datasources

    Details

    • Type: Improvement Improvement
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.3.2
    • Fix Version/s: None
    • Component/s: Oracle, PostGIS
    • Labels:
      None
    • Environment:
      n/a
    • Number of attachments :
      0

      Description

      Currently the connection pooling as implemented in GeoServer (actually Geotools) does not restrict the number of open database connections in any way. For some users it is important to be able to manage this potentially scarce resource by capping the number of database connections. Most of the available connection pooling libraries have this functionality.

        Activity

        Hide
        Corey Puffalt added a comment -

        As an additional note... Currently all the JDBC-based FeatureSources are using their own database-driver-specific connection-pooling scheme. There are benefits in sharing a common connection pooling scheme across all the JDBC-based FeatureSources: a) Less code overall and b) all the JDBC-based FeatureSources would share the same features.) For example, it looks like the org.postgresql.jdbc2.optional.ConnectionPool actually supports a maxConnections parameter but the Oracle equivalent doesn't appear to support that functionality.

        One connection pooling library that I've had success with is C3P0. It seems to be one of the few that supports all the features required for enterprise-class stability (as of v0.9.0). (See http://www.mchange.com/projects/c3p0/ for documentation.)

        Show
        Corey Puffalt added a comment - As an additional note... Currently all the JDBC-based FeatureSources are using their own database-driver-specific connection-pooling scheme. There are benefits in sharing a common connection pooling scheme across all the JDBC-based FeatureSources: a) Less code overall and b) all the JDBC-based FeatureSources would share the same features.) For example, it looks like the org.postgresql.jdbc2.optional.ConnectionPool actually supports a maxConnections parameter but the Oracle equivalent doesn't appear to support that functionality. One connection pooling library that I've had success with is C3P0. It seems to be one of the few that supports all the features required for enterprise-class stability (as of v0.9.0). (See http://www.mchange.com/projects/c3p0/ for documentation.)
        Hide
        Chris Holmes added a comment -

        A few comments from the email list: http://www.nabble.com/Fwd%3A-Connection-Pooling...-t1786794.html is the thread

        Saul says that ArcSDE supports max and timeouts. Corey points out that Postgresql's jdbc driver has support for maxConnections, though oracle's doesn't and it'd be nice to take advantage of a single shared implementation.

        Looking at the ArcSDE code, it looks to be based on Sean's original ConnectionPooling code that we're using. So perhaps we could just backport the max connection and timeout stuff. Then we'd just have the one implementatoin for oracle, mysql and postgres. One thing to point out though is that we'd still have to have custom code for each factory to set the max connections. I would really like to use c3p0, it looks like a great library, but I fear the overhead in switching over. The thing to do would likely to be to have it sit alongside the current stuff, implement it to work with a new factory for each datastore, so that users could easily swap out implementations if one wasn't working...

        Show
        Chris Holmes added a comment - A few comments from the email list: http://www.nabble.com/Fwd%3A-Connection-Pooling...-t1786794.html is the thread Saul says that ArcSDE supports max and timeouts. Corey points out that Postgresql's jdbc driver has support for maxConnections, though oracle's doesn't and it'd be nice to take advantage of a single shared implementation. Looking at the ArcSDE code, it looks to be based on Sean's original ConnectionPooling code that we're using. So perhaps we could just backport the max connection and timeout stuff. Then we'd just have the one implementatoin for oracle, mysql and postgres. One thing to point out though is that we'd still have to have custom code for each factory to set the max connections. I would really like to use c3p0, it looks like a great library, but I fear the overhead in switching over. The thing to do would likely to be to have it sit alongside the current stuff, implement it to work with a new factory for each datastore, so that users could easily swap out implementations if one wasn't working...
        Hide
        Corey Puffalt added a comment -

        You could take a look at how Hibernate does this and perhaps adopt some code from that project as it does exactly that (the connection pooling library is configurable). For example, another ConnectionFactory implementation could delegate to a JNDI DataStore if running GeoServer inside of a J2EE container.

        Show
        Corey Puffalt added a comment - You could take a look at how Hibernate does this and perhaps adopt some code from that project as it does exactly that (the connection pooling library is configurable). For example, another ConnectionFactory implementation could delegate to a JNDI DataStore if running GeoServer inside of a J2EE container.
        Hide
        Andrea Aime added a comment -

        This is being addressed on Geotools trunk as http://docs.codehaus.org/display/GEOTOOLS/Connection+pool+subsystem+upgrade and with a bit of luck will become part of the 1.6.0 release

        Show
        Andrea Aime added a comment - This is being addressed on Geotools trunk as http://docs.codehaus.org/display/GEOTOOLS/Connection+pool+subsystem+upgrade and with a bit of luck will become part of the 1.6.0 release
        Hide
        Andrea Aime added a comment -

        the new connection pooling subsystem addresses this issue using commons DBCP and allowing to setup min/max connection and connection validation (more to come when we ready a user interface to setup all of properties DBCP allows to tune)

        Show
        Andrea Aime added a comment - the new connection pooling subsystem addresses this issue using commons DBCP and allowing to setup min/max connection and connection validation (more to come when we ready a user interface to setup all of properties DBCP allows to tune)

          People

          • Assignee:
            Andrea Aime
            Reporter:
            Corey Puffalt
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: