pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: --pls reply ASAP
4+ messages / 4 participants
[nested] [flat]
* Re: --pls reply ASAP
@ 2006-02-22 14:22 Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2006-02-22 14:22 UTC (permalink / raw)
To: Chethana, Rao (IE10) <Chethana.Rao@Honeywell.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: --pls reply ASAP
@ 2006-02-22 17:16 Theodore LoScalzo <theodoreloscalzo@mac.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Theodore LoScalzo @ 2006-02-22 17:16 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Chethana, Rao (IE10) <Chethana.Rao@Honeywell.com>; pgsql-performance
I know I am sticking my nose in an area here that I have not been
involved in but
this issue is important to me.
Chethana I have a couple of questions based on what you said you are
using as a
platform. see below :
On Feb 22, 2006, at 8:22 AM, Richard Huxton wrote:
> 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.
What Operating System are you running this on??
How much "other" stuff or applications are you running on the box
Is this a IDE hard drive system?? SCSI?? Bus Speed?? is it a older
server or a pc??
You dont have a large database at all but quick access to the data
that is residing in
the database has a lot to do with how the hardware is configured and
what other programs
are using the limited system resources!
>
> 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
Theodore LoScalzo
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 4+ messages in thread
* pls reply ASAP
@ 2006-04-09 09:27 Chethana, Rao (IE10) <Chethana.Rao@Honeywell.com>
0 siblings, 1 reply; 4+ messages in thread
From: Chethana, Rao (IE10) @ 2006-04-09 09:27 UTC (permalink / raw)
To: theodoreloscalzo@mac.com; Richard Huxton <dev@archonet.com>; pgsql-performance
Hello!
Kindly go through the following ,
I wanted to know whether, the command line arguments(function arguments)
------ $1 $2 $3 -- can be used as in the following , like, ----
CREATE TYPE TT
AS(something,something,.................................................
.................etc..................);
CREATE OR REPLACE FUNCTION f1(varchar,varchar,varchar,varchar) RETURNS
.......................(something).............
BEGIN
SELECT a1,a2,a3,a4,a5,a6
FROM (SELECT * FROM T1, T2......WHERE ............etc... Flag = 0
$1 $2 $3 $4)
ORDER BY ........................
...............................
RETURN NEXT .........;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
NOTE : The values for $1 $2 $3 $4 will be passed when the function
is invoked(called) from the command prompt.
I tried implementing the above, but this type of usage is not
supported , how should use it?
I am converting from (sprintf, "SELECT query stmts (which uses %s %s
%s %s ...... ) to functions.
Any help will be deeply appreciated. Thank you.
Kind regards,
Chethana.
Attachments:
image001.jpg (image/jpeg, 5.5K) download
view image
* Re: pls reply ASAP
@ 2006-04-09 15:34 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Chethana, Rao (IE10) <Chethana.Rao@Honeywell.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rajesh Kumar Mallah @ 2006-04-09 15:34 UTC (permalink / raw)
To: Chethana, Rao (IE10) <Chethana.Rao@honeywell.com>; +Cc: theodoreloscalzo@mac.com, "Richard Huxton" <dev@archonet.com>; pgsql-performance
On 4/9/06, Chethana, Rao (IE10) <Chethana.Rao@honeywell.com> wrote:
>
>
>
>
> Hello!
>
>
>
> Kindly go through the following ,
>
>
>
>
>
> I wanted to know whether, the command line arguments(function arguments)
------ $1 $2 $3 -- can be used as in the following , like, ----
>
>
>
>
>
> CREATE TYPE TT AS(something,something,…………………………………………………………etc………………);
>
> CREATE OR REPLACE FUNCTION f1(varchar,varchar,varchar,varchar) RETURNS
…………………..(something)………….
>
the overall idea expressed is doable.
following are comments
1. you have to put RETURNS setof TT (if you plan to return TT) since you
used RETURN NEXT
2. you have to use SELECT INTO rec in the function where rec is rowtype TT
hope it helps
------- non technical comments
------------------------------------------------------
3. its not a performance question , it shud have been marked more
appropriately to pgsql-sql i think.
4. its not a good etiquette to address email to someone and mark Cc to a
list.
kind regds
mallah.
>
>
> BEGIN
>
> SELECT a1,a2,a3,a4,a5,a6
>
> FROM (SELECT * FROM T1, T2……WHERE …………etc… Flag = 0 $1 $2 $3 $4)
>
> ORDER BY
……………………
>
> ………………………….
>
> RETURN NEXT ………;
>
> END LOOP;
>
> RETURN;
>
> END;
>
> ' LANGUAGE 'plpgsql';
>
>
>
> NOTE : The values for $1 $2 $3 $4 will be passed when the function is
invoked(called) from the command prompt.
>
>
>
> I tried implementing the above, but this type of usage is not supported
, how should use it?
>
>
>
> I am converting from (sprintf, "SELECT query stmts (which uses %s %s
%s %s …… ) to functions.
>
>
>
>
>
> Any help will be deeply appreciated. Thank you.
>
>
>
>
>
> Kind regards,
>
> Chethana.
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-04-09 15:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-22 14:22 Re: --pls reply ASAP Richard Huxton <dev@archonet.com>
2006-02-22 17:16 ` Theodore LoScalzo <theodoreloscalzo@mac.com>
2006-04-09 09:27 pls reply ASAP Chethana, Rao (IE10) <Chethana.Rao@Honeywell.com>
2006-04-09 15:34 ` Re: pls reply ASAP Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
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