[postgis-users] postgis tuning parameters

[Mark Cave-Ayland]

On Wed, 2007-09-26 at 09:16 -0700, Dylan Beaudette wrote:
> Hi,
> 
> I have looked over a couple documents on tuning a postgis server:
> 
> 1.  
> http://postgis.refractions.net/pipermail/postgis-users/2006-March/ 
> 011539.html
> 2. http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
> 
> When performing complex queries, with thousands of records and  
> geometries I am noticing that the postmaster process never exceeds 1%  
> of my system's memory (2Gb). I have tried setting the shared_buffers  
> parameter to values > 2000 ... but this only leads to an error when the  
> postgres server is restarted: cannot allocate this much mem, check the  
> SHMMAX kernel parameter.
> 
> Perhaps some answers on are this page:
> http://www.redhat.com/docs/manuals/database/RHDB-2.1-Manual/admin_user/ 
> kernel-resources.html
> 
> or in the minds of people on the list. The machine in question is  
> mostly used for such queries and I would like postgis to be able to  
> better utilize the resources available.
> 
> Cheers,
> 
> Dylan


Hi Dylan,

Indeed, everything you need to know should be on that page - here is the
most recent version:
http://www.postgresql.org/docs/8.2/interactive/kernel-resources.html.
Experiment with different settings, but most people tend to recommend
about 25% of your available RAM (~60,000 buffers for 2Gb) for a
dedicated database server.


HTH,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk