tune mapserver on oraclespatial

Mon Dec 11 06:52:12 EST 2006

Hi Sebastian,
    The Oracle Spatial driver try to be very fast to request data from
database. Its use the OCI to connect directly in the Oracle core API,
without use any middle ware/interface.
    To execute fast requests it try to use the database operators, like
SDO_FILTER and SDO_RELATE. Another important thing is that the oracle
Spatial driver apply your actual extent to restrict the data from
database, to just retrieve the data that are inside of the extent (this
just occur to draw the map, not for query).
     The driver can just uses data from SDO_GEOMETRY object, another
kind of the object cannot uses the Maporaclespatial but the OGR. A hint
with the database connect is use the connection pool, to do this you
just need to add the "PROCESSING CONNECTION=DEFER" in you layer (all
with the connection). This little change will force the Mapserver to
store the connection handler until the end of your request, this help
when the login cost is high.
     As Bart and Michael wrote, exist two way how to see the SQL. The
first is using the DEBUG flags, with this way you can see all the SQL's
that  driver execute. The second is  just change the connection to see
the SQL that retrive the data from database. An important thing is just
use the debug (first way) in a development state, because will increase
a lot the time to process and finish the request.
     In the database side you can to improve the performance with little
things. When you create the index you need to use an extent (MBR) that
represent all you data, but just it. If you define a generic MBR (like
-180, -90 180 90) will be very bad because not help the optimizer to
find your data. In the 9.2 manual (item 1.7) have a good explanation
about the spatial index methods. The Mapserver driver is very dependent
of a good index to be fast, if your table have a poor index will be
worst than without the index.
     If you are using geodetic data the Oracle Spatial operations will
be more slow, and if you have complex data will be worst. This occurs
because the Oracle Spatial need to consider the world surface to
represent the data, need to execute more mathematical calculus to
delivery your data.
     Another thing to improve is use some Oracle ways. The Oracle Server
can store a clone of you table in memory to help and execute the
requests more fast, this help when your table not change a lot and is
very used. ( I don't remember if it's possible to use this in the 9i
version)
     Sorry the delay to reply your message, I was out last week. If you
have more doubts you can contact me and the list. We will try to help
you with the best hints.
     Best regards.

------------------------------------------------------------------------
Fernando Simon
Mapserver and Oracle Spatial developer
G10 - Laboratorio de Computacao Aplicada - Brazil
http://www.univali.br/g10 - UNIVALI/CTTMAR
------------------------------------------------------------------------

Rahkonen Jukka wrote:
> Hi Sebastian,
>
> Do you have a feeling that your Oracle is especially slow at the moment? How many features your typical query is giving back?
> I am using a polygon layer with 1.2 million polygons, all in one partition and I think that MapServer works quite well with it with pure spatial queries. Of course it gets slow if the query returns many thousand features, but that is another thing that must be handled by using more genaralised layers for far-zoom-out cases.  What really can make Oracle slow are attribute joins without proper indices.
> MapServer is somewhat slower than GeoServer with the same data and I believe that is mostly because GeoServer utilises connection pooling better.
>
> I found results from a quick test I made once by having both MapServer and Geoserver running on the same computer and sending request to Oracle layer with different routes.
>
> Test cases:
> 1) Polygons from Oracle 9i spatial table through MapServer WMS and native Oracle connection
> 2) Same polygons through MapServer, but through by accessing them through GeoServer WFS service (running on the same computer)
> 3) Same polygons again through MapServer, but now cascading through GeoServer WMS service (running on the same computer)
> 4) Same polygons but this time directly through the GeoServer WMS
>
> I got this kind of results presented as throughput requests/minute
> 1) 200 requests / minute (60 % processor load)
> 2) 112  requests / minute (100%  processor load)
> 3) 87 requests / minute (75% processor load)
> 4) 300 requests / minute (85% processor load)
>
> Regards,
>
> -Jukka Rahkonen-
>  
>
>   
>> -----Alkuperäinen viesti-----
>> Lähettäjä: UMN MapServer Users List 
>> [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] Puolesta Sebastian Schmitz
>> Lähetetty: 11. joulukuuta 2006 11:32
>> Vastaanottaja: MAPSERVER-USERS at LISTS.UMN.EDU
>> Aihe: Re: [UMN_MAPSERVER-USERS] tune mapserver on oraclespatial
>>
>> Hi
>> thanks for help with step 1 of my question - getting the SQL 
>> Mapserver uses. My Mapserver (4.8.3) does not print anything 
>> but failed to draw layer onto the screen, but setting debug 
>> flag and setting an MS_ERRORFILE env-variable worked out fine.
>> So, now I have the SQL I may as well post it here and ask for 
>> comments on tuning options for a large table (300.000 rows):
>>
>> SELECT KUNNR, GEOM FROM     (< subselect >)  WHERE SDO_FILTER( GEOM, 
>>         MDSYS.SDO_GEOMETRY(2003, 8307,
>> NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_AR
>> RAY(4.04520417,48.6727777,9.82231387,51.398884)
>> ),
>>         'querytype=window') = 'TRUE'
>>
>> Our current idea is to partition the table by x and y values 
>> from SDO_GEOMETRY and put spatial indices onto each of these 
>> partitions. 
>> Status quo is that we only have one spatial index for the 
>> whole table..
>>
>> Feedback appreciated
>>
>> Cheers
>>
>> Sebastian
>>
>>
>>
>>
>> Michael Smith schrieb:
>>     
>>> One of the easiest ways is to just introduce a small syntax 
>>>       
>> error in 
>>     
>>> your data statement (change the tablename or something). Then 
>>> Mapserver will print the spatial sql statement to the screen (using 
>>> the cgi version)
>>>
>>> Mike Smith
>>>
>>>   
>>>       
>> --
>> KARTA.GO Gesellschaft für raumbezogenes Informationsmanagement mbH
>>
>> Siemensstraße 8
>> 53121 Bonn
>>
>> Sebastian Schmitz
>> Projektleiter
>>
>> fon: +49 (0) 228 / 9 45 99 91
>> fax: +49 (0) 228 / 9 45 99 93
>> sschmitz at kartago.de
>> http://www.kartago.de
>> ________________________________________________________
>>
>>     
>
>