[postgis-users] postgis tuning parameters
dylan.beaudette at gmail.com
Tue Oct 9 13:11:54 PDT 2007
On Thursday 27 September 2007, Kevin Neufeld wrote:
> Hi Dylan,
> As a developer on a stand-alone postgresql box, I have the following
> 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,
Thanks Kevin, this list is a tremendous resource.
> 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/011539.
> > 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/ke
> > 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
Soil Resource Laboratory
University of California at Davis
More information about the postgis-users