[mapserver-dev] Query Sandbox: PostGIS

[Steve Lime]

I guess the question is if 8x is fast enough. I was thinking of adding a feature cache 
that could be used in addition to the new code (and would be tunable). Small result
sets would only hit the db once.

Jim, do your patches need to be applied in any order?

Steve

>>> "Jim Klassen" <Jim.Klassen at ci.stpaul.mn.us> 03/12/09 12:21 AM >>>
I made some updates to my previous patch and attached a new patch to #2933.

The new results for the same dataset are:
* trunk takes 6m8s (1m19s in mapserver code)
* prepare takes 1m35s (23.5s in mapserver code)
* single-pass takes 46.5s (26.5s in mapserver code)
* both function=none and function=filter function, although function=none returns one more row than function=filter. function=filter agrees with postgis and shape. I haven't had time to track this down.

Conclusion:
* Single-pass is about 8x faster than trunk.
* In single-pass PostGIS is 2.4x faster than Oracle.

BTW: the bus error in trunk with filter=none appears to be related to a failed malloc. I haven't looked into it enough yet to know for sure what is going on.

>>> "Jim Klassen" <Jim.Klassen at ci.stpaul.mn.us> 03/11/09 6:47 PM >>>
Ok, I feel kind of stupid here in that I should have noticed something was wrong with a file that small. (The DBF of the shape was 300MB). My test dataset is the address points for the City of St. Paul (2D Point with 32 attributes).

Note: single-pass is in ticket #2933, prepare is in ticket #2927, my trunk is at r8765.

It turns out:
* The dataset is 139,000 rows (excluding the ones with null geometry, which mapserver skips)
* The final GML size is 364MB
* The single-pass time to generate that file is 3m17s (1m6s in mapserver the rest waiting for oracle over the network)
* The trunk time to generate the file is 3m21s (1m2s in mapserver) with a bus error at the 100MB mark.
* The trunk+prepare time to generate the file is 1m3s (20s in mapserver) with a bus error at the 100MB mark.
* The PostGIS (from trunk) time to generate the file is 1m21s (17s in mapserver) and finished successfully.
* THe PostGIS (from single-pass) time to generate the file is 19s (14s in mapserver) and finished successfully.
* There is a bug somewhere on using function = none (vs. function = filter which works fine) where the record id seems to be getting mangled causing single-pass to exit early.  Also there appears to still be a bug in trunk given the bus errors.
* MapServer appears to use about 25% cpu and 200MB ram on single-pass vs. 33% cpu and 13MB ram in trunk (according to activity monitor). Shark says most of this is spent in OCIStmtFetch2() in single-pass and OCIStmtExecute() in trunk and vs. 38% cpu and 13MB ram for trunk+prepare.

Conclusion:
* Just looking at the rates, single-pass is about 3.58x faster than trunk, and prepare is about 3.2x faster than trunk.
* For PostGIS, I get single-pass is about 4.3x faster than trunk.
* In single-pass PostGIS is 10.9x faster than Oracle. In trunk PostGIS is 8.9x faster then Oracle.
* There is something different with the oracle at the office vs. the sprint because I wasn't seeing bus errors at the sprint.

>>> Jim Klassen 03/11/09 11:39 AM >>>
I added the OracleSpatial patch to track as #2933.
 
On our 220k point address database the query time went from 6 minutes down to 32 seconds and produced about 360K of GML.

>>> On 3/10/2009 at 10:33 AM, Paul Ramsey <pramsey at opengeo.org> wrote:
I have updated the PostGIS driver in the sandbox to use the new logic.
My timing on outputting 5Mb of GML via WFS query has gone from 12s to
0.5s, a 24x improvement.

Great work Steve!

P.
_______________________________________________
mapserver-dev mailing list
mapserver-dev at lists.osgeo.org 
http://lists.osgeo.org/mailman/listinfo/mapserver-dev

_______________________________________________
mapserver-dev mailing list
mapserver-dev at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-dev

_______________________________________________
mapserver-dev mailing list
mapserver-dev at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-dev