[mapserver-users] Grave performance issue with MapServer and GDAL/OCI driver

[Frank Warmerdam]

Niccolo Rigacci wrote:
> I experienced a severe performance problem with MapServer 
> configured as WFS server, taking data from Oracle via GDAL/OCI 
> driver.
> 
> All the features take about 9.6 Mb of xml data (via WFS) or 8.4 
> Mb of text data (via ogrinfo).
> 
> Retrieving all the features via WFS from a shapefile takes less 
> than one second, retrieving the same WFS from Oracle takes about 
> 470 seconds. Retrieving data via the ogrinfo utility takes about 
> 3 seconds:
> 
> Tests are performed from the same Linux box, taking data in this 
> way:
> 
>   1 s: wget <- http <- Remote_host <- MapServer <- GDAL <- shapefile
> 470 s: wget <- http <- Remote_host <- MapServer <- GDAL/OCI <- localhost <- Oracle
>   3 s: ogrinfo <- GDAL/OCI <- Remote_host <- Oracle
> 
> It seems that wget (int the Oracle case) starts at about 400 
> Kb/sec transfer rate, then it drops down to 13 Kb/sec a few 
> seconds later.
> 
> Does somebody have any idea where is the bottleneck? How can I 
> debug such slodown?

Niccolo,

This is almost certainly due to the two-pass query approach in mapserver.
On the first pass the id's of all features matching the query are collected
and then the features are fetched, one by one, by id.  In the case of the
oci driver in OGR a fetch by id issues an sql select statement.  So you are
basically getting one sql select statement per features which is very
inefficient.

What you need if you want to do large amounts of feature query - such as for
WFS - is the new one pass query mechanism that Steve is working on.  I'm not
exactly clear on it's status.

Note that this two pass query anti-optimization does not affect feature
drawing which is accomplished in one pass.  It also does not significantly
affect formats like shape where fetching by id's has no noticable overhead.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent