[PERFORM] Need help in setting optimal configuration for a huge database.

2005-10-24 Thread Kishore B


Hi All,

I am Kishore doing freelance development of J2EE applications. 

We switched to use Postgresql recently because of the advantages it has over other commercial databases. All went well untill recently, untill we began working on an application that needs to maintain a huge database. 

I am describing the problem we are facing below. Can you please take a look at the case, and help me in configuring the PostgreSQL.

We have only two tables, one of which contains 97% of the data and the other table which contains 2.8% of the data. All other contain only the remaining 0.2% of data and are designed to support these two big tables. Currently we have 9 million of records in the first table and 0.2 million of records in the second table.We need to insert into the bigger table almost for every second , through out the life time. In addition, we receive at least 200,000 records a day at a fixed time.We are facing a critical situation because of the performance of the database. Even a basic query like select count(*) from bigger_table is taking about 4 minutes to return.The following is the system configuration.Database : Postgresql 7.3OS : Redhat LinuxProcessor : Athlon,Memory : 2 GBWe are expecting that at least 200 active connections need to be maintainedthrough out the day.

I am also attaching the configuration file that we are currently using.
Can anyyou please suggest the best configuration to satisfy the above requirements?
Thanks in advance. 

Thank you,
Kishore.

		 Yahoo! FareChase - Search multiple travel sites in one click.

 

 

postgresql.conf
Description: 3963038301-postgresql.conf

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


[PERFORM] Need help in setting optimal configuration for a huge database.

2005-10-22 Thread Kishore B
Hi All,

I am Kishore doing freelance development of J2EE applications. 

We switched to use Postgresql recently because of the advantages it has over other commercial databases. All went well untill recently, untill we began working on an application that needs to maintain a huge database. 


I am describing the problem we are facing below. Can you please take a look at the case, and help me in configuring the PostgreSQL.


We have only two tables, one of which contains 97% of the data and the other table which contains 2.8% of the data. All other contain only the remaining 
0.2% of data and are designed to support these two big tables. Currently we have 9 million of records in the first table and 0.2 million of records in the second table.We need to insert into the bigger table almost for every second , through out the life time. In addition, we receive at least 200,000 records a day at a fixed time.
We are facing a critical situation because of the performance of the database
. Even a basic query like select count(*) from bigger_table is taking about 4 minutes to return.The following is the system configuration.Database 
: Postgresql 7.3OS : Redhat LinuxProcessor : Athlon,Memory : 2 GBWe are expecting that at least 200 active connections need to be maintainedthrough out the day.Can anyyou please suggest the best configuration to satisfy the above requirements?

Thanks in advance. 

Thank you,
Kishore.



postgresql.conf
Description: Binary data

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PERFORM] Need help in setting optimal configuration for a huge database.

2005-10-22 Thread Steinar H. Gunderson
On Sun, Oct 23, 2005 at 02:45:25AM +0530, Kishore B wrote:
 Database *:* Postgresql 7.3

You definitely want to upgrade this if you can.

 Memory : 2 GB

For 2GB of RAM, your effective_cache_size (10) is a bit low (try doubling
it), and sort_mem (2048) is probably a bit too low as well.

/* Steinar */
-- 
Homepage: http://www.sesse.net/


---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Need help in setting optimal configuration for a huge database.

2005-10-22 Thread Tom Lane
Kishore B [EMAIL PROTECTED] writes:
 Even a basic query like select count(*) from bigger_table is
 taking about 4 minutes to return.

You do realize that select count(*) requires a full table scan in
Postgres?  It's never going to be fast.

If that's not where your performance problem really is, you need to
show us some of the actual problem queries.  If it is, you should
rethink why your application needs an exact row count.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [PERFORM] Need help in setting optimal configuration for a huge database.

2005-10-22 Thread Kishore B
Hi Gunderson, 

Can I set the effective_cache_size to 20?
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 makeheavy 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 tothe following configuration parameters?


shared_buffers, (Current Setting : 48000 (375MB))
sort_memory, (Current setting 2048 kb (2MB))
effective_cache_size (Current setting: 10 (1GB))


On 10/23/05, Steinar H. Gunderson [EMAIL PROTECTED] 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. (700krows is not really a "huge database", BTW -- I've seen people here haveseveral 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 20?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 andevery 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 doesnot actually allocate memory./* Steinar */--Homepage: http://www.sesse.net/