tune mapserver on oraclespatial

[Rahkonen Jukka]

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
> ________________________________________________________
>