Re: [Geoserver-users] Connection pool issue with an Oracle NG data source in GeoServer

[Jody G. <jod...@gm...>]

Do you only have one data store or several? In production intend to prefer
managing oracle a shared connection pool in tomcat, and access it in each
workspace via JNDI.

The version of geoserver in production is quite old; I encourage you to
read the outstanding vulnerabilities reports and apply some of the
motivations listed :)

You may wish to check the oracle logs for a clue on why connections are
being not returned?

In older GeoServer we were not always as strict about cleaning up a
connection when a failure occurred.  Perhaps failures started 2 weeks ago
and running out of connections is the consequence.
--
Jody Garnett


On Fri, Apr 12, 2024 at 5:45 PM Jean-Christophe Bastin <jcb...@th...>
wrote:

> Hello community,
>
>
>
> I'm asking your help because I'm really lost and I don't know what to do,
> or I don't understand what is wrong.
>
> Let me explain first the different environments, they are 3 :
> *Development*, *Test* and *Production*.
>
> We are working on all of them with a web application with Angular and
> Openlayers, Web API controllers on Windows Servers, with GeoServer as
> layers data sources and an Oracle 12 database.
>
> The issue appears in Production, must come from GeoServer and/or Oracle,
> but I guess it's only GeoServer.
>
>
>
> For each environment :
>
>    - Development :
>       - GeoServer 2.10.0, installed as stand-alone Windows service
>    - Test :
>       - GeoServer 2.23.0, installed as stand-alone Windows service
>    - Production :
>       - GeoServer 2.16.1, BUT HERE, it is installed with Apache Tomcat
>       9.0, not as a stand-alone Windows service (don't ask me why, I can't answer
>       ^^)
>
>
>
> On each environment, an "Oracle NG" data source is configured to share
> spatial data from the database to the web application.
>
> All was working fine since some years, but last weeks we noticed an issue
> that occurs sometime in the Production environnement. I can't reproduce it
> for now on Development and Test.
>
> GeoServer sent an error message "*Rendering process failed, Unable to
> obtain connection : Cannot get a connection, pool error Timeout waiting for
> idle object*".
>
> So I updated (Development/Test/Production) the "max connections" parameter
> of "Oracle NG" data source from 10 to 50, restarted the service to be sure,
> but the error continues to come after sometime.
>
> I daily checked the Oracle sessions for the user (call it map_ora_user)
> associated with the "Oracle NG" GeoServer data source, it keeps most of the
> time between 6-10 sessions, sometimes less but never more.
>
> I tried to connect many times manually to Oracle with the map_ora_user,
> more than 10, plus the already opened sessions from GeoServer, it doesn't
> look like the limitation comes from Oracle database.
>
> So I made researches, and most of the time the solution talks about an
> "Oracle JNDI" data source, that is also configured on Apache/Tomcat side,
> in a context as I see here :
> https://docs.geoserver.org/stable/en/user/tutorials/tomcat-jndi/tomcat-jndi.html
>
>
>
> But many questions come to me reading this :
>
>    - Why the "max connections" parameter doesn't seem to affect the
>    connection pool with the "Oracle NG" data source ? How is it clearly
>    managed ? It looks like it is better explained in the JNDI version than the
>    NG one.
>    - Is the issue could come from some configuration in Apache/Tomcat ?
>    Because this is the only place where GeoServer is installed in another way
>    than in Development or Test.
>    - Is the "Oracle NG" need more configuration (Apache/Tomcat context
>    for example) to manage the connection pool like the JNDI version ? I don't
>    see a parameter that could come from outside GeoServer, like a
>    jndiReferenceName for example.
>    - If I have to migrate my "Oracle NG" connection to an "Oracle JNDI",
>    is there a simple way to adapt all the layers defined from the "Oracle NG"
>    data source ? We have many layers defined, it will take many time to reset
>    them one by one manually.
>    - Do I have to review the installation of GeoServer in Windows service
>    in Production environment ?
>
>
>
> Hope someone already had this kind of issue, and help me to clarify the
> situation.
>
>
>
> Many thanks in advance :-)
>
>
>
> JC
> _______________________________________________
> Geoserver-users mailing list
>
> Please make sure you read the following two resources before posting to
> this list:
> - Earning your support instead of buying it, but Ian Turton:
> http://www.ianturton.com/talks/foss4g.html#/
> - The GeoServer user list posting guidelines:
> http://geoserver.org/comm/userlist-guidelines.html
>
> If you want to request a feature or an improvement, also see this:
> https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
>
>
> Geo...@li...
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>