RE: DB Getting Slower and Slower and Slower....

Hi Doug,
    Thanks for the suggestions.
    With regards to vacuuming, yes, I do vacuum enough. I even tried vacuuming
right before each query, but it still slows down.
    For the running out of memory part, there seem to be two aspects to it:
1) I tried running the application on a separate machine to make sure that
it was not my code that was eating up all the memory but that did not help.
However, I suspect that my code may be "losing" some connections (i.e.
leaves them dangling somewhere in the ether :-). Do you know of a way to as
Postgres how many open connections it currently has and the client IP's that
opened those connections?
2) Even though the DB gets faster after reboot, I have the impression (no
hard data to support this) that with usage, it gets slower in general. When
I had a clean machine, right after installation, it was lightening-fast.
However, even though the data that is loaded in the DB did not increase in
size all that much, after that it has been used for a while, the DB gets
progressively slower.

    With regards to the JDBC driver that I am using, I am using one that I had
built with v7.0. The reason for this is that I had applied a patch to
correct the mutli-byte support that was not working. Has this problem been
fixed in the new version of the JDBC driver? If so, I would gladly switch.


Thanks,
Othman

-----Original Message-----
From: doug@belphigor.mcnaught.org [mailto:doug@belphigor.mcnaught.org]On
Behalf Of Doug McNaught
Sent: Thursday, May 03, 2001 9:19 PMs
To: Othman Laraki
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] DB Getting Slower and Slower and Slower....


"Othman Laraki" <othman@epitrope.com> writes:

> I recently upgraded to V7.1 version of Postgres on Linux and have
> noticed that as the DB is being used, it gets progressively slower
> and slower, until very simple queries slow down to a crawl.

First question: are you VACUUMing enough?  How active is the DB with
respect to inserts/updates?  You should be doing VACUUM ANALYZE at
least nightly (some people with very busy databases do it every couple
hours).

>                                                             The
> basic setup I have is a set of JSPs and Servlets running on Tomcat,
> that use Java classes which connect to Postgres through a JDBC
> driver and perform various DB operations. But, as I said, the DB
> gets noticeably slower, as if it does not clean up behind itself
> after performing operations and finally runs out of memory... Then,
> the only thing that works is stopping the postmaster and rebooting
> the machine.

Very odd.  You certainly shouldn't have to reboot to reclaim memory;
restarting the postmaster should be enough.

Are you sure the Java app or some other process isn't eating up all
the memory?  I was getting PG backend crashes due to lack of memory
every few days for a while, and finally found that it was my
webserver that was running away with itself...

Also make sure you're using the latest JDBC driver (early 7.1 RPMs had
one that was out of date).

Are you doing connection pooling in your web app?  Or does each
servlet/JSP connect and disconnect on its own?

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan