[Mapserver-users] Spatial Database Connections

[Jan Hartmann]

I'll try to put this into CVS this weekend. A description of the 
solution is on the mapserver-dev list, but its archive hasn't reached 
December yet. It isn't very complex, but a few lines need to be added to 
the central code (I don't want to horse around too much with that), and 
things have to be coordinated with the database programmers (SDE, 
Oracle, PostGis). If all goes well, something should be available for 
testing next week.

Note that in this scenario only one single map wil be able to use the 
same database connection for all its layers. If you zoomin (i.e. send a 
second MapServer request to the server), a new connection has to be 
opened. This is a much more complex problem. Web programs like MapServer 
CGI are stateless, i.e. they forget everything they have done after they 
have finished. You would need something like a a small server daemon 
listening to MapServer requests and holding open a permanent database 
connection for the duration of a user session. With PHP/PostGreSQL and 
PHP/Oracle you can do something like that, but you need to have PHP 
installed as an internal server module (DSO) and this is not supported 
for PHP/MapScript yet. As soon as MapScript supports PHP-DSO, it should 
be possible (in principle) to make these two database accessible with 
single connections per user session. I guess it would be possible to 
write something like that for SDE too, but it wouldn't be easy.

Jan Hartmann

Paul Ramsey wrote:
> You are absolutely correct in your understanding. 
> Yes, this probably is a performance bottleneck. The amount of connection
> induced latency will scale up with the number of db-sourced layers.
> Jan Hartmann posted a reasonable strategy to reduce the number of open
> connections to the number of unique databases being accessed. I do not
> know if he was planning on implementing it himself.
> Paul
> 
> Benjamin Wragg wrote:
> 
> 
>>Last month there was a discussion on this list regarding database
>>connections to SDE/Oracle Spatial and PostGIS. From what I could
>>understand, MapServer creates a database connection for each layer. So
>>if I have a map file with ten layers from a PostGIS database, and a user
>>requests the map file, 10 database connections would be created and once
>>the data is retrieved MapServer would terminate the connections. Then if
>>the user zoomed in on the map MapServer would create another 10
>>connections, retrieve the data and terminate the connections. If at the
>>same time another user accesses the same map file, a second lot of 10
>>database connections would be created.
>>
>>If I?m correct in my understanding of the way it works, is there any
>>performance problems with opening so many database connections?
>>
>>Thanks,
>>
>>Benjamin Wragg
> 
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> 

Jan Hartmann
Department of Geography
University of Amsterdam