Oracle Spatial doesn't scale well with concurrent connections

[ivanopicco]

Hi all, I perform some performance tests on mapserv (CGI and FASTCGI), using Oracle and SDE connections to retrieve a simple feature layer from the same oracle (SDO) table (the layer was registered on SDE). I use Microsoft Web Strees Tool (WAST) to make the concurrent getmap requests. As the results show (see attach), Oracle doesn't scale well with concurrent connections. Mapserv can serve 12 concurrent SDE connections, but only 3 from Oracle.

The web server has 2 Intel Xeon 3.40GHz and 2GB of RAM and Linux CentOS 4.5. The database server is a Sun-Fire-V210 with Solaris 9, it has 2 sparcv9 processor operates at 1002 MHz and 2GB of RAM.

This is an extract from my mapfile:

LAYER
    NAME "CTR-SDE"
    STATUS ON
    DEBUG ON
    DATA "ctr_sde.comuni,SHAPE"
    TYPE POLYGON
    CONNECTIONTYPE SDE
    CONNECTION "bubble,port:5153,esri_sde,ctr_sde,ctr_sde"
    PROCESSING "CLOSE_CONNECTION=DEFER"
    TEMPLATE "template.html"
    METADATA
      "WMS_SRS"    "EPSG:32632"
      "WMS_NAME"    "Comuni_SDE"
      "WMS_TITLE"    "Comuni_SDE"
      "WMS_ABSTRACT"    "Comuni SDE"
    END
    CLASS
      STYLE
        OUTLINECOLOR 0 0 0
        ANTIALIAS TRUE
      END
    END
  END

  LAYER
    NAME "CTR-SDO"
    STATUS ON
    DEBUG ON
    DATA "GEOMETRY FROM comuni USING UNIQUE OBJECTID SRID 82344 FILTER
VERSION 10G"
    TYPE POLYGON
    CONNECTIONTYPE oraclespatial
    CONNECTION 'ctr_sdo/ctr_sdo@bubble:1522/OLYMPIA'
    PROCESSING "CLOSE_CONNECTION=DEFER"
    TEMPLATE "template.html"
    METADATA
      "WMS_SRS"    "EPSG:32632"
      "WMS_NAME"    "Comuni_SDO"
      "WMS_TITLE"    "Comuni_SDO"
      "WMS_ABSTRACT"    "Comuni SDO"
    END
    CLASS
      STYLE
        OUTLINECOLOR 0 0 0
        ANTIALIAS TRUE
      END
    END
   END

In attach you find an extract from debug log regarding a stress test with 16 concurrent connections.

[bene]

I'm no mapserver-developer. I just stumbled about this one and I'm a little bit concerned/irritated about this strange result.

Were you able to get further insight on this issue? I'd like to learn ...

Did you try DATA "... SRID 82344 RELATE VERSION 10G" instead of "FILTER"?

ArcSDE uses internally the first level filter of Oracle and implements an own second-level (exact) filter. So from Mapservers point of view, ArcSDE acts like Oracle-SDO-RELATE.

[ivanopicco]

Hi, I tried with RELATE, with this my stress-test reported an increasing processing time, as i expected. Recently, I made new tests using more different layers than previous (with a single layer) and the ultimate mapserv (5.0.0). My results doesn't report any performance issue (but ArcSDE is still faster), I think there was problems related to memory management, or something else, I don't know.

From my point of view the defect was fixed inside other bugfix, but i can't figure out why Oracle is slower than ArcSDE, since using the same table and a simpler filter (1 level Oracle vs 2 level ArcSDE as bene notes).

[bene]

Hallo, I wrote a testprogram in Java. This program creates Maps with Oracle/Relate, Oracle/Filter and SDE. In every case: same database, same table, same extent, connection-reuse.

Database-Server/SDE runs on a slow (Virtual-)Machine. Mapserver/Testprogram runs on my Personal-Computer (Standard-PC) Datas are lines (streams).

My experience differs from yours. Create 10 Maps with each layer: SDORELATE 17 sec SDOFILTER 17 sec SDE 30 sec

Results may differ between runs. But in principle they remain same.

By Benedikt Rothe

[bene]

Again because of the wiki:

• SDORELATE: 17 sec

• SDOFILTER: 17 sec

• SDE: 30 sec

[ivanopicco]

Hi bene, does "same database and same table" mean a registered Oracle Simple Feature table in SDE geodatabase?

Query data from a Oracle simple feature table registered in a geodb both using ArcSDE or Oracle drivers should have same performance since it should use the same index. Isn't it?

[bene]

Hi

does "same database" mean ...

I created the table with Oracle-SQL-Statements. Than I used "sdelayer -o register ...". I think this doesn't register in the GDB-Tables but only in the SDE3-style-tables.

drivers should have same performance ...

ArcSDE is an additional peace of software between. It's just more software doing any work before the line can be rendered.

Something additional: As far as I understood the Mapserver-ArcSDE-Driver reads *all* attributes of the table - although my lines are just always rendered 'blue'. The Oracle-driver reads only the required attributes: Shape and ObjectId. This could be important.

Actually I don't believe, that it's all a question about the spatial-index.

I have no idea why the Native-Oracle-performance is bad for you.

[fsimon]

Hi folks, Sorry for the delay to replay this issue.

The first thing is about the FILTER and RELATE, the first (FILTER) make Mapserver to use the SDO_FILTER operator. From Oracle Spatial manual this operator only apply the primary filter operation, the both (primary and second) is used with SDO_RELATE operation. This is the reason because the RELATE (that make oracle driver to use SDO_RELATE) is more slow, is a Oracle issue. In Mapserver case, the RELATE will return more precisely data, but will be more slow (was Ivano wrote). But for a good performance it's important to have a correct index and the information for your table in the USER_SDO_GEOM_METADATA needs to be right, the extents stored need to be right and not generic (like -180 +180, -90 +90).

The second is about the "scale", another bugs was fixed for 5.0 version and I believe that solved some "errors" (like from this bug). The bugs was relate with memory and connection management.

As Benedikt wrote, the Oracle drive just read from database the columns used. If you specify a LABELITEM, CLASSITEM for your layer, the driver will append this columns in the SQL that will be send to database.

If the problem persist we can investigate more and reopen this bug. Benedikt, just a little questions, in your test you used the data stored inside of the Oracle and read using Mapserver (with SDO and SDE), right? SDO was more quickly than SDE with RELATE too? Was the kind and the number of data that you used? Ivano, did you replay your stress test? How many connections Mapserver (using SDO) "scaled"?

Best regards. Fernando Simon

[ivanopicco]

As I wrote ArcSDE is faster than Oracle. But the difference is small. See my new tests results in "Summary Report".

The tests was performed subsequentially with SDE and SDO, 2 different FCGI (reuse connection) on a loop of different extent (34 extent, the different extente at every loop is used to minimize the database cache) and 28 concurrent users.

[bene]

SDO was more quickly than SDE with RELATE too?

"SDE with RELATE"? :-) Anyway: Using Mapserver/Oracle-Driver had double speed in comparison to Mapserver/SDE-Driver. In the "Oracle-Driver"-case: Using RELATE/FILTER did'nt give real differences.

Was the kind and the number of data that you used?

Approx. 1800 Lines.

One difference between my tests and Ivanos: I didn't work with multithreading. The maps where created one after the other ... Maybe the Oracle-driver has some Mutexes?