tune mapserver on oraclespatial
Sebastian Schmitz
sschmitz at KARTAGO.DE
Mon Dec 18 08:26:22 PST 2006
Hello list,
we are still trying to tune our Mapserver/Oracle Spatial Database
performance. Some facts about our data are:
=> DATA 210 000 Rows (WGS84, SRID 8307)
- In 137 000 The Geom-column is filled (2D, only Point Geometry)
We created the following Insert-Metadata-statement for the spatial
index/data:
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('GDI_KD_STAMM', 'GEOM',
MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('X', -31.250000000,
46.180000000, 0.000000050),
MDSYS.SDO_DIM_ELEMENT('Y', 27.640000000,
78.910000000, 0.000000050)
),
8307
);
Our statement for creating the spatial index (R-type index):
CREATE INDEX "GDI"."IDX_GDI_KD_STAMM" ON "GDI"."GDI_KD_STAMM" ("GEOM")
INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" PARAMETERS ('TABLESPACE=GDII
SDO_INDX_DIMS=2 LAYER_GTYPE=POINT SDO_NON_LEAF_TBL=TRUE');
Our used Mapserver version (Windows):
MapServer version 4.8.3 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP
OUTPUT=PDF
OUTPUT=SWF OUTPUT=SVG SUPPORTS=PROJ SUPPORTS=FREETYPE
SUPPORTS=WMS_SERVER SUPPO
RTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT
SUPPORTS=WCS_SERVER SUPPO
RTS=THREADS SUPPORTS=GEOS INPUT=JPEG INPUT=POSTGIS INPUT=ORACLESPATIAL
INPUT=OGR
INPUT=GDAL INPUT=SHAPEFILE DEBUG=MSDEBUG
Extract from our used mapfile with one example layer (whole mapfile
contains 8 layers):
#######################################################
# KARTA.GO mapfile GDI_____ #
#######################################################
NAME "Kunden"
STATUS ON
SIZE 640 490
EXTENT 1689025 3960945 7079493 8188877 UNITS meters
PROJECTION
"init=epsg:31467"
END
IMAGECOLOR 255 255 255
SYMBOLSET "../icons/symbset"
FONTSET "../fonts/font.list"
SHAPEPATH "../data"
######################################################
# Start web interface definition #
######################################################
WEB
METADATA
WMS_ONLINERESOURCE
"http://******/cgi-bin/mapserv.exe?map=C:/Programme/Apache_Group/Apache2/htdocs/gdi/mapfiles/kunden.map"
WMS_SRS "epsg:31467"
WMS_TITLE "Kunden"
WMS_FEATURE_INFO_MIME_TYPE "gml"
WMS_ABSTRACT "GDI Kunden"
END
END
#######################################################
# Layer Example: #
#######################################################
### aktive Kunden ##################################
LAYER
NAME "Aktiv"
STATUS ON
TYPE Point
TEMPLATE "../attributformulare/sst.html"
TOLERANCE 6
TOLERANCEUNITS Pixels
DUMP TRUE
CONNECTIONTYPE oraclespatial
CONNECTION "***/***@*****"
DATA "GEOM FROM GDI_KD_STAMM USING UNIQUE KD_NR SRID 8307"
FILTER "KD_AUSG_SEIT IS NULL"
PROCESSING "CLOSE_CONNECTION=DEFER"
PROJECTION
"init=epsg:4326"
END
SYMBOLSCALE 6000000
METADATA
"WMS_SRS" "epsg:4326"
"WMS_TITLE" "Aktiv"
"WMS_FEATURE_INFO_MIME_TYPE" "gml"
"WMS_EXTENT" "1689025 3960945 7079493 8188877"
"GML_INCLUDE_ITEMS" "KD_NR"
END
CLASSITEM "KD_NR"
LABELITEM "KD_NR"
CLASS
EXPRESSION /./
MAXSCALE 10000000
MINSCALE 150001
MINSIZE 3
MAXSIZE 8
SYMBOL "circle"
OUTLINECOLOR 21 86 255
COLOR 21 86 255
END
CLASS
EXPRESSION /./
MAXSCALE 150000
MINSCALE 0
MAXSIZE 25
MINSIZE 25
SYMBOL "circle"
OUTLINECOLOR 21 86 255 COLOR 255 255 254
LABEL
FORCE TRUE
POSITION cc
ANGLE 0
TYPE TRUETYPE
FONT verdana
MINSIZE 6
MAXSIZE 6
COLOR 0 0 0
OUTLINECOLOR 255 255 254
END
END
END
#################
# Some other Layers
#################
END
We tried a lot of things (partitioning with spatial index, OGR
connection, tuning indexes) to get to a better performance, but nothing
really worked. For testing we removed the filter in layer data and
fetched all rows from the table. There was no difference in performance
time to the case with filter in use. We would be greatful for any new
idea to improve the performance.
Fernando Simon schrieb:
> 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
>>> ________________________________________________________
>>>
>>>
>>>
>>
>>
--
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
________________________________________________________
More information about the MapServer-users
mailing list