[postgis-users] Query crashed

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Stephen Woodbridge
> Sent: 22 November 2005 14:49
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Query crashed
> 
> Hi Mark,
> 
>  From top:
> 
> last pid: 70096;  load averages:  0.00,  0.00,  0.00   up 117+01:48:34
> 09:31:36
> 40 processes:  1 running, 39 sleeping
> CPU states:  0.0% user,  0.0% nice,  0.0% system,  0.0% interrupt,  100%
> idle
> Mem: 53M Active, 716M Inact, 170M Wired, 53M Cache, 111M Buf, 3376K Free
> Swap: 4096M Total, 948K Used, 4095M Free


Hi Steve,

Wow that looks like a default installation! By default PostgreSQL will only
use a very small amount of memory which would explain why these queries are
taking so long to process. It looks like you have a box with 1Gb physical
RAM so we'll use those as a basis to alter the configuration.

As a rule of thumb, the default parameters should be:

shared_buffers ~ 25% of physical RAM
effective_cache_size ~ 75% of physical RAM
work_mem - around 16Mb should help out

There parameters are documented in more detail here:
http://www.postgresql.org/docs/8.0/interactive/runtime-config.html. So
you'll need to open up your /usr/local/pgsql/data/postgresql.conf file and
uncomment these entries so that they read:

shared_buffers = 32768 
effective_cache_size = 98304
work_mem = 16384

Once this is done, you'll need to stop your postgresql server and increase
the amount of shared memory to ~ 260Mb using the information at
http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html#SYSVIPC
. If you don't do this, PostgreSQL will refuse to start since it is now
asking for more memory than the OS is willing to give it, however it will
leave a useful message in the logfile indicating what the problem is. You
may need to reboot after for this to take effect - you'll need one of the
*BSD guys to help you out there.

Once you've altered the settings and the server is up and running, confirm
the new values have taken effect with SHOW ALL and then try just the simple
index query on its own:

EXPLAIN ANALYZE select count(*) from roadseg r, streets s where r.the_geom
&& s.the_geom;


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.