Message-ID: <43FC7389.4050408@archonet.com>
Date: Wed, 22 Feb 2006 14:22:01 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: "Chethana, Rao (IE10)" <Chethana.Rao@Honeywell.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: --pls reply ASAP
References: 
 <544485BC51F81A41AC1A72CFA690136552384E@AZ18EV805.global.ds.honeywell.com>
In-Reply-To: 
 <544485BC51F81A41AC1A72CFA690136552384E@AZ18EV805.global.ds.honeywell.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Chethana, Rao (IE10) wrote:
> Hello!
> 
> Thank you for responding quickly.  I really need ur help.

Please make sure you cc: the list - I don't read this inbox regularly.

> Sir, here r the answers for ur questions, please do tell me what to do
> next(regarding increasing performance of postgresql),  so that I can
> proceed  further.
> 
> How are you using PostgreSQL?
> We r using 7.4.3 with max of (512*6) around 3000 records.

Max of what are (512*6)? Rows? Tables? Sorry - I don't understand what 
you mean here.

Oh, and upgrade to the latest release of 7.4.x - there are important 
bugfixes.

> How many concurrent users?
> It configures for 100, but we r using 4 or 5 only.
> 
> Mostly updates or small selects or large summary reports?
> Update,delete,insert operations.
> 
> What hardware do you have?
> X86 based, 233 MHz, 256 MB RAM.

Hmm - not blazing fast, but it'll certainly run on that.

> What configuration changes have you made?
> No changes, we've used default settings.

That will need changing. As Gourish suggested in another reply, read the 
notes here:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

You'll want to be careful with the memory settings given that you've 
only got 256MB to play with. Don't allocate too much to PostgreSQL 
itself, let the o.s. cache some files for you.

> Are you having problems with all queries or only some?
> Only some queries, particularly foreign key.

Are you happy that there are indexes on the referring side of the 
foreign key where necessary? The primary keys you reference will have 
indexes on them, the other side will not unless you add them yourself.

> Have you checked the plans for these with EXPLAIN ANALYSE?
> No.

That would be something worth doing then. Find a bad query, run EXPLAIN 
ANALYSE SELECT ... and post a new question with the output and details 
of the tables involved.

> Have you made sure your tables are vacuumed and analysed?
> Yes.

Good. With the limited amount of RAM you have, you'll want to use it as 
efficiently as possible.

-- 
   Richard Huxton
   Archonet Ltd