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

"Othman Laraki" <othman(at)epitrope(dot)com> writes:

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

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

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