pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance tuning!!!
7+ messages / 6 participants
[nested] [flat]
* Performance tuning!!!
@ 1999-12-27 11:38 Baris ULU <ulu@bornova.ege.edu.tr>
0 siblings, 0 replies; 7+ messages in thread
From: Baris ULU @ 1999-12-27 11:38 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am executing a query from Java and I wait for a long time to
gather the results especially for large tables; but when I execute it
from the psql prompt it gives me a quick result....Can anyone help me if
I can find anything about the performance tuning about the
Postgresql6.5.3? My base classes are the JBCL QueryDataSet class to
execute the queries...I know this side of the question deals with the
Blackdown users bu I gues that the problem is about the backend
process..
THANKS
Baris ULU
Computer Engineer
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Performance tuning!!!
@ 1999-12-28 00:41 omid omoomi <oomoomi@hotmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: omid omoomi @ 1999-12-28 00:41 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm not sure but may be that's because, by using java, all your data process
(from a query statement ) is running on client side. I mean that's more
likely to be a file server architecture. If you make the query run on server
side ( ie, using cgi as php or perl ) and plan a 3 tier architecture then
you will have your time saved enough.
So I don't think tuning the database makes it much different.
Any other comments from the experts?
with regards
omid omoomi
>From: Baris ULU <ulu@bornova.ege.edu.tr>
>To: pgsql-sql@postgreSQL.org
>Subject: [SQL] Performance tuning!!!
>Date: Mon, 27 Dec 1999 13:38:58 +0200
>
> Hi,
> I am executing a query from Java and I wait for a long time to
>gather the results especially for large tables; but when I execute it
>from the psql prompt it gives me a quick result....Can anyone help me if
>I can find anything about the performance tuning about the
>Postgresql6.5.3? My base classes are the JBCL QueryDataSet class to
>execute the queries...I know this side of the question deals with the
>Blackdown users bu I gues that the problem is about the backend
>process..
>
>
>THANKS
>
>Baris ULU
>Computer Engineer
>
>
>************
>
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* performance tuning
@ 2001-10-27 17:58 Sheer El-Showk <sheer@saraf.com>
0 siblings, 1 reply; 7+ messages in thread
From: Sheer El-Showk @ 2001-10-27 17:58 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
Does anyone have a good reference for Postgres performance tuning. The
first thing I'm looking for is a way to determine my bottle-neck. My
system is currently running with 5-6 processes in the runqueue and 100%
cpu utilization (50% in the kernel). I'm guessing I'm cpu-bound but I'd
like to find out why (I'm doing mostly inserts, but I have a lot of
foreign keys). I know postgres has a lot of pg_stat_* tables but I'd like
to learn how to use these and other performance analysis tools.
Thanks,
Sheer
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance tuning
@ 2001-10-27 19:00 Keary Suska <hierophant@pcisys.net>
parent: Sheer El-Showk <sheer@saraf.com>
0 siblings, 2 replies; 7+ messages in thread
From: Keary Suska @ 2001-10-27 19:00 UTC (permalink / raw)
To: PostgreSQL <pgsql-general@postgresql.org>
If I had a guess I would say the problem isn't with postgres, if your system
CPU usage is 50%. I haven't had a system go over 20%, even under extreme
duress. Are you by chance using Linux with a 2.4 kernel? The swap mechanism
in 2.4 was terrible (it may have been fixed by now). What is you memory and
virtual memory performance like? Extensive swapping could be at least
partially responsible.
Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"
> From: Sheer El-Showk <sheer@saraf.com>
> Date: Sat, 27 Oct 2001 13:58:42 -0400 (EDT)
> To: <pgsql-general@postgresql.org>, <pgsql-sql@postgresql.org>
> Subject: [GENERAL] performance tuning
>
> Does anyone have a good reference for Postgres performance tuning. The
> first thing I'm looking for is a way to determine my bottle-neck. My
> system is currently running with 5-6 processes in the runqueue and 100%
> cpu utilization (50% in the kernel). I'm guessing I'm cpu-bound but I'd
> like to find out why (I'm doing mostly inserts, but I have a lot of
> foreign keys). I know postgres has a lot of pg_stat_* tables but I'd like
> to learn how to use these and other performance analysis tools.
>
> Thanks,
> Sheer
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance tuning
@ 2001-10-27 22:57 Sheer El-Showk <sheer@saraf.com>
parent: Keary Suska <hierophant@pcisys.net>
1 sibling, 0 replies; 7+ messages in thread
From: Sheer El-Showk @ 2001-10-27 22:57 UTC (permalink / raw)
To: Keary Suska <hierophant@pcisys.net>; +Cc: PostgreSQL <pgsql-general@postgresql.org>
I have to apologize to the assembled crowd and congratulate the postgres
team at the same time. After several days of posting on lists (this one
primarily) and talking with people on IRC it finally occured to me to
get metrics on a per statement basis in my code (I hadn't thought to do
this because I thought the problem was elsewhere). Examining them showed
several missing indices and also some 'like' queries which didn't use the
index. Adding the indices and running code without the 'like' resulted in
a _huge_ performance improviement. Rather than the factor of 10 speed
decrease I had seen when my database went to 20k record tables and no
concurrency performance improvements, I saw a performance shoot back up.
To be fair (since I was using it as a point of reference before), I would
like to presents some stats.
On my pentium III 1 Ghz with a single IDE disk running both my
multi-threaded app and the database I am getting 300 transactions per
minute (50 queries, 17 inserts).
On a modified version of the app running on oracle were my app is running
on a seperate dual processor box and oracle is running on a four-way sun
box I get only 144 transactions per minute.
This problem really nagged me since I was convinced postgres was better
and really enjoyed its features and interface (Oracle can be such a
pain). I had just never used a large system and was concerned that it
didn't scale ... I'm glad to see that is not a problem.
Thanks,
Sheer
On Sat, 27 Oct 2001, Keary Suska wrote:
> If I had a guess I would say the problem isn't with postgres, if your system
> CPU usage is 50%. I haven't had a system go over 20%, even under extreme
> duress. Are you by chance using Linux with a 2.4 kernel? The swap mechanism
> in 2.4 was terrible (it may have been fixed by now). What is you memory and
> virtual memory performance like? Extensive swapping could be at least
> partially responsible.
>
> Keary Suska
> Esoteritech, Inc.
> "Leveraging Open Source for a better Internet"
>
> > From: Sheer El-Showk <sheer@saraf.com>
> > Date: Sat, 27 Oct 2001 13:58:42 -0400 (EDT)
> > To: <pgsql-general@postgresql.org>, <pgsql-sql@postgresql.org>
> > Subject: [GENERAL] performance tuning
> >
> > Does anyone have a good reference for Postgres performance tuning. The
> > first thing I'm looking for is a way to determine my bottle-neck. My
> > system is currently running with 5-6 processes in the runqueue and 100%
> > cpu utilization (50% in the kernel). I'm guessing I'm cpu-bound but I'd
> > like to find out why (I'm doing mostly inserts, but I have a lot of
> > foreign keys). I know postgres has a lot of pg_stat_* tables but I'd like
> > to learn how to use these and other performance analysis tools.
> >
> > Thanks,
> > Sheer
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance tuning
@ 2001-11-07 23:14 Francisco Reyes <lists@natserv.com>
parent: Keary Suska <hierophant@pcisys.net>
1 sibling, 1 reply; 7+ messages in thread
From: Francisco Reyes @ 2001-11-07 23:14 UTC (permalink / raw)
To: Keary Suska <hierophant@pcisys.net>; +Cc: PostgreSQL <pgsql-general@postgresql.org>
On Sat, 27 Oct 2001, Keary Suska wrote:
> If I had a guess I would say the problem isn't with postgres, if your system
> CPU usage is 50%. I haven't had a system go over 20%, even under extreme
> duress. Are you by chance using Linux with a 2.4 kernel? The swap mechanism
> in 2.4 was terrible (it may have been fixed by now). What is you memory and
> virtual memory performance like? Extensive swapping could be at least
> partially responsible.
>
> Keary Suska
> Esoteritech, Inc.
> "Leveraging Open Source for a better Internet"
coming very late to this thread..
In general what settings can one change to enhance performance. I too have
a machine with high CPU utilization when doing a query. Said query does a
sequential scan.
When I use 'top' to see how much memory is been used by postgresql it is
only using 8MB. This machine is very lightly used so I would like to give
PostgreSQL more memory.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: performance tuning
@ 2001-11-07 23:55 Jason Earl <jason.earl@simplot.com>
parent: Francisco Reyes <lists@natserv.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jason Earl @ 2001-11-07 23:55 UTC (permalink / raw)
To: Francisco Reyes <lists@natserv.com>; +Cc: Keary Suska <hierophant@pcisys.net>; PostgreSQL <pgsql-general@postgresql.org>
Bruce Momjian says it much better than I could:
http://www.ca.postgresql.org/docs/momjian/hw_performance/
You also might take a look at some of your most resource draining
queries with 'explain' in psql.
Jason Earl
Francisco Reyes <lists@natserv.com> writes:
> On Sat, 27 Oct 2001, Keary Suska wrote:
>
> > If I had a guess I would say the problem isn't with postgres, if your system
> > CPU usage is 50%. I haven't had a system go over 20%, even under extreme
> > duress. Are you by chance using Linux with a 2.4 kernel? The swap mechanism
> > in 2.4 was terrible (it may have been fixed by now). What is you memory and
> > virtual memory performance like? Extensive swapping could be at least
> > partially responsible.
> >
> > Keary Suska
> > Esoteritech, Inc.
> > "Leveraging Open Source for a better Internet"
>
> coming very late to this thread..
> In general what settings can one change to enhance performance. I too have
> a machine with high CPU utilization when doing a query. Said query does a
> sequential scan.
>
> When I use 'top' to see how much memory is been used by postgresql it is
> only using 8MB. This machine is very lightly used so I would like to give
> PostgreSQL more memory.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-11-07 23:55 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-27 11:38 Performance tuning!!! Baris ULU <ulu@bornova.ege.edu.tr>
1999-12-28 00:41 Re: [SQL] Performance tuning!!! omid omoomi <oomoomi@hotmail.com>
2001-10-27 17:58 performance tuning Sheer El-Showk <sheer@saraf.com>
2001-10-27 19:00 ` Re: performance tuning Keary Suska <hierophant@pcisys.net>
2001-10-27 22:57 ` Re: performance tuning Sheer El-Showk <sheer@saraf.com>
2001-11-07 23:14 ` Re: performance tuning Francisco Reyes <lists@natserv.com>
2001-11-07 23:55 ` Re: performance tuning Jason Earl <jason.earl@simplot.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