X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C01B3DA8AB for ; Sat, 22 Oct 2005 23:05:48 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66422-03 for ; Sun, 23 Oct 2005 02:05:43 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.197]) by svr1.postgresql.org (Postfix) with ESMTP id A61C7D6E34 for ; Sat, 22 Oct 2005 23:05:45 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 8so429197nzo for ; Sat, 22 Oct 2005 19:05:50 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=ALZlQXvFmpEWNZuyjdWin0LN87zVXQFz7KLCczWq2nu7fUk6zlFJbwb2obZjX4RfMnps1kxy7MEzUW343j/6xS2s4l8+B9+36nSlr9xEdkCYIOCvc3cS5XJexdgQiz/uyJMII4sZUv5x1ynnRXfxc6WvSbIVm4dm+UMX0g9DOQo= Received: by 10.36.158.11 with SMTP id g11mr4455843nze; Sat, 22 Oct 2005 19:05:50 -0700 (PDT) Received: by 10.36.61.1 with HTTP; Sat, 22 Oct 2005 19:05:50 -0700 (PDT) Message-ID: <42567e060510221905t54baa7d4u81cb83a8616a5a99@mail.gmail.com> Date: Sun, 23 Oct 2005 07:35:50 +0530 From: Kishore B To: pgsql-performance@postgresql.org Subject: Re: Need help in setting optimal configuration for a huge database. In-Reply-To: <20051022215749.GB22328@samfundet.no> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_22561_26022568.1130033150191" References: <42567e060510221415r2516523l4c3f9b42dee91a09@mail.gmail.com> <20051022214259.GA22328@samfundet.no> <42567e060510221449j636538c7sae78a4d05fc77760@mail.gmail.com> <20051022215749.GB22328@samfundet.no> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.418 required=5 tests=[AWL=-0.111, HTML_20_30=0.504, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/310 X-Sequence-Number: 15078 ------=_Part_22561_26022568.1130033150191 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Gunderson, * Can I set the effective_cache_size to 200000?* * Yes, that should work fine. * Do you mean that I can set the effective_cache_size to 1.5 GB out of 2GB Memory that I have in the current system? Can I set the sort_memory to 3072? We need to generate reports which make heavy use of group by and order by clauses. Based on the 2GB available memory, how do you want me to Please note further that we need to execute upto 10 data centric queries at any instance. Based on these specifications, how do you want me to allocate memory to the following configuration parameters? shared_buffers, (Current Setting : 48000 (375MB)) sort_memory, (Current setting 2048 kb (2MB)) effective_cache_size (Current setting: 100000 (1GB)) On 10/23/05, Steinar H. Gunderson wrote: > > [please send replies to the list, not to me directly] > > On Sun, Oct 23, 2005 at 03:19:39AM +0530, Kishore B wrote: > > *You definitely want to upgrade this if you can. > > > > > Memory : 2 GB > > * > > We can move upto 12 GB if need to be. > > I was referring to your PostgreSQL version, not your RAM. More RAM is > almost > always an improvement, but for your data set, 2GB sounds quite good. (700= k > rows is not really a "huge database", BTW -- I've seen people here have > several billion rows a _day_.) > > > For now, let us set the configuraiton parameters for 2GB. > > I failed to mention earlier, that we have a dedicated server for > database. > > Can I set the effective_cache_size to 200000? > > Yes, that should work fine. > > > Can I set the sort_mem size to 4096? > > This depends a bit on the queries you're running. Remember that for each > and > every sort you do, one of these (measured in 8kB buffers) will get > allocated. > Some tuning of your queries against this would probably be useful. > > > Will the performance suffer, if I set these parameters too high? > > Yes, you can easily run into allocating too much RAM with too high > sort_mem, > which could kill your performance. Overestimating effective_cache_size is > AFAIK not half as bad, though -- it is merely a hint to the planner, it > does > not actually allocate memory. > > /* Steinar */ > -- > Homepage: http://www.sesse.net/ > > ------=_Part_22561_26022568.1130033150191 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi Gunderson,
 
 Can I set the effective_cache_si= ze to 200000?
 Yes, that should work fine.
<= br> 
Do you mean that I can set the effective_cache_size to 1.5 GB out of 2= GB Memory that I have in the current system?
 
Can I set the sort_memory to 3072? We need to generate reports which m= ake heavy use of group by and order by clauses.
 
Based on the 2GB available memory, how do you want me to Please note f= urther that we need to execute upto 10 data centric queries at any instance= . Based on these specifications, how do you  want me to allocate memor= y to the following configuration parameters?
 
shared_buffers, (Current Setting : 48000 (375MB))
sort_memory,    (Current setting 2048 kb (2MB))
effective_cache_size (Current setting: 100000 (1GB))
 


 
On 10/23/05, Steinar H. Gunderson <sgu= nderson@bigfoot.com> wrote:
[please send replies to the list= , not to me directly]

On Sun, Oct 23, 2005 at 03:19:39AM +0530, Kish= ore B wrote:
>  *You definitely want to upgrade this if you can.
>= ;
> > Memory : 2 GB
> *
> We can move upto 12 GB if ne= ed to be.

I was referring to your PostgreSQL version, not your RAM. = More RAM is almost
always an improvement, but for your data set, 2GB sounds quite good. (7= 00k
rows is not really a "huge database", BTW -- I've seen people here h= ave
several billion rows a _day_.)

>  For now, let u= s set the configuraiton parameters for 2GB.
> I failed to mention earlier, that we have a dedicated server for d= atabase.
>  Can I set the effective_cache_size to 200000?
Yes, that should work fine.

> Can I set the sort_mem size t= o 4096?

This depends a bit on the queries you're running. Remember that for= each and
every sort you do, one of these (measured in 8kB buffers) will= get allocated.
Some tuning of your queries against this would probably = be useful.

>   Will the performance suffer, if I set these parame= ters too high?

Yes, you can easily run into allocating too much RAM = with too high sort_mem,
which could kill your performance. Overestimatin= g effective_cache_size is
AFAIK not half as bad, though -- it is merely a hint to the planner, it= does
not actually allocate memory.

/* Steinar */
--
Homepa= ge: http://www.sesse.net/


------=_Part_22561_26022568.1130033150191--