[Geoserver-users] Problems with large amount of features from Oracle-DB

GeoServer GeoServer User
1

Hi,

I want to use a GeoServer to display a large collection of spatial data,
stored in an oracle database (~4 million features spreaded on a radius of
150 miles). I added the database as a data source (oracle plugin) and added
a layer to publish the data. The layer preview with openlayers usually fails
after 60 seconds, because thats the preconfigured maximum rendering time (i
also tried to set the time to 180 seconds, also without success).

So i tried to add a cached layer and to fill the cache through the
generate-function. Some of the generated pictures in the cache show the
correct data, but most of the pictures in the cache are just black, because
the rendering fails again after 60 seconds. If i reduce the features to a
few hundred feaures everything works fine. If I export the features to a big
shapefile and publish the shapefile everything is ok, but the features can
change everyday and i can't export all the features everyday.

I think i'm doing sth. completely wrong, a lot of wms-servers have these
amounts of features and don't face any problems. I wonder why publishing a
big shapefile is really fast and publishing the same data from an oracle
server (in the same intranet) is such a big deal for me. Can i reduce the
features to render on a lower zoom level? Why is this a problem if loaded
from a database and no problem if loaded from a shapefile?

Thanks for your effort,

Frank

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Problems-with-large-amount-of-features-from-Oracle-DB-tp5139088.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

2

On Wed, May 7, 2014 at 9:47 PM, frankzander <frankzan78@anonymised.com> wrote:

Hi,

I want to use a GeoServer to display a large collection of spatial data,
stored in an oracle database (~4 million features spreaded on a radius of
150 miles). I added the database as a data source (oracle plugin) and added
a layer to publish the data. The layer preview with openlayers usually
fails
after 60 seconds, because thats the preconfigured maximum rendering time (i
also tried to set the time to 180 seconds, also without success).

So i tried to add a cached layer and to fill the cache through the
generate-function. Some of the generated pictures in the cache show the
correct data, but most of the pictures in the cache are just black, because
the rendering fails again after 60 seconds. If i reduce the features to a
few hundred feaures everything works fine. If I export the features to a
big
shapefile and publish the shapefile everything is ok, but the features can
change everyday and i can't export all the features everyday.

I think i'm doing sth. completely wrong, a lot of wms-servers have these
amounts of features and don't face any problems. I wonder why publishing a
big shapefile is really fast and publishing the same data from an oracle
server (in the same intranet) is such a big deal for me. Can i reduce the
features to render on a lower zoom level? Why is this a problem if loaded
from a database and no problem if loaded from a shapefile?

Wondering if you have all the indexes you need.
You can have a look at the queries (prepared statements) we are doing by
enabling
the "geotools developer logging" logging level in the server config panel

Anyways, Oracle itself has a part in this.
If you try the same with PostGIS you'll get much better results, it's more
efficient in itself, but also smarter, it can decide dynamically whether to
do
a sequential scan (if the bbox you're asking for contains most of the data)
or to use the spatial index, something that Oracle cannot do instead
(not against a simple query, it can do that if you use stored procedures
though).

There is a slim chance the way we interact with Oracle can be improved, too,
but that would require some analysis of what's going on in your case and
a programmer to alter the way GeoServer interacts with your Oracle instance
(if you don't have java development knowledge in house, you can look
at the commercial support page).

We have many users hitting Oracle, and a general slowness compared to
other data sources is often reported, but so far we failed to see obvious
issues in
the way we do queries so far and have been blaming the database instead.

Cheers
Andrea

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
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

-------------------------------------------------------

3

Hi Franz,
We have multi-million row Oracle layers too. They’re quite slow, but I’m with Andrea in the “Blaming the [Oracle] database” - all of my indicates point to it being Oracle being slow for our stuff.

There are several things we do:

  • Ensure the geometry is indexed.
  • Ensure any columns you’re using for SLD filters are indexed.
  • Use a Max scale denominator (or lower the one you’re using).
  • We try not to have more than 40-50,000 features loading in any request.
  • Use TMS/WMTS for the layer to reduce Oracle calls.
  • Pre-render TMS/WMTS tiles.

Between them they should provide adequate responses.
Cheers,
Jonathan

This transmission is intended for the named addressee(s) only and may contain confidential, sensitive or personal information and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All email traffic sent to or from us, including without limitation all GCSX traffic, may be subject to recording and/or monitoring in accordance with relevant legislation.

···

On 9 May 2014 11:03, Andrea Aime <andrea.aime@anonymised.com> wrote:

Is your legacy SCM system holding you back? Join Perforce May 7 to find out:
� 3 signs your SCM is hindering your productivity
� Requirements for releasing software faster
� Expert tips and advice for migrating your SCM now
http://p.sf.net/sfu/perforce

Geoserver-users mailing list
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

On Wed, May 7, 2014 at 9:47 PM, frankzander <frankzan78@anonymised.com157…> wrote:

Hi,

I want to use a GeoServer to display a large collection of spatial data,
stored in an oracle database (~4 million features spreaded on a radius of
150 miles). I added the database as a data source (oracle plugin) and added
a layer to publish the data. The layer preview with openlayers usually fails
after 60 seconds, because thats the preconfigured maximum rendering time (i
also tried to set the time to 180 seconds, also without success).

So i tried to add a cached layer and to fill the cache through the
generate-function. Some of the generated pictures in the cache show the
correct data, but most of the pictures in the cache are just black, because
the rendering fails again after 60 seconds. If i reduce the features to a
few hundred feaures everything works fine. If I export the features to a big
shapefile and publish the shapefile everything is ok, but the features can
change everyday and i can’t export all the features everyday.

I think i’m doing sth. completely wrong, a lot of wms-servers have these
amounts of features and don’t face any problems. I wonder why publishing a
big shapefile is really fast and publishing the same data from an oracle
server (in the same intranet) is such a big deal for me. Can i reduce the
features to render on a lower zoom level? Why is this a problem if loaded
from a database and no problem if loaded from a shapefile?

Wondering if you have all the indexes you need.
You can have a look at the queries (prepared statements) we are doing by enabling
the “geotools developer logging” logging level in the server config panel

Anyways, Oracle itself has a part in this.
If you try the same with PostGIS you’ll get much better results, it’s more
efficient in itself, but also smarter, it can decide dynamically whether to do
a sequential scan (if the bbox you’re asking for contains most of the data)
or to use the spatial index, something that Oracle cannot do instead
(not against a simple query, it can do that if you use stored procedures though).

There is a slim chance the way we interact with Oracle can be improved, too,
but that would require some analysis of what’s going on in your case and
a programmer to alter the way GeoServer interacts with your Oracle instance
(if you don’t have java development knowledge in house, you can look
at the commercial support page).

We have many users hitting Oracle, and a general slowness compared to
other data sources is often reported, but so far we failed to see obvious issues in
the way we do queries so far and have been blaming the database instead.

Cheers
Andrea

–

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
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