Re: Monitoring postgres slowdowns - Mailing list pgsql-general

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
>