Timeout issue between mapserver and postgresql

Wed Apr 5 11:20:20 EDT 2006

You could try and use the connection pooling by adding:

PROCESSING "CLOSE_CONNECTION=DEFER"

to all of your layers in you haven't done so.

Bart

Gregory S. Williamson wrote:

>Franck --
>
>Drat -- doesn't seem like my ideas were relevant ... in general a real error will show up in postgres logs; you might consider bumping the level of logging up for a while to log all sql to see if the received requests make sense ... [log_statement = all] 
>
>A thought -- sodden -- almost bed time for me -- 10 refresh of 20 layers could mean as many as 200 queries at once, more or less ? I am not sure if MSS does a differnet query for each layer but it might ... in which case 10x20 is greater than your 100 connections ? If this is case try bumping max connectiuons up to 220 or something and see if that helps any.
>
>You might try monitoring the machine ... vmstat and/or top for *NIX machines ... see if the postgres server is getting hammered. If so it might indicate issues with the postgres data, including missing indexes, lack of recent statistics leading to bad plans and sequential scans, etc. High i/o wait times would indicate lots of disk activity, for instance. Capturing the SQL being sent to postgres and then running an EXPLAIN ANALYXE ... on it might reveal issues that postgres is having.
>
>Check the postgres stats views and tables (for instance, check pg_stat_all_tables for tables with lots of sequential scans, which tend to be slow and eat up disk i/o and time).
>
>If you can, compile a Mapserver with debugging output and check the (apache ?) logs for timing of different layers. 
>
>Not sure what else to suggest ... hopefully someone else with a sharper head than mine can help.
>
>G
>
>-----Original Message-----
>From:	UMN MapServer Users List on behalf of zze-SIGALE PORTANERI F ext RD-BIZZ-SOP
>Sent:	Wed 4/5/2006 6:54 AM
>To:	MAPSERVER-USERS at LISTS.UMN.EDU
>Cc:	
>Subject:	Re: [UMN_MAPSERVER-USERS] Timeout issue between mapserver and postgresql
>
>Hi Greg,
>
>Thanks for you prompt answer!!!
>
>The postgres log just shows several lines like:
>
>"WARNING: there is no transaction in progress"
>...
>
>May be the level of debug is too low... but on an other side, I guess that a fatal error would had been logged anyway...
>
>
>The posgresql.conf sets:
>
>max_connections : 100
>work_mem : 1024 
>superuser_reserved_connections : 2
>
>
>I am not sure max_connections is the problem since when a session is stuck, I still can open sessions in a new browser using the "uuuuu" user...
>
>Thanks again,
>Franck
>
>
>
>-----Message d'origine-----
>De : UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] De la part de Gregory S. Williamson
>Envoyé : mercredi 5 avril 2006 14:39
>À : MAPSERVER-USERS at LISTS.UMN.EDU
>Objet : Re: [UMN_MAPSERVER-USERS] Timeout issue between mapserver and postgresql
>
>What error(s) is/are postgres reporting ?
>
>Check the configuration file (postgresql.conf) for postgres and see what max_connections is set to. It may be low enough that you are bumping into this threshold. Each connection takes work_mem kilobytes so you don't want to have too many connections allowed with a lot of memory for each; it can cause poor performance on the postgres side of things. Not having enough connections could cause the problem you describe.
>
>If the superuser_reserved_connections is set you might see this when you have run out of normal connections for user "uuuuu" but connecting as user postgres from a terminal might be allowed if there are less than superuser_reserved_connections being used by postgres or other super user accounts.
>
>HTH,
>
>Greg Williamson
>DBA
>GlobeXplorer LLC
>
>
>-----Original Message-----
>From:	UMN MapServer Users List on behalf of zze-SIGALE PORTANERI F ext RD-BIZZ-SOP
>Sent:	Wed 4/5/2006 5:26 AM
>To:	MAPSERVER-USERS at LISTS.UMN.EDU
>Cc:	
>Subject:	[UMN_MAPSERVER-USERS] Timeout issue between mapserver and postgresql
>
>
>Hi list,
>
>I have a dual server configuration with :
>	server 1:  Cartoweb 3 + php 5.0.3  + mapserver 4.6.0 
>	server 2:  postgresql 8.0.3 + postgis 0.9.1 
>
>Everything is OK during the first part of the session, the cartoweb well displays the mapserver generated maps thought the postgis data, the connection is OK.
>
>After some time (let say 10 refresh of the about 20 layers mapfile), I have a timeout issue :  The message is the following
>
>FATAL ServerContext - Error in msPOSTGISLayerOpen(): couldnt make connection to DB with connect string 'host=XXX.XXX.XXX.XXX dbname=ddddd user=uuuuu password=pppppp'. 
>Error reported was 'could not connect to server: Connection timed out Is the server running on host "XXX.XXX.XXX.XXX" and accepting TCP/IP connections on port 5432? '. 
>
>The strange thing is that when this happen, I still can open a new session (new browser window) in parallel and access to the same data (so the database is still up and running).
>
>Any idea to where investigate?
>
>Thanks in advance,
>Franck
>
>
>
>
>!DSPAM:4433cc2d93101804284693!
>
>
>
>  
>

-- 
Bart van den Eijnden
OSGIS, Open Source GIS
http://www.osgis.nl