[MS4W-Users] MS4W 4.0 Render Speeds with GDB, Spatialite, MySQL, and POSTGIS

[Jeff McKenna]

You might also benefit from reviewing the results of the past "WMS 
Shootout" exercises at FOSS4G events, where we/MapServer compared map 
draw speed against other mapping engine teams, and reviewed several 
formats  (OGR, PostGIS, SHP, etc) : (example results 
http://svn.osgeo.org/osgeo/foss4g/benchmarking/wms/2011/presentation/) 
You can google for other years and compare.  Today we'd probably want to 
analyze GeoPackage as well.


--

Thank you for using MS4W.
"MS4W: open doors as well as windows"


-- 
Jeff McKenna
MapServer Consulting and Training Services
https://gatewaygeomatics.com/



On 2019-06-04 11:29 AM, IGIS via MS4W-Users wrote:
> In upgrading to MS4W 4.0, we've been exploring how to store data as 
> well.  We recently tried some data (~200,000 features in ~50 
> layers/tables) and set the data up as (ESRI) GDB, Spatialite, MySQL, and 
> PostGIS.
> 
> We found PostGIS was fastest at rendering the map images, and I'm 
> curious why.  A sample of a simple railroad layer is at the end, and the 
> only changes were the CONNECTIONTYPE, CONNECTION, and DATA.
> 
> Running shp2img we got results on one of the mapfiles that uses some of 
> the bigger layers:
> 
>     5.99    mysql
>     3.12    sqlite
>     2.91    gdb
>     2.26    postgis
> 
> Is there any reason why PostGIS is fastest, and MySQL is so slow for us?
> 
> It's the same hardware and configured per the manual 
> <https://mapserver.org/input/vector/> so I was just curious if POSTGIS 
> is just faster than OGR, or if there's something I'm missing in the others.
> 
> Thanks!
> 
> *MAP FILE:*
>      LAYER
>          NAME 'rail'
>          STATUS ON
>          TYPE LINE
>          CONNECTIONTYPE POSTGIS
>          CONNECTION "host=localhost dbname=name user=user password=pass 
> port=#"
>          DATA "geom FROM geo_rail USING UNIQUE objectid USING srid=3857"
>          CLASS
>              NAME "Rail Road Tracks"
>              STYLE
>                  SIZE 1
>                  COLOR 0 0 0
>              END
>              STYLE
>                  SYMBOL '|'
>                  SIZE 8
>                  GAP -10
>                  COLOR 92 51 23
>              END
>          END
>      END # layer
> 
> This creates an image with a Mapserver URL like: 
> /fcgi-bin/mapserv.exe?MAP=/path/to/file.map&SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&FORMAT=png8&TRANSPARENT=true&LAYERS=rail&CRS=EPSG%3A3857&STYLES=&WIDTH=1024&HEIGHT=768&BBOX=COORDS 
> <https://giles.interactivegis.com/fcgi-bin/mapserv.exe?MAP=/ms4w/apps/giles/maps/postgis/transportation.map&SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&FORMAT=png8&TRANSPARENT=true&LAYERS=rail&CRS=EPSG%3A3857&STYLES=&WIDTH=2881&HEIGHT=1529&BBOX=-9173685.495930785%2C4372790.404881292%2C-9063577.956685988%2C4431226.51300531>
> 
>