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

[Steve Lime]

As an update on single-pass queries, I'm working on implementation now in trunk. We've
done a proof-of-concept in a sandbox and saw orders of magnitude improvements in both
Oracle and PostGIS. Shapefiles were about the same. These enhancements are scheduled
to be the next release due out early this fall.

More details on the implementation can be found at:

  http://mapserver.org/development/rfc/ms-rfc-52.html 

Steve

>>> On 6/29/2009 at 9:51 AM, in message <4A48D4F2.3020203 at pobox.com>, Frank
Warmerdam <warmerdam at pobox.com> wrote:
> 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,