mapserver initialization delays

[Frank Koormann]

Scott,

* Scott Melby <smelby at TURNKEY-TECHNOLOGY.COM> [060612 22:29]:
> Thanks for the response.  Although I agree that there is definitely 
> something that is getting "evicted" from memory, it does not appear to 
> me that the amount of pertinent data that is cached in memory is the 
> issue.  The reason I say this, is that I can perform hundreds of 
> queries, all with differing bounding boxes and differing dataFilter 
> parameters (no result set overlap), and only the first query after a 
> long period is slow.

Cache in the postgresql sense does not mean caching query results but
data. Looking at your sample request of your initial post you seem to
issue a 

        geometry && 'BOX3D(-180 -180,180 180)'

search. Given your data is in latlon this will result in a full table
scan. Depending on the grade of fragmentation of your data even with sharper
filters it might be necessary to load many data pages from disk into ram
to pick out the objects in question.

> Regardless of the cause of the delays, I really can't have my users 
> hitting a 40+ second delay for an interactive query on the site.  Are 
> there known strategies for dealing with large datasets? 

Performance tuning usually depends on a bunch of parameters. However,
some basic rules: Use fast disks and much RAM. You might want to
optimize the "shared_buffers" parameter in your postgresql.conf.
 
To further elaborate you could also take the queries generated by
mapserver and feed them directly into psql to see if this is a 
mapserver startup or database performance issue.

Some usually quoted performance tipps for PostgreSQL:

http://www.powerpostgresql.com/PerfList
http://candle.pha.pa.us/main/writings/pgsql/hw_performance/0.html

HTH,

        Frank

-- 
Frank Koormann                             <frank.koormann at intevation.de>
 Professional Service around Free Software       (http://intevation.net/)
 FreeGIS Project                                 (http://freegis.org/)
 PostGIS Support     (http://intevation.de/services/gis/postgis.en.html)