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: [EMAIL PROTECTED] > > 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/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 > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected] > 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
