From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: Kishore B <kishorebh@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need help in setting optimal configuration for a huge database.
Date: Sat, 22 Oct 2005 23:57:49 +0200
Message-ID: <20051022215749.GB22328@samfundet.no> (raw)
In-Reply-To: <42567e060510221449j636538c7sae78a4d05fc77760@mail.gmail.com>
References: <42567e060510221415r2516523l4c3f9b42dee91a09@mail.gmail.com>
<20051022214259.GA22328@samfundet.no>
<42567e060510221449j636538c7sae78a4d05fc77760@mail.gmail.com>
[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. (700k
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgunderson@bigfoot.com, kishorebh@gmail.com
Subject: Re: Need help in setting optimal configuration for a huge database.
In-Reply-To: <20051022215749.GB22328@samfundet.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox