Thank you scott. We plan on upgrading to Postgres 8.2 very soon. Would it be safe to say I can make my SHARED BUFFER setting 200MB (I have 2GB memory ). The default is 24MB.
Regds, Radhika On 10/10/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > On 10/9/07, Radhika S <[EMAIL PROTECTED]> wrote: > > Hi, > > Along with my previous message (slow postgres), I notice the shared > buffer > > setting for our production database is set to 1000. > > How much higher can I go? I don't know how much my kernel can take? > > > > I am running postgres 7.4.6 on Redhat enterprise 3 server. > > Your kernel can go much much higher. However, 7.4 was not very > efficient at handling large amount of shared_buffers, so the rule of > thumb is to make it big enough to hold your largest working set and > test to see if it's faster or slower. > > Most of the time it will be faster, but sometimes in 7.4 it will be > slower due to the inefficient caching algorithm it used. > > two points: > > * 7.4.18 or so is the latest version in that branch. Updating it is a > simple pg_ctl stop;rpm -Uvh postgresql-7.4.18.rpm;pg_ctl start or > equivalent. Painless and takes a minute or two, and there are actual > factual data eating bugs in 7.4.6. > > * 8.2 (8.3 due out soon) is MUCH faster than 7.4, AND it can handle > much larger shared_buffer settings than 7.4 > > Back to shared_buffer issues. Keep in mind the kernel caches too, and > it pretty good at it. A common school of thought is to give > postgresql about 25% of the memory in the machine for shared_buffers > and let the kernel handle the rest. It's not a hard fast number. I > run about 35% of the memory for shared_buffers on my server, and it > works very well. > > Keep in mind, memory handed over to shared buffers means less memory > for other things, like sorts or kernel buffering / caching, so > TANSTAAFL (There ain't no such thing as a free lunch) is the key word. > > In 7.4, using 25% is often too high a setting for it to handle well, > and the practical useful maximum is usually under 10,000 > shared_buffers, and often closer to 1,000 to 5,000 > -- It is all a matter of perspective. You choose your view by choosing where to stand. --Larry Wall
