Really slow map creation from POSTGIS DB

[Matt McClelland]

Hi All

I have a set of contours in a SHP file
If I point my map file to the SHP file I can generate a WMS image in under 1 second
Shp file is just shy of 200MB

I then use shp2pgsql to load the same dataset into the DB
I added an index for the_geom
And am requesting the data in the mapfile using the same EPSG

The map draws correclty but is now taking about 30secs when using the DB.

Any ideas on how to make it faster.

Thanks

Matt  :)


_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[Stephen Woodbridge]

On 4/28/2012 8:44 AM, Matt McClelland wrote:

> Hi All
>
> I have a set of contours in a SHP file
> If I point my map file to the SHP file I can generate a WMS image in
> under 1 second
> Shp file is just shy of 200MB
>
> I then use shp2pgsql to load the same dataset into the DB
> I added an index for the_geom
> And am requesting the data in the mapfile using the same EPSG
>
> The map draws correclty but is now taking about 30secs when using the DB.
>
> Any ideas on how to make it faster.

Simplify the data for the that zoom level.

If you are all the way zoomed out and drawing all the data, then the
shapefile will be fastest. An index does not help if you are drawing all
the data as you need to do a full table scan anyway to get all the
records. It is simply faster to read 200MB from disk than from the
database because the database has to fetch the same data and may have to
jump through some hoops to get it out of extended storage, then it has
to encode the data and send it over the connection and the client has to
decode the data.

Do you really need all the resolution of the data at this scale? If not
create another column and simplify the geometry into that column and set
up your mapfile to query the simplified data at this zoom and to query
the detailed data as you zoom in.

-Steve W
_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[Puneet Kishor]

On Apr 28, 2012, at 8:20 AM, Stephen Woodbridge <[hidden email]> wrote:

> On 4/28/2012 8:44 AM, Matt McClelland wrote:
>> Hi All
>>
>> I have a set of contours in a SHP file
>> If I point my map file to the SHP file I can generate a WMS image in
>> under 1 second
>> Shp file is just shy of 200MB
>>
>> I then use shp2pgsql to load the same dataset into the DB
>> I added an index for the_geom
>> And am requesting the data in the mapfile using the same EPSG
>>
>> The map draws correclty but is now taking about 30secs when using the DB.
>>
>> Any ideas on how to make it faster.
>
> Simplify the data for the that zoom level.
>
> If you are all the way zoomed out and drawing all the data, then the shapefile will be fastest. An index does not help if you are drawing all the data as you need to do a full table scan anyway to get all the records. It is simply faster to read 200MB from disk than from the database because the database has to fetch the same data and may have to jump through some hoops to get it out of extended storage, then it has to encode the data and send it over the connection and the client has to decode the data.
>
> Do you really need all the resolution of the data at this scale? If not create another column and simplify the geometry into that column and set up your mapfile to query the simplified data at this zoom and to query the detailed data as you zoom in.

All of the above is great advice. Additionally, implement a tilecache server; why redraw an image if it has already been drawn.

That said, while a db is going to be slower than the file system in many cases, there are many reasons why your db is performing so slowly. Thirty times slow is too much, and 30s for a query, no matter what query, is wrong. Benchmark your entire stack to find bottlenecks... Is the query itself taking that long? Is something else going wrong? Is the exact query that MapServer is performing actually using your indexes?

--
Puneet Kishor_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[Stephen Woodbridge]

On 4/28/2012 9:44 AM, Puneet Kishor wrote:

> On Apr 28, 2012, at 8:20 AM, Stephen
> Woodbridge<[hidden email]>  wrote:
>
>> On 4/28/2012 8:44 AM, Matt McClelland wrote:
>>> Hi All
>>>
>>> I have a set of contours in a SHP file If I point my map file to
>>> the SHP file I can generate a WMS image in under 1 second Shp
>>> file is just shy of 200MB
>>>
>>> I then use shp2pgsql to load the same dataset into the DB I added
>>> an index for the_geom And am requesting the data in the mapfile
>>> using the same EPSG
>>>
>>> The map draws correclty but is now taking about 30secs when using
>>> the DB.
>>>
>>> Any ideas on how to make it faster.
>>
>> Simplify the data for the that zoom level.
>>
>> If you are all the way zoomed out and drawing all the data, then
>> the shapefile will be fastest. An index does not help if you are
>> drawing all the data as you need to do a full table scan anyway to
>> get all the records. It is simply faster to read 200MB from disk
>> than from the database because the database has to fetch the same
>> data and may have to jump through some hoops to get it out of
>> extended storage, then it has to encode the data and send it over
>> the connection and the client has to decode the data.
>>
>> Do you really need all the resolution of the data at this scale? If
>> not create another column and simplify the geometry into that
>> column and set up your mapfile to query the simplified data at this
>> zoom and to query the detailed data as you zoom in.
>
>
> All of the above is great advice. Additionally, implement a tilecache
> server; why redraw an image if it has already been drawn.
>
> That said, while a db is going to be slower than the file system in
> many cases, there are many reasons why your db is performing so
> slowly. Thirty times slow is too much, and 30s for a query, no matter
> what query, is wrong. Benchmark your entire stack to find
> bottlenecks... Is the query itself taking that long? Is something
> else going wrong? Is the exact query that MapServer is performing
> actually using your indexes?

In pgAdmin or from the psql commandline, how long does it take to do:

select * from yourtable;

Do you have the exact query that mapserver is making? You can get it by
adding to the MAP object

DEBUG 10
CONFIG MS_ERRORFILE "stderr"

then looking in the apache error_log.

-Steve
_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[Julien Cigar]

On 04/28/2012 14:44, Matt McClelland wrote:

Hi All

I have a set of contours in a SHP file
If I point my map file to the SHP file I can generate a WMS image in under 1 second
Shp file is just shy of 200MB

I then use shp2pgsql to load the same dataset into the DB
I added an index for the_geom
And am requesting the data in the mapfile using the same EPSG

The map draws correclty but is now taking about 30secs when using the DB.

are you sure your postgresql is well configured ? can you pastebin your postgresql.conf and some details about your configuration ?

Any ideas on how to make it faster.

Thanks

Matt  :)

_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users

_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users