tune mapserver on oraclespatial
Fernando Simon
fsimon at UNIVALI.BR
Tue Dec 19 05:43:07 EST 2006
Hi Sebastian,
The slow time and cpu cost can be relate with the SRID that you
defined for you table.
The SRID that you used is for geodetic data, so the cost to Oracle
server read is high because needs to use a lot mathematical functions to
consider the world surface. The Oracle was running in the same machine
that Mapserver (and geoserver)? If yes, you will need to put the
services in another machine to find where is the high cost.
About the SRID, if you need to use geodetic data you have to
increase the CPU velocity. If the geodetic is not a requisit you can
convert to another.In the chapter #5 of the Oracle Spatial Guide (to
version 9i) you will find a good explanation about the two systems.
Best regards.
Fernando Simon
Sebastian Schmitz wrote:
> 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
>>>> ________________________________________________________
>>>>
>>>>
>>>
>
More information about the mapserver-users
mailing list