On Fri, 2005-07-29 at 08:24, Kailash Vyas wrote: > 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. Generally speaking, you're better off letting your kernel do the majority of CACHING, and letting postgresql do BUFFERING. On a linux box, the kernel will use all spare memory to cache disk accesses automagically. So, assuming postgresql uses up a few hundred megs, after the machine reaches a steady state, the kernel should be using the 1.5 gig or so left over for caching. While the latest versions of postgresql have much improved caching algorithms for its buffers, it still pretty much dumps the buffers when the last backend looking at them disconnects. I.e. it's not a persistent caching system like the kernel cache is. > 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 That's large, but not unreasonable for a machine handling a large dataset. I assume you do have a decent sized dataset (a gig or more) to handle. > what value should i specify for Effective Cache Size. I came across > this article but it is slightly confusing. Effective cache size just tells the query planner about how much memory the OS is using to cache your dataset. Bring the machine up, run lots of queries, and check the cache and buffers with top, and there's your amount. divide by 8k to get the setting for effective cache size.
