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 "***/[EMAIL PROTECTED]"
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:[EMAIL PROTECTED] Puolesta Sebastian Schmitz
Lähetetty: 11. joulukuuta 2006 11:32
Vastaanottaja: [email protected]
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
[EMAIL PROTECTED]
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
[EMAIL PROTECTED]
http://www.kartago.de
________________________________________________________
