Thread: killing idle postgres instances
Hi, everyone, I've noticed a somewhat annoying problem while using a java program to do some periodic work on some postgresql tables. We have a java program scheduled to run a few times in an hour and the server backend keeps running when the java program exits. This leads to a leak behaviour: each backend instance eats some resources (mainly memory, since the connection is idle) and, in a few hours, our server starts swapping and begins to spiral down. Is there any way to prevent this behaviour? I guess there's a postgres option to limit the time that a statement can run, but we rather prefer another, less traumatic solution like automagically killing idle connections. Can this be configured on the server side or should we start doing some hack shell scripts to do it? Thanks a lot.
"Marcus Andree S. Magalhaes" <marcus.magalhaes@vlinfo.com.br> writes:
> We have a java program scheduled to run a few times in an hour and
> the server backend keeps running when the java program exits.
That means you're not closing the connection. Fix your client.
regards, tom lane
Thanks, Tom, but the connections are closed by a finally clause. And, besides, when the vm exits, all tcp/ip connections should be closed... > "Marcus Andree S. Magalhaes" <marcus.magalhaes@vlinfo.com.br> writes: >> We have a java program scheduled to run a few times in an hour and the >> server backend keeps running when the java program exits. > > That means you're not closing the connection. Fix your client. > > regards, tom lane
Marcus, Can we see the code, sometimes a second set of eyes helps. Dave On Thu, 2004-02-05 at 17:56, Marcus Andree S. Magalhaes wrote: > Thanks, Tom, > > but the connections are closed by a finally clause. > And, besides, when the vm exits, all tcp/ip connections should be closed... > > > > "Marcus Andree S. Magalhaes" <marcus.magalhaes@vlinfo.com.br> writes: > >> We have a java program scheduled to run a few times in an hour and the > >> server backend keeps running when the java program exits. > > > > That means you're not closing the connection. Fix your client. > > > > regards, tom lane > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Dave Cramer 519 939 0336 ICQ # 14675561
I'll clean it up a bit and make an as-similar-as-possible tomorrow morning. It's a relatively large package with lots of classes and internal calls but 90% of the code has nothing to do with the database. > Marcus, > > Can we see the code, sometimes a second set of eyes helps. > > Dave > On Thu, 2004-02-05 at 17:56, Marcus Andree S. Magalhaes wrote: >> Thanks, Tom, >> >> but the connections are closed by a finally clause. >> And, besides, when the vm exits, all tcp/ip connections should be >> closed... >> >> >> > "Marcus Andree S. Magalhaes" <marcus.magalhaes@vlinfo.com.br> >> writes: >> >> We have a java program scheduled to run a few times in an hour and >> the server backend keeps running when the java program exits. >> > >> > That means you're not closing the connection. Fix your client. >> > >> > regards, tom lane >> >> >> >> >> ---------------------------(end of >> broadcast)--------------------------- TIP 5: Have you checked our >> extensive FAQ? >> >> http://www.postgresql.org/docs/faqs/FAQ.html >> > -- > Dave Cramer > 519 939 0336 > ICQ # 14675561
On Thu, Feb 05, 2004 at 08:56:15PM -0200, Marcus Andree S. Magalhaes wrote:
>
> Thanks, Tom,
>
> but the connections are closed by a finally clause.
If you have idle connections, then necissarily, that's not happening.
> And, besides, when the vm exits, all tcp/ip connections should be closed...
I've definitely seen cases where this is not the case; they're
closed, but not properly. Eventually PostgreSQL will notice that the
client is gone and shut the connection down, but that will take some
time (like 2 hours).
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner