DB Getting Slower and Slower and Slower....

DB Getting Slower and Slower and Slower....

Post by Othman Larak » Sat, 05 May 2001 13:07:35



Hi,
        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. 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.

        If you have any ideas of what may cause this or what I can do to explore
the issue, I would be very grateful. I am fairly new to Postgres, so any
suggestions are welcome.

Thanks,
Othman

_____________________________
Othman Laraki
Epitrope Corporation
860 Hillview Court, Suite 200
Milpitas, CA 95035, USA
650-814-9580 (phone)
650-745-2449 (fax)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

DB Getting Slower and Slower and Slower....

Post by Mitch Vincen » Sat, 05 May 2001 13:27:26


Do you VACUUM and VACUUM ANALYZE regularly?

-Mitch

----- Original Message -----


Sent: Thursday, May 03, 2001 11:59 PM
Subject: DB Getting Slower and Slower and Slower....

> Hi,
> 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. 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.

> If you have any ideas of what may cause this or what I can do to explore
> the issue, I would be very grateful. I am fairly new to Postgres, so any
> suggestions are welcome.

> Thanks,
> Othman

> _____________________________
> Othman Laraki
> Epitrope Corporation
> 860 Hillview Court, Suite 200
> Milpitas, CA 95035, USA
> 650-814-9580 (phone)
> 650-745-2449 (fax)

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

DB Getting Slower and Slower and Slower....

Post by Othman Larak » Sat, 05 May 2001 13:28:42


Yup
-----Original Message-----

Sent: Thursday, May 03, 2001 8:59 PM

Subject: Re: DB Getting Slower and Slower and Slower....

Do you VACUUM and VACUUM ANALYZE regularly?

-Mitch
----- Original Message -----


Sent: Thursday, May 03, 2001 11:59 PM
Subject: DB Getting Slower and Slower and Slower....

> Hi,
> 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. 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.

> If you have any ideas of what may cause this or what I can do to explore
> the issue, I would be very grateful. I am fairly new to Postgres, so any
> suggestions are welcome.

> Thanks,
> Othman

> _____________________________
> Othman Laraki
> Epitrope Corporation
> 860 Hillview Court, Suite 200
> Milpitas, CA 95035, USA
> 650-814-9580 (phone)
> 650-745-2449 (fax)

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

DB Getting Slower and Slower and Slower....

Post by Doug McNaug » Sat, 05 May 2001 13:47:20



> 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).

Quote:>                                                             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 *d up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

DB Getting Slower and Slower and Slower....

Post by Gilles DARO » Sat, 05 May 2001 17:26:48


Hi,

We have an application running the same Java-tomcat on Apache running
on Oracle DB. We have a max of 3 users per second connection on it and
you now what, we have to restart the Apache daemon at 12 and 22 hours
each days.

For me the problem is not relative to the database but only to Java. Take
a look at your memory you will see that it goes were we don't know.

So now we don't use Java anymore. Perl and Mod_perl as the same perf
and it's stable.

Regards

Gilles DAROLD


> Do you VACUUM and VACUUM ANALYZE regularly?

> -Mitch
> ----- Original Message -----


> Sent: Thursday, May 03, 2001 11:59 PM
> Subject: DB Getting Slower and Slower and Slower....

> > Hi,
> > 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. 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.

> > If you have any ideas of what may cause this or what I can do to explore
> > the issue, I would be very grateful. I am fairly new to Postgres, so any
> > suggestions are welcome.

> > Thanks,
> > Othman

> > _____________________________
> > Othman Laraki
> > Epitrope Corporation
> > 860 Hillview Court, Suite 200
> > Milpitas, CA 95035, USA
> > 650-814-9580 (phone)
> > 650-745-2449 (fax)

> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster

> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?

> http://www.postgresql.org/users-lounge/docs/faq.html

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly
 
 
 

DB Getting Slower and Slower and Slower....

Post by Justin Cli » Sat, 05 May 2001 22:43:41


Hi Guys,

I don't know if this has any bearing, but I remember someone recently
saying that Sun's JDK 1.3 has a memory leak bug in one of it's releases,
and our JDBC driver is affected by that.

Not sure of it's present status though.

Regards and best wishes,

Justin Clift



> > 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 *d up my mind,
> Now people just get uglier, and I got no sense of time...          --Dylan

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi

---------------------------(end of broadcast)---------------------------

 
 
 

DB Getting Slower and Slower and Slower....

Post by Tony Gra » Sat, 05 May 2001 23:42:45



Quote:> I don't know if this has any bearing, but I remember someone recently
> saying that Sun's JDK 1.3 has a memory leak bug in one of it's releases,
> and our JDBC driver is affected by that.

JDK1.3.0_2 fixed that for me. When I moved from 7.0.3 to 7.1RC4 I
changed the JDK. Read the JDK changelogs they mention something

Cheers

Tony Grant

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

 
 
 

DB Getting Slower and Slower and Slower....

Post by Steve Wampl » Sun, 06 May 2001 00:18:52




> > I don't know if this has any bearing, but I remember someone recently
> > saying that Sun's JDK 1.3 has a memory leak bug in one of it's releases,
> > and our JDBC driver is affected by that.

> JDK1.3.0_2 fixed that for me. When I moved from 7.0.3 to 7.1RC4 I
> changed the JDK. Read the JDK changelogs they mention something

I haven't seen a problem since switching from jdk1.3.0 to jdk1.3.1rc2,
either.

--
Steve Wampler-  SOLIS Project, National Solar Observatory

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

DB Getting Slower and Slower and Slower....

Post by Othman Larak » Sun, 06 May 2001 05:05:47


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-----

Behalf Of Doug McNaught
Sent: Thursday, May 03, 2001 9:19 PMs
To: Othman Laraki

Subject: Re: [GENERAL] DB Getting Slower and Slower and Slower....


> 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 *d up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

DB Getting Slower and Slower and Slower....

Post by Doug McNaug » Sun, 06 May 2001 06:05:39



>    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?

I don't think PG has any direct way to find this out.  What you can
do is (1) count the number of "postgres" processes running (each
client connection has a backend process) and (2) use "netstat" and
"lsof" to find the IP addresses for the established socket
connections.  It would be possible to automate this procedure but
somewhat tricky.  ;)

Bruce Momjian wrote a fairly simple PG monitor program that you night
want to play with--I think it's in contrib/.

Quote:> 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.

This may be an effect of not VACUUMing often enough, if you are doing
a lot of updates.  Hard to say much without real numbers.  If you are
leaking memory/backends, the slowdown may result from your system
starting to swap.  You might consider running 'vmstat' and collecting
the output, to see if swap i/o starts going up after a while.

Quote:> 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.

I don't know; you'll have to look at the code or the Changelog to see.

-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 *d up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

DB Getting Slower and Slower and Slower....

Post by Tatsuo Ish » Sun, 06 May 2001 10:09:34


Quote:>    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.

7.1 JDBC driver seems much better than 7.0 with the regard of the
multi-byte handling. It understands the database encoding and does
automatic encoding convertion to/from Unicode.
--
Tatsuo Ishii

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

 
 
 

DB Getting Slower and Slower and Slower....

Post by Othman Larak » Tue, 08 May 2001 23:18:27


Hi,
        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. 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.

        If you have any ideas of what may cause this or what I can do to explore
the issue, I would be very grateful. I am fairly new to Postgres, so any
suggestions are welcome.

Thanks,
Othman

_____________________________
Othman Laraki
Epitrope Corporation
860 Hillview Court, Suite 200
Milpitas, CA 95035, USA
650-814-9580 (phone)
650-745-2449 (fax)

---------------------------(end of broadcast)---------------------------

 
 
 

DB Getting Slower and Slower and Slower....

Post by Einar Karttun » Tue, 08 May 2001 23:57:35



> Hi,
>    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. 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.

>    If you have any ideas of what may cause this or what I can do to explore
> the issue, I would be very grateful. I am fairly new to Postgres, so any
> suggestions are welcome.

> Thanks,
> Othman

Are you using VACUUM ANLYZE periodically? Does the postmaster process
bloat? The backend processes (postgres) are created for each connection.
Does their size increase?

- Einar Karttunen

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

DB Getting Slower and Slower and Slower....

Post by feng x » Thu, 10 May 2001 15:46:33


hi,

try to use
    vacuumdb dbname

to clean the database, you may set it as a cron job.

cheers

feng


> Hi,
>         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. 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.

>         If you have any ideas of what may cause this or what I can do to explore
> the issue, I would be very grateful. I am fairly new to Postgres, so any
> suggestions are welcome.

> Thanks,
> Othman

> _____________________________
> Othman Laraki
> Epitrope Corporation
> 860 Hillview Court, Suite 200
> Milpitas, CA 95035, USA
> 650-814-9580 (phone)
> 650-745-2449 (fax)

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

1. Unique Index gets slower and slower and slower for inserts

Hi,

If I have a table with a unique index/constraint on a GUID column (which,
includes using it as a primary key), then inserts to the table get progressively
slower and slower as more records are added.

However, if I specify that the index is non-unique, then this slowing effect is
either not noticeable, or not present at all.

Is it normal for the slowing, and is there anything I can do about it, other
than not have the unique constraint on the index?

Cheers,

Steve.


Remove the 'NOSPAM.' to send me e-Mail

2. Can I have one connection with dif.transactions

3. Slow, slow, slow search on VB6, ADO and SS7

4. Management tools

5. Slow, slow, slow DDEs

6. ??Difference between JOINs

7. Slow slow slow from an ASP

8. Calculating elapsed "work" days?

9. Insert into SQL Server slow, slow, slow...

10. Why does my BCP Load run slower, and slower, and slower

11. PDOXWIN5 Slow (slower than slow)

12. why is update statistics getting slower and slower?

13. Query getting slower and slower...