[postgis-users] postgis tuning parameters

Tue Oct 9 22:49:51 PDT 2007

Hi,

Would it be a problem if I posted this thread on a (tangentially related)
forum (http://forum.manifold.net/Site/Default.aspx)? A couple of similar
queries have popped up there and this is such a useful reply.

Henry Holland

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Dylan Beaudette
> Sent: 09 October 2007 22:12 PM
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] postgis tuning parameters
> 
> 
> On Thursday 27 September 2007, Kevin Neufeld wrote:
> > Hi Dylan,
> >
> > As a developer on a stand-alone postgresql box, I have the following
> > suggestions:
> >
> > shared_buffers - This memory, of course as the name implies, is the 
> > amount of memory shared between concurrent database connections. 
> > Typically this should be about 50-75% of available RAM you have 
> > dedicated to postgresql on your box. So, a little math is 
> involved... 
> > take your total memory, subtract that needed by the OS, subtract 
> > anything else needed by other applications, and use 75% of the 
> > remainder.  This parameter is set on startup.  I've seen web-server 
> > boxes with 16GB of RAM configured such that shared_buffers consume 
> > about 12GB.  The idea here is simple: disks are slow, 
> memory is fast. 
> > The more data that will slowly float up into RAM the better.
> >
> > work-mem - This memory is the amount of RAM used by PostgreSQL when 
> > performing sort or hash join operations before being forced 
> to disk. 
> > This memory is a per-connection allocation, hence, it is tightly 
> > coupled with max_connections (the number of concurrent 
> connections on 
> > your database).  The default value is 1MB.  In my opinion, this is 
> > far, far too low to do anything useful in a database ... in 
> a timely 
> > manner.  For a web-service database with hundreds of concurrent 
> > connections, each using 1MB of ram, I suppose this makes 
> sense.  But 
> > for me, a developer on a small team hitting a development 
> box with 4GB 
> > of ram, I have this set to about 150MB or 200MB.  But since this 
> > variable can be set at run time, before I run a complex 
> one-time query 
> > (and no one else is using the box) I often set this to about 1.4GB 
> > using "SET work_mem TO 1400000;" before running my query.
> >
> > maintenance_work_mem - This memory is the amount of RAM used by 
> > PostgreSQL when performing maintenance operations like VACUUM, 
> > ANALYZE, CREATE INDEX, etc... before being forced to disk.  The 
> > default value of 32MB is again far too low.  I often have 
> this set to 
> > about 250MB on a 4GB box with 3 or 4 developers since it is 
> unlikely 
> > that every developer will be creating an index all at the 
> same time.  
> > Since this variable can also be set at run time, if the box is 
> > relatively idle from other developers, I often can create a large 
> > index quickly by issuing the command "SET maintenance_work_mem to 
> > 1400000;".
> >
> > You will have to strike some sort of balance.  shared_buffers makes 
> > sense with many concurrent connections, but not so much on a single 
> > developer box.  I would say, for you, set shared_buffers to 
> about 50% 
> > of availble postgres RAM and juggle the rest between work_mem and 
> > maintenance_work_mem. Remember you can always set the last 
> two on the 
> > fly!
> >
> > Also, the others are right, you will have to play with your 
> SHMMAX and 
> > SHMMIN settings to achieve these recommended settings.
> >
> > Hope this helps,
> > Kevin
> >
> 
> Thanks Kevin, this list is a tremendous resource.
> 
> cheers,
> 
> Dylan
> 
> >
> > Phone: (250) 383-3022
> > Email: kneufeld at refractions.net
> >
> > 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/011
> > >539.
> > >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_use
> > >r/ke
> > >rnel-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
> > >
> > > --
> > > Dylan Beaudette
> > > Soils and Biogeochemistry Graduate Group
> > > University of California at Davis
> > > 530.754.7341
> > >
> > > _______________________________________________
> > > postgis-users mailing list postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> 
> -- 
> Dylan Beaudette
> Soil Resource Laboratory http://casoilresource.lawr.ucdavis.edu/
> University of California at Davis
> 530.754.7341
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 