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
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"
Show quoted text
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 tuningDoes 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
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:
Show quoted text
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 tuningDoes 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?
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.
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:
Show quoted text
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?