[mapserver-users] RE: MySQL data

[Paul Ramsey]

Did you create spatial indexes on your MySQL tables?

On Wed, Jun 23, 2010 at 11:29 AM, Kent Morrison
<KMorrison at advantrack.com> wrote:
> Thanks for the reply.
>
> Below is the information used to generate tiles from TigerLine data for 1 county.. The tiles are generated but the perfomance is excutiatinly slow. This is a test set of data.
>
> I guess what I need is some guidance on using mapserver with MySQL and how to optimize the performance. Ultimately my tables will contain millions of rows of data pulled from difference sources.
>
>
>
> Versions:
> Mysql: 5.1.41
> Mapserver: using  MS4W is version 3.0 beta 11  install
>
> Access method: ? (see map file text below)
>
> Spacial data: MySQL geometries
>
> Map File:
> MAP
>
>  IMAGETYPE      AGG
>  SIZE           256 256
>  SHAPEPATH      "/mapdata/shapefiles"
>  IMAGECOLOR     255 255 255
>  TRANSPARENT    ON
>  FONTSET        "../fonts/fonts.list"
>
>  EXTENT -95.909985 29.523624 -95.014496 30.110732
>
>  CONFIG PROJ_LIB "c:\ms4w\proj\nad\"
>
>
> CONFIG MS_ERRORFILE "c:\ms4w\tmp\errors.txt"
> DEBUG 2
>
>  PROJECTION
>     "init=epsg:4326"
>  END
>
>  OUTPUTFORMAT
>    NAME 'AGG'
>    DRIVER AGG/PNG
>    IMAGEMODE RGB
>  END #Outputformat
>
>  LAYER
>    DEBUG ON
>
>    NAME        roads
>    STATUS      ON
>    CONNECTIONTYPE OGR
>
>    CONNECTION  "MYSQL:revgeotest,host=tws1r-tj,user=*****,password=*****,port=3306,tables=tl_2009_48201_edges"
>    DATA        "SELECT SHAPE as the_geom, fullname FROM tl_2009_48201_edges"
>
>    TYPE        LINE
>
>   CLASS
>        STYLE
>                COLOR   0 0 0
>                WIDTH   8
>        END #Style
>    END #End Class
>
>  END #layer
>
>
>
> END #Map
>
> -----Original Message-----
> From: Lime, Steve D (DNR) [mailto:Steve.Lime at state.mn.us]
> Sent: Friday, June 18, 2010 8:39 AM
> To: Kent Morrison; 'mapserver-users at lists.osgeo.org'
> Subject: RE: MySQL data
>
> Hi Kent: Probably need more information regarding:
>
>  - versions
>  - access method (are you using GDAL?)
>  - what type of spatial data (MySQL geometries vs points in x/y columns)
>  - etc...
>
> Steve
> ________________________________________
> From: mapserver-users-bounces at lists.osgeo.org [mapserver-users-bounces at lists.osgeo.org] On Behalf Of Kent Morrison [KMorrison at advantrack.com]
> Sent: Thursday, June 17, 2010 2:13 PM
> To: 'mapserver-users at lists.osgeo.org'
> Subject: [mapserver-users] MySQL data
>
> I have data stored in MySQL. When I configure the map file to pull from a small subset of the data, tiles are generated correctly. If I change the map file to point to the larger master table, map server returns "CGI Timeout". I dont't really want to breakdown the master data table into hundreds of smaller tables. I need additional informaiton and/or suggestions on how to get mapserver to work with millions of rows of data in MySQL.
>
> Kent W. Morrison
> Software Development Manager
> Advanced Tracking Technologies, Inc.
> 6001 Savoy, Suite 600, Houston Texas 77036
> (W) 713-353-6065 (C) 832-434-8175
> <mailto:kmorrison at advantrack.com>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>