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
On 9 May 2014 11:03, Andrea Aime <[email protected]> wrote:
> On Wed, May 7, 2014 at 9:47 PM, frankzander <[email protected]> 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
>
> -------------------------------------------------------
>
>
> ------------------------------------------------------------------------------
> 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
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>
--
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.
------------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
