Thread: Monitoring postgres slowdowns
Hello, is there any way to "look under the hood" when slowdowns occur? We have a tomcat / postgres site with each app having it's own server. The db machine is a dual CPU / RAID 5 / 2GB RAM box running RedHat Linux 7.1 and Postgres 7.1.3 The two machines are connected via a hub on which no other machines are present (i.e. private link). shmall is set to 805306368 and shmmax is 536870912 We seem to have daily slowdowns, and the only tools I know of are top and ps, which are pretty general and only tell you when something is cranking along. I'd like to better be able to 1) determine if indeed something strange is happening with our postgres install and 2) what it might be. I could find no pointers in the faq. Out user load isn't very heavy (max of 200 users), yet occasionally things just crawl. Looking at the tomcat machine shows most memory free low CPU usage, so all signs point to the DB machine - but how to tell if something's wrong / what exactly it is doing at the moment? It's getting frustrating because when it happens everyone looks at me, and I have no idea how to pinpoint what's happening. (Also, we are doing a nightly vacuum --analyze (we tried doing hourly vacuums on 6 of our update-heavy tables, but that slowed things down too much)) thanks, -Steve
On Monday 17 Jun 2002 11:12 pm, Steve Bacon wrote: > Hello, > is there any way to "look under the hood" when slowdowns occur? We > have a tomcat / postgres site with each app having it's own server. > The db machine is a dual CPU / RAID 5 / 2GB RAM box running RedHat > Linux 7.1 and Postgres 7.1.3 > We seem to have daily slowdowns, and the only tools I know of are top > and ps Try looking at vmstat and iostat to see some more info. What does vmstat say is happening on the DB server when this slowdown occurs? > Out user load isn't very heavy (max of 200 users), yet occasionally > things just crawl. Looking at the tomcat machine shows most memory > free low CPU usage, so all signs point to the DB machine - but how to > tell if something's wrong / what exactly it is doing at the moment? Logging queries might help - it could be locks on competing updates. I take it your application logs don't show any obvious patterns. - Richard Huxton
Hi Steve- We have a very similar environment, so I'll be interested to hear what you learn & if it is clearly connected with the Tomcat/Postgres combo. In our case, we have run into occasional problems that seem to be the result of a long-running query being "abandoned" by a web user. For instance, a wild-card query is submitted that would return zillions of rows, so the user waits about 5 seconds, then gets bored & uses the browser's [stop] or [back] button to refine the search & re-submit it. In this situation it seems that often, the original query still keeps churning along. We're guessing that this is a programming issue & we just need to find a way to make Tomcat signal the back-end properly to halt the query. We've gotten this far in tracking down our problem by watching "top" while initiating the query & then observing that the process doesn't go away or stop using CPU time when the user hits [stop]. You can probably use a similar test to see if you are running into the same problem. Hope this helps. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Steve Bacon > Sent: Monday, June 17, 2002 5:12 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Monitoring postgres slowdowns > > > Hello, > is there any way to "look under the hood" when slowdowns occur? We > have a tomcat / postgres site with each app having it's own server. > The db machine is a dual CPU / RAID 5 / 2GB RAM box running RedHat > Linux 7.1 and Postgres 7.1.3 > The two machines are connected via a hub on which no other machines > are present (i.e. private link). shmall is set to 805306368 and shmmax > is 536870912 > > We seem to have daily slowdowns, and the only tools I know of are top > and ps, which are pretty general and only tell you when something is > cranking along. I'd like to better be able to 1) determine if indeed > something strange is happening with our postgres install and 2) what > it might be. I could find no pointers in the faq. > > Out user load isn't very heavy (max of 200 users), yet occasionally > things just crawl. Looking at the tomcat machine shows most memory > free low CPU usage, so all signs point to the DB machine - but how to > tell if something's wrong / what exactly it is doing at the moment? > It's getting frustrating because when it happens everyone looks at me, > and I have no idea how to pinpoint what's happening. > > (Also, we are doing a nightly vacuum --analyze (we tried doing hourly > vacuums on 6 of our update-heavy tables, but that slowed things down > too much)) > > thanks, > -Steve > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Sounds like you might be swapping out a bit much. If your sort_mem is high, and several people execute a query that needs a lot of sort_mem, you might start swapping since the sortmem setting is a per process setting, not the total for the database. Cranking up shared_buffers is usually ok since it IS a setting that is for the database. Try turning down sort_mem to something small like 256 or something and see if that helps. Keep in mind that 256*8k is still 2 Megs per process, and if all 200 users are using sort mem that's 400 Megs right there. On 17 Jun 2002, Steve Bacon wrote: > Hello, > is there any way to "look under the hood" when slowdowns occur? We > have a tomcat / postgres site with each app having it's own server. > The db machine is a dual CPU / RAID 5 / 2GB RAM box running RedHat > Linux 7.1 and Postgres 7.1.3 > The two machines are connected via a hub on which no other machines > are present (i.e. private link). shmall is set to 805306368 and shmmax > is 536870912 > > We seem to have daily slowdowns, and the only tools I know of are top > and ps, which are pretty general and only tell you when something is > cranking along. I'd like to better be able to 1) determine if indeed > something strange is happening with our postgres install and 2) what > it might be. I could find no pointers in the faq. > > Out user load isn't very heavy (max of 200 users), yet occasionally > things just crawl. Looking at the tomcat machine shows most memory > free low CPU usage, so all signs point to the DB machine - but how to > tell if something's wrong / what exactly it is doing at the moment? > It's getting frustrating because when it happens everyone looks at me, > and I have no idea how to pinpoint what's happening. > > (Also, we are doing a nightly vacuum --analyze (we tried doing hourly > vacuums on 6 of our update-heavy tables, but that slowed things down > too much)) > > thanks, > -Steve > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- "Force has no place where there is need of skill.", "Haste in every business brings failures.", "This is the bitterest pain among men, to have much knowledge but no power." -- Herodotus