Skip site navigation (1) Skip section navigation (2)

postgres optimization

From: Kailash Vyas <kailash(dot)vyas(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Cc: kailash(dot)vyas(at)gmail(dot)com
Subject: postgres optimization
Date: 2005-07-29 13:24:15
Message-ID: a3cc7917050729062449b122da@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
hi

i am optmizing postgres database and need some help on it.
I currently have a server with 2 Gb RAM.

I have setup the /proc/sys/kernel/shmall and /proc/sys/kernel/shmmax to 
536870912 i.e 512 Mb.
should i change it to 2 Gb and how will it affect the perfomance and will 
there be any downsides to it or is it better to keep it less.

I then changed the shared buffers in config file according to this 
expression specifies in postgres manual

max connections=128
250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or infinity


shared buffer=65277

what value should i specify for Effective Cache Size. I came across this 
article but it is slightly confusing.

"Say there is 1.5GB RAM in your machine, shared buffers are set to 32MB and 
effective cache size is set to 800MB. So if a query needs 700MB of data set, 
PostgreSQL would estimate that all the data required should be available in 
memory and would opt for more aggressive plan in terms of optimization, 
involving heavier index usage and merge joins. But if effective cache is set 
to only 200MB, the query planner is liable to opt for the more I/O efficient 
sequential scan."



thanks,
kailash

Responses

pgsql-admin by date

Next:From: Guido BarosioDate: 2005-07-29 13:33:52
Subject: Re: Speed Up clean_pending.pl?
Previous:From: Simon RiggsDate: 2005-07-29 11:53:41
Subject: Re: Checkpoint segments

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group