Обсуждение: 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" > 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 >
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 >
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: > 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