[mapserver-users] WMS TIME parameter slow

[Heiko Schröter]

Am Mittwoch 10 März 2010 16:42:02 schrieben Sie:

Thanks for the hint.
Hm, i thought 4000 out of 4.5 Millions datasets for the whole year in 5 sec would be not that bad ....
The timing figure shall just show the huge difference between the two types of requests.

I will follow your advice and test the db. And yes the db is indexed.

Heiko

> Firstly, 2 seconds is not fast, so you have another performance troll
> beyond this one.
> 
> Secondly, it's impossible to know what's going on inside the database
> without seeing the actual SQL being generated. Turn on statement
> loggging in PgSQL and extract the SQL that is being run against the
> database. Then you can put that into PgAdmin or psql and get EXPLAIN
> ANALYZE results which will tell you why there's a problem, hopefully.
> 
> P.
> 
> On Wed, Mar 10, 2010 at 7:22 AM, Heiko Schröter
> <schroete at iup.physik.uni-bremen.de> wrote:
> > Hello,
> >
> > using mapserv (5.6.1) with PostGIS (8.3) and accessing ten-thousands of geo locations in the PostGIS db results in very slow speed when using the WMS TIME parameter:
> > (in this example the number is about 4000 datapoints for this day, timing measurements with DEBUG OFF).
> >
> > 1) Very fast using FILTER, approx. 5 seconds:
> > ....
> > CONNECTION "user={BF961C25A57BA226} password={C9748CA35D48D049} dbname={6B40E65B12B92106} host=localhost"
> > DATA "geocoord from xyz_ref using unique datum"
> > VALIDATION
> > 'filter' "^datum >= '[0-9]{4}-[0-9]{2}-[0-9]{2}' and datum <= '[0-9]{4}-[0-9]{2}-[0-9]{2}'"
> > END
> > FILTER "datum >= '2007-12-10' and datum < '2007-12-11'"
> > ....
> >
> > 2) Very slow using WMS TIME, approx 210 and more seconds:
> > (Leaving the VALIDATION/FILTER on or leaving out 'using unique datum' doesn't change a thing)
> > ...
> > METADATA
> > "wms_timeextent" "1970-01-01/2030-12-31"
> > "wms_timeitem" "datum"
> > "wms_timedefault" "2007-12-10/2007-12-11"
> > "wms_title" "xyz_r"
> > "wms_srs" "init=epsg:-1"
> > END
> > ...
> >
> > The MS ERRORFILE is very noisy spilling out all datapoints when running 2). This does not happen when running 1).
> > Database is the same. No other changes have been made.
> >
> > Why is mapserver so slow when using the WMS TIME parameter ?
> >
> > It would be nice to stick to the "official" WMS configuration and not to introduce a "vendor specific" workaround.
> >
> > Thanks and Regards
> > Heiko
> >
> > # ######################################################################################################
> >
> > /var/www/localhost/cgi-bin/mapserv -v
> > MapServer version 5.6.1 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP OUTPUT=SVG SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WFS_SERVER SUPPORTS=WCS_SERVER INPUT=EPPL7 INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE
> >
> > A complete Layer definition:
> >
> >        LAYER
> >                EXTENT -180 -90 180 90
> >                CONNECTIONTYPE POSTGIS
> >                NAME "SACURA_REF_R"
> >                PROCESSING "CLOSE_CONNECTION=DEFER"
> >                OPACITY 60
> >                STATUS ON
> >                METADATA
> > #                       "wms_timeextent" "1970-01-01/2030-12-31"
> > #                       "wms_timeitem" "datum"
> > #                       "wms_timedefault" "2007-12-10/2007-12-11"
> >                        "wms_title" "xyz_ref_r"
> >                        "wms_srs" "init=epsg:-1"
> >                END
> >                PROJECTION
> >                        "init=epsg:4326"
> >                END
> >                CONNECTION "user={BF961C25A57BA226} password={C9748CA35D48D049} dbname={6B40E65B12B92106} host=localhost"
> >                DATA "geocoord from xyz_ref using unique datum"
> >                VALIDATION
> >                        'filter' "^datum >= '[0-9]{4}-[0-9]{2}-[0-9]{2}' and datum <= '[0-9]{4}-[0-9]{2}-[0-9]{2}'"
> >                END
> >                FILTER "datum >= '2007-12-10' and datum < '2007-12-11'"
> >
> >                TYPE POLYGON
> >                SIZEUNITS meters
> >                CLASS
> >                        DEBUG OFF
> >                        NAME "xyz_ref_class"
> >                        STYLE
> >                                OUTLINECOLOR 0 0 0
> >                                COLOR [rfarbe]
> >                        END
> >                END
> >        END # Layer
> > _______________________________________________
> > mapserver-users mailing list
> > mapserver-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/mapserver-users
> >