Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
Thanks, Andrea, that sounds like the cause. We'll upgrade and see if the
problem goes away.
On Tue, Sep 1, 2015 at 11:25 PM, Andrea Aime
wrote:
> On Wed, Sep 2, 2015 at 1:21 AM, Martin Davis wrote:
>
>> We've run some DB logging on the map requests.
>>
>> One of the causes of the slowdown for no schema is this query:
>>
>> SELECT NULL AS table_cat,
>>o.owner AS table_schem,
>>o.object_name AS table_name,
>>o.object_type AS table_type,
>>NULL AS remarks
>> FROM all_objects o
>> WHERE o.owner LIKE :1 ESCAPE '/'
>> AND o.object_name LIKE :2 ESCAPE '/'
>> AND o.object_type IN ('xxx', 'TABLE', 'VIEW', 'SYNONYM')
>> ORDER BY table_type, table_schem, table_name
>>
>> If no owner is specified this returns 38K rows, as opposed to about 240
>> when owner is given.
>>
>> I'm not totally sure what this is doing, but I suspect checking for
>> Primary Key info?
>>
>> And why is this being run on each MapRequest ?
>>
>> Funnily, we have 2.6.3 running in a different setup, and it doesn't seem
>> to be issuing any of these queries - just the data query. I can't see any
>> config difference that would cause this.
>>
>
> Maybe not so funny, I remember of a major performance regression in JDBC
> stores in 2.6.0,
> indeed it was very slow against spatial databases, see the 2.6.1 release
> notes:
> http://blog.geoserver.org/2014/11/18/geoserver-2-6-1-released/
>
> Discussion here:
>
> http://osgeo-org.1560.x6.nabble.com/Severe-slowdown-in-ContentDataStore-in-12-x-and-trunk-td5167030.html
>
> To avoid this kind of issue, besides more review, which never hurts but
> requires more staff, we'd need
> continuous performance testing and comparison against production sized
> databases, something
> I've been proposing on and off for several years, but that never got the
> resourcing it needs to make it happen.
>
>
>
--
Monitor Your Dynamic Infrastructure at Any Scale With Datadog!
Get real-time metrics from all of your servers, apps and tools
in one place.
SourceForge users - Click here to start your Free Trial of Datadog now!
http://pubads.g.doubleclick.net/gampad/clk?id=241902991&iu=/4140___
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
On Wed, Sep 2, 2015 at 1:21 AM, Martin Davis wrote:
> We've run some DB logging on the map requests.
>
> One of the causes of the slowdown for no schema is this query:
>
> SELECT NULL AS table_cat,
>o.owner AS table_schem,
>o.object_name AS table_name,
>o.object_type AS table_type,
>NULL AS remarks
> FROM all_objects o
> WHERE o.owner LIKE :1 ESCAPE '/'
> AND o.object_name LIKE :2 ESCAPE '/'
> AND o.object_type IN ('xxx', 'TABLE', 'VIEW', 'SYNONYM')
> ORDER BY table_type, table_schem, table_name
>
> If no owner is specified this returns 38K rows, as opposed to about 240
> when owner is given.
>
> I'm not totally sure what this is doing, but I suspect checking for
> Primary Key info?
>
> And why is this being run on each MapRequest ?
>
> Funnily, we have 2.6.3 running in a different setup, and it doesn't seem
> to be issuing any of these queries - just the data query. I can't see any
> config difference that would cause this.
>
Maybe not so funny, I remember of a major performance regression in JDBC
stores in 2.6.0,
indeed it was very slow against spatial databases, see the 2.6.1 release
notes:
http://blog.geoserver.org/2014/11/18/geoserver-2-6-1-released/
Discussion here:
http://osgeo-org.1560.x6.nabble.com/Severe-slowdown-in-ContentDataStore-in-12-x-and-trunk-td5167030.html
To avoid this kind of issue, besides more review, which never hurts but
requires more staff, we'd need
continuous performance testing and comparison against production sized
databases, something
I've been proposing on and off for several years, but that never got the
resourcing it needs to make it happen.
Cheers
Andrea
--
==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.
==
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*
Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.
The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.
---
--
Monitor Your Dynamic Infrastructure at Any Scale With Datadog!
Get real-time metrics from all of your servers, apps and tools
in one place.
SourceForge users - Click here to start your Free Trial of Datadog now!
http://pubads.g.doubleclick.net/gampad/clk?id=241902991&iu=/4140___
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
On Tue, Sep 1, 2015 at 11:13 PM, Jody Garnett wrote: > I have a silly suggestion, when not using a schema is the data store > getting back an amazingly large number of oracle tables .. checking each > one for a spatial index and so on? > GeoServer allows to publish non spatial tables, which is used/useful to publish non spatial information via WFS, checking for spatial indexes would result in a regression. What we could have though, is a new datastore parameter that makes the store only return spatial tables Cheers Andrea -- == GeoServer Professional Services from the experts! Visit http://goo.gl/it488V for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions S.A.S. Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it http://twitter.com/geosolutions_it *AVVERTENZE AI SENSI DEL D.Lgs. 196/2003* Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003. The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc. --- -- Monitor Your Dynamic Infrastructure at Any Scale With Datadog! Get real-time metrics from all of your servers, apps and tools in one place. SourceForge users - Click here to start your Free Trial of Datadog now! http://pubads.g.doubleclick.net/gampad/clk?id=241902991&iu=/4140___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
We've run some DB logging on the map requests.
One of the causes of the slowdown for no schema is this query:
SELECT NULL AS table_cat,
o.owner AS table_schem,
o.object_name AS table_name,
o.object_type AS table_type,
NULL AS remarks
FROM all_objects o
WHERE o.owner LIKE :1 ESCAPE '/'
AND o.object_name LIKE :2 ESCAPE '/'
AND o.object_type IN ('xxx', 'TABLE', 'VIEW', 'SYNONYM')
ORDER BY table_type, table_schem, table_name
If no owner is specified this returns 38K rows, as opposed to about 240
when owner is given.
I'm not totally sure what this is doing, but I suspect checking for Primary
Key info?
And why is this being run on each MapRequest ?
Funnily, we have 2.6.3 running in a different setup, and it doesn't seem to
be issuing any of these queries - just the data query. I can't see any
config difference that would cause this.
Any ideas?
On Tue, Sep 1, 2015 at 12:46 PM, Martin Davis wrote:
> An update on this issue.
>
> As Andrea predicted, using a JNDI connection made no difference to the
> performance issue (no schema still substantially slower than using a
> schema).
>
> On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis wrote:
>
>> Thanks, Andrea.
>>
>> We're using GeoServer 2.6.0
>>
>> The performance issue occurs for map requests - so wouldn't this be
>> something different to the issue with slow metadata loading (The metadata
>> retrieval is an issue we've seen as well, but it only hurts the admin, not
>> the users, so we're less caring about that 8^).
>>
>> We'll probably try using a JNDI pool and see whether that helps at all.
>> If so, we may just use that approach. If not, we'll be looking for a code
>> fix - which we can likely get funded and contribute back.
>>
>>
>>
--
___
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
We're working on profiling from the DB side. It's difficult to get a debug GeoServer setup against the environment showing the problem. On Tue, Sep 1, 2015 at 2:47 PM, Jody Garnett wrote: > So are you in position to profile the code? We recently enabled the > database online tests again, but they test conformance, not performance. > > -- > Jody Garnett > > On 1 September 2015 at 14:38, Martin Davis wrote: > >> Yes, we are suspicious that the metadata queries are returning a lot of >> rows when no schema is specified. But can't confirm this is happening, >> until we can get DB-level tracing enabled. >> >> And as you say, why would this be happening on every GetMap ? And why >> happening in one environment and not in a similar different one? >> >> On Tue, Sep 1, 2015 at 2:13 PM, Jody Garnett >> wrote: >> >>> I have a silly suggestion, when not using a schema is the data store >>> getting back an amazingly large number of oracle tables .. checking each >>> one for a spatial index and so on? >>> >>> I would expect that to take a bit longer on startup ... but you are >>> indicating that every GetMap request is consistently slow. >>> >>> -- >>> Jody Garnett >>> >>> On 1 September 2015 at 12:46, Martin Davis wrote: >>> An update on this issue. As Andrea predicted, using a JNDI connection made no difference to the performance issue (no schema still substantially slower than using a schema). We're now attempting to do Oracle logging to try and see what's getting run that might slow a map request down. (We have only limited access to the box where the problem shows up). Results are not conclusive so far, but we think we are seeing several queries being run over as many as 5 different "Geoserver sessions" during a single map request. Cannot tell what these are yet, but seems likely they are metadata queries. This is odd, since we are not seeing this happen in another similar environment. One difference is that we are connecting via an Oracle Service rather than a SID in the slow environment. Would be odd if this was the cause, though. On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis wrote: > Thanks, Andrea. > > We're using GeoServer 2.6.0 > > The performance issue occurs for map requests - so wouldn't this be > something different to the issue with slow metadata loading (The metadata > retrieval is an issue we've seen as well, but it only hurts the admin, not > the users, so we're less caring about that 8^). > > We'll probably try using a JNDI pool and see whether that helps at > all. If so, we may just use that approach. If not, we'll be looking for > a > code fix - which we can likely get funded and contribute back. > > > -- ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users >>> >> > -- ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
So are you in position to profile the code? We recently enabled the database online tests again, but they test conformance, not performance. -- Jody Garnett On 1 September 2015 at 14:38, Martin Davis wrote: > Yes, we are suspicious that the metadata queries are returning a lot of > rows when no schema is specified. But can't confirm this is happening, > until we can get DB-level tracing enabled. > > And as you say, why would this be happening on every GetMap ? And why > happening in one environment and not in a similar different one? > > On Tue, Sep 1, 2015 at 2:13 PM, Jody Garnett > wrote: > >> I have a silly suggestion, when not using a schema is the data store >> getting back an amazingly large number of oracle tables .. checking each >> one for a spatial index and so on? >> >> I would expect that to take a bit longer on startup ... but you are >> indicating that every GetMap request is consistently slow. >> >> -- >> Jody Garnett >> >> On 1 September 2015 at 12:46, Martin Davis wrote: >> >>> An update on this issue. >>> >>> As Andrea predicted, using a JNDI connection made no difference to the >>> performance issue (no schema still substantially slower than using a >>> schema). >>> >>> We're now attempting to do Oracle logging to try and see what's getting >>> run that might slow a map request down. (We have only limited access to >>> the box where the problem shows up). Results are not conclusive so far, >>> but we think we are seeing several queries being run over as many as 5 >>> different "Geoserver sessions" during a single map request. Cannot tell >>> what these are yet, but seems likely they are metadata queries. This is >>> odd, since we are not seeing this happen in another similar environment. >>> One difference is that we are connecting via an Oracle Service rather than >>> a SID in the slow environment. Would be odd if this was the cause, though. >>> >>> >>> On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis >>> wrote: >>> Thanks, Andrea. We're using GeoServer 2.6.0 The performance issue occurs for map requests - so wouldn't this be something different to the issue with slow metadata loading (The metadata retrieval is an issue we've seen as well, but it only hurts the admin, not the users, so we're less caring about that 8^). We'll probably try using a JNDI pool and see whether that helps at all. If so, we may just use that approach. If not, we'll be looking for a code fix - which we can likely get funded and contribute back. >>> >>> -- >>> >>> ___ >>> Geoserver-users mailing list >>> Geoserver-users@lists.sourceforge.net >>> https://lists.sourceforge.net/lists/listinfo/geoserver-users >>> >>> >> > -- ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
Yes, we are suspicious that the metadata queries are returning a lot of rows when no schema is specified. But can't confirm this is happening, until we can get DB-level tracing enabled. And as you say, why would this be happening on every GetMap ? And why happening in one environment and not in a similar different one? On Tue, Sep 1, 2015 at 2:13 PM, Jody Garnett wrote: > I have a silly suggestion, when not using a schema is the data store > getting back an amazingly large number of oracle tables .. checking each > one for a spatial index and so on? > > I would expect that to take a bit longer on startup ... but you are > indicating that every GetMap request is consistently slow. > > -- > Jody Garnett > > On 1 September 2015 at 12:46, Martin Davis wrote: > >> An update on this issue. >> >> As Andrea predicted, using a JNDI connection made no difference to the >> performance issue (no schema still substantially slower than using a >> schema). >> >> We're now attempting to do Oracle logging to try and see what's getting >> run that might slow a map request down. (We have only limited access to >> the box where the problem shows up). Results are not conclusive so far, >> but we think we are seeing several queries being run over as many as 5 >> different "Geoserver sessions" during a single map request. Cannot tell >> what these are yet, but seems likely they are metadata queries. This is >> odd, since we are not seeing this happen in another similar environment. >> One difference is that we are connecting via an Oracle Service rather than >> a SID in the slow environment. Would be odd if this was the cause, though. >> >> >> On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis >> wrote: >> >>> Thanks, Andrea. >>> >>> We're using GeoServer 2.6.0 >>> >>> The performance issue occurs for map requests - so wouldn't this be >>> something different to the issue with slow metadata loading (The metadata >>> retrieval is an issue we've seen as well, but it only hurts the admin, not >>> the users, so we're less caring about that 8^). >>> >>> We'll probably try using a JNDI pool and see whether that helps at all. >>> If so, we may just use that approach. If not, we'll be looking for a code >>> fix - which we can likely get funded and contribute back. >>> >>> >>> >> >> -- >> >> ___ >> Geoserver-users mailing list >> Geoserver-users@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/geoserver-users >> >> > -- ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
I have a silly suggestion, when not using a schema is the data store getting back an amazingly large number of oracle tables .. checking each one for a spatial index and so on? I would expect that to take a bit longer on startup ... but you are indicating that every GetMap request is consistently slow. -- Jody Garnett On 1 September 2015 at 12:46, Martin Davis wrote: > An update on this issue. > > As Andrea predicted, using a JNDI connection made no difference to the > performance issue (no schema still substantially slower than using a > schema). > > We're now attempting to do Oracle logging to try and see what's getting > run that might slow a map request down. (We have only limited access to > the box where the problem shows up). Results are not conclusive so far, > but we think we are seeing several queries being run over as many as 5 > different "Geoserver sessions" during a single map request. Cannot tell > what these are yet, but seems likely they are metadata queries. This is > odd, since we are not seeing this happen in another similar environment. > One difference is that we are connecting via an Oracle Service rather than > a SID in the slow environment. Would be odd if this was the cause, though. > > > On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis wrote: > >> Thanks, Andrea. >> >> We're using GeoServer 2.6.0 >> >> The performance issue occurs for map requests - so wouldn't this be >> something different to the issue with slow metadata loading (The metadata >> retrieval is an issue we've seen as well, but it only hurts the admin, not >> the users, so we're less caring about that 8^). >> >> We'll probably try using a JNDI pool and see whether that helps at all. >> If so, we may just use that approach. If not, we'll be looking for a code >> fix - which we can likely get funded and contribute back. >> >> >> > > -- > > ___ > Geoserver-users mailing list > Geoserver-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geoserver-users > > -- ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
An update on this issue. As Andrea predicted, using a JNDI connection made no difference to the performance issue (no schema still substantially slower than using a schema). We're now attempting to do Oracle logging to try and see what's getting run that might slow a map request down. (We have only limited access to the box where the problem shows up). Results are not conclusive so far, but we think we are seeing several queries being run over as many as 5 different "Geoserver sessions" during a single map request. Cannot tell what these are yet, but seems likely they are metadata queries. This is odd, since we are not seeing this happen in another similar environment. One difference is that we are connecting via an Oracle Service rather than a SID in the slow environment. Would be odd if this was the cause, though. On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis wrote: > Thanks, Andrea. > > We're using GeoServer 2.6.0 > > The performance issue occurs for map requests - so wouldn't this be > something different to the issue with slow metadata loading (The metadata > retrieval is an issue we've seen as well, but it only hurts the admin, not > the users, so we're less caring about that 8^). > > We'll probably try using a JNDI pool and see whether that helps at all. > If so, we may just use that approach. If not, we'll be looking for a code > fix - which we can likely get funded and contribute back. > > > -- ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
Thanks, Andrea. We're using GeoServer 2.6.0 The performance issue occurs for map requests - so wouldn't this be something different to the issue with slow metadata loading (The metadata retrieval is an issue we've seen as well, but it only hurts the admin, not the users, so we're less caring about that 8^). But I guess anything might be happening in there. We're attempting to get tracing on the DB side to see what's going on, but so far haven't found a friendly-enough DBA. 8^) We'll probably try using a JNDI pool and see whether that helps at all. If so, we may just use that approach. If not, we'll be looking for a code fix - which we can likely get funded and contribute back. Will communicate any findings we have, to help improve the support for Oracle. (Postgres does tend to scare people at this particular client, unfortunately. We did float the idea of using a "local data cache" built on top of a special technology ideally suited for this purpose whose initials just happen to be PG... but so far no bites. However, if we can show a major improvement in map image request performance, we might still be able to make this fly...) On Thu, Jul 30, 2015 at 1:42 PM, Andrea Aime wrote: > On Thu, Jul 30, 2015 at 7:56 PM, Martin Davis wrote: > >> We have the following GeoServer setup: >> >> Datastore to an Oracle 12c Exadata instance >> Internal connection pooling >> No schema specified >> > > What GEoServer version? > > >> >> We noticed a serious performance anomaly, where each layer was taking >> about 4 s to render, even when the data was very small. >> >> When we switched to specifying an explicit schema in the Datastore >> config, the performance got significantly faster, and the time to render >> individual layers became more proportional to the query result size. >> >> So the questions are: >> 1. does omitting a schema name in the Oracle Datastore config cause >> connection pooling to be disabled or defeated? >> > > While I cannot ensure I won't be hit by an asteroid in the next hour, > that's unlikely. So is the idea that not setting > up the catalog can break connection pooling. > > It will likely slow down things for other reasons unrelated to connection > pooling instead. > Oracle is a database from hell, if you don't setup the catalog the jdbc > driver returns a huge number of tables (50k on some installations?) when > you get the > database metadata. > We had issues with caching metadata at the content data store level, with > the Oracle dialect not using prepared statements > for metadata (pull request still open, I'm unable to find the time to > review it, see here: > https://github.com/geotools/geotools/pull/905), and likely something else > that I don't remember. > > I would suggest to try trunk, with the above pull request applied, and see > if it gets any better. > > >> 2. Will this issue be avoided when using a JNDI connection pool? >> > > Not directly, but with JNDI you can setup a single connection pool and > then setup N datastores, one for each > of the catalogs you need to access. > > Investigations and patches welcomed too, we have a large disconnect > between people using and complaining > about Oracle and people actually doing something about it. The common > wisdom is to just drop > Oracle in favor of PostGIS, when one can (yes, I'm well aware that's often > not an option). > > > -- ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
Re: [Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
On Thu, Jul 30, 2015 at 7:56 PM, Martin Davis wrote: > We have the following GeoServer setup: > > Datastore to an Oracle 12c Exadata instance > Internal connection pooling > No schema specified > What GEoServer version? > > We noticed a serious performance anomaly, where each layer was taking > about 4 s to render, even when the data was very small. > > When we switched to specifying an explicit schema in the Datastore config, > the performance got significantly faster, and the time to render individual > layers became more proportional to the query result size. > > So the questions are: > 1. does omitting a schema name in the Oracle Datastore config cause > connection pooling to be disabled or defeated? > While I cannot ensure I won't be hit by an asteroid in the next hour, that's unlikely. So is the idea that not setting up the catalog can break connection pooling. It will likely slow down things for other reasons unrelated to connection pooling instead. Oracle is a database from hell, if you don't setup the catalog the jdbc driver returns a huge number of tables (50k on some installations?) when you get the database metadata. We had issues with caching metadata at the content data store level, with the Oracle dialect not using prepared statements for metadata (pull request still open, I'm unable to find the time to review it, see here: https://github.com/geotools/geotools/pull/905), and likely something else that I don't remember. I would suggest to try trunk, with the above pull request applied, and see if it gets any better. > 2. Will this issue be avoided when using a JNDI connection pool? > Not directly, but with JNDI you can setup a single connection pool and then setup N datastores, one for each of the catalogs you need to access. Investigations and patches welcomed too, we have a large disconnect between people using and complaining about Oracle and people actually doing something about it. The common wisdom is to just drop Oracle in favor of PostGIS, when one can (yes, I'm well aware that's often not an option). Cheers Andrea -- == GeoServer Professional Services from the experts! Visit http://goo.gl/it488V for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions S.A.S. Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it http://twitter.com/geosolutions_it *AVVERTENZE AI SENSI DEL D.Lgs. 196/2003* Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003. The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc. --- -- ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
[Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?
We have the following GeoServer setup: Datastore to an Oracle 12c Exadata instance Internal connection pooling No schema specified We noticed a serious performance anomaly, where each layer was taking about 4 s to render, even when the data was very small. When we switched to specifying an explicit schema in the Datastore config, the performance got significantly faster, and the time to render individual layers became more proportional to the query result size. So the questions are: 1. does omitting a schema name in the Oracle Datastore config cause connection pooling to be disabled or defeated? 2. Will this issue be avoided when using a JNDI connection pool? If the answer to # 1 is yes, this should probably be highlighted in the docs... -- ___ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
