[mapserver-users] long-running postgres queries initiated bymapserver

[Rahkonen Jukka]

Hi,
 
This is a problem with big databases and standard WFS where users have no limits and they can build however complicated queries.  Indices are essential but they cannot handle all situations. For example strings from the middle of text fields with propertyIsLike usually leads to full table scan. Finding intersections with a polygon of tens of thousands or vertices is heavy. By using a lots of ANDs and ORs and some imagination and knowledge of WFS and databases a WFS user can rather easily build requests which will jam your database server. A clever user can even guess what database you have behind WFS by experimenting what kind of queries are exceptionally slow.
 
If you are running some critical production system I believe you should consider making a kind of proxy service in front of the real WFS server. The proxy should investigate the GetFeature requests and deny or modify them by adding some additional filter elements if they do not fulfil your rules.
 
-Jukka Rahkonen-
 
 
________________________________

Lähettäjä: mapserver-users-bounces at lists.osgeo.org [mailto:mapserver-users-bounces at lists.osgeo.org] Puolesta Matt Mendick
Lähetetty: 21. joulukuuta 2010 18:44
Vastaanottaja: Carlos Ruiz; mapserver-users at lists.osgeo.org
Aihe: RE: [mapserver-users] long-running postgres queries initiated bymapserver



	Carlos,
	 
	Thank you for your response, and indeed the tables do have indices. The spatial search is just fine and comes back very quickly, however the text-based searches do not come back quickly (propertyIsLike for example).  I suppose I wasn't clear enough in my original question.  Mapserver does a case insensitive "like" query on the postgres server and on tables that are very large (millions of rows), this takes too long.  We are training the customers to search for things more intelligently, but we cannot stop them entirely.  We are looking into a full-text indexing methodology, but in the meantime, we need to solve the problem of the long-running queries.
	 
	-Matt

________________________________

	From: Carlos Ruiz [mailto:boolean10001 at yahoo.com] 
	Sent: Tuesday, December 21, 2010 10:23 AM
	To: Matt Mendick; mapserver-users at lists.osgeo.org
	Subject: Re: [mapserver-users] long-running postgres queries initiated by mapserver
	
	
	Matt,
	
	I suggest to enhance the PostgreSQL performance at first. Each table with geometry must have a GiST spatial 
	index. When you upload a shape file to PostgreSQL, the shp2pgsql creates this index specifying the -I 
	parameter.
	
	Have you uploaded your data in this way ?
	
	If the geometry have been modified with PostGIS, you must recompute statistics to have the index updated.
	
	Check if you have a spatial index in your table.
	
	Cheers from México
	
	
	IC Carlos Ruiz

	
________________________________

	From: mattmendick <matt.mendick at pictometry.com>
	To: mapserver-users at lists.osgeo.org
	Sent: Tue, December 21, 2010 8:48:19 AM
	Subject: [mapserver-users] long-running postgres queries initiated by mapserver
	
	
	Hi All-
	
	I'm running mapserver 5.6.3 under centos 5.4, using fastcgi. I'm using
	postgres as the data storage container, and mapserver is primarily serving
	WFS requests.  Sometimes, people do WFS queries that take a very long time
	(searching for "virginia" on a nation-wide layer) and eventually postgres
	will return with the result after a long time (hours later), however the
	client has cancelled the WFS request.  Is there any way for mapserver to
	know that the client has cancelled the http request, and it can therefore
	stop the DB query to postgres?  I tried using persistent and non-persistent
	connections with this:
	
	PROCESSING "CLOSE_CONNECTION=DEFER"
	
	but that didn't change the behavior.
	
	Thanks a lot!
	-- 
	View this message in context: http://osgeo-org.1803224.n2.nabble.com/long-running-postgres-queries-initiated-by-mapserver-tp5855539p5855539.html
	Sent from the Mapserver - User mailing list archive at Nabble.com.
	_______________________________________________
	mapserver-users mailing list
	mapserver-users at lists.osgeo.org
	http://lists.osgeo.org/mailman/listinfo/mapserver-users
	

	NOTICE: This message is covered by the Electronic Communications Privacy Act, Title 18, United States Code, Sections 2510-2521. This e-mail and any attached files are the exclusive property of Pictometry International Corp., are deemed privileged and confidential, and are intended solely for the use of the individual(s) or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or believe that you have received this message in error, please delete this e-mail and any attachments and notify the sender immediately. Any other use, re-creation, dissemination, forwarding or copying of this e-mail is strictly prohibited and may be unlawful. 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20101222/1ca36732/attachment.htm>