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
Subject: postgres optimization
Date: 2005-07-29 11:34:12
Message-ID: a3cc791705072904344eeeb058@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
hi


I need some information to optimize the postgres database server (7.3.10).
I have 2 Gb RAM on the server.

I have set the /proc/sys/kernel/shmmax and /proc/sys/kernel/shmall to 
536870912 (512 Mb)

should i set the shmmax and shmall to use 2 GB or it is better to keep it 
less.
also how does it affect the performance and stability of system


what would be the ideal shared buffer size and effective cache size to be 
set in conf file.

currently i am using this expression to find out shared buffers parameter

max connections = 128

250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or infinity

shared buffers = 65277 (approx)

also regarding the effective cache size how much should i set that to ?

one of the article suggested this so which option would be more optimized

"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

pgsql-admin by date

Next:From: Simon RiggsDate: 2005-07-29 11:53:41
Subject: Re: Checkpoint segments
Previous:From: Devrim GUNDUZDate: 2005-07-29 11:06:39
Subject: Re: libpq.so.3 compatability problem still there for 8.0.3

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