Thread: killing idle postgres instances

killing idle postgres instances

From
"Marcus Andree S. Magalhaes"
Date:
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.



Re: killing idle postgres instances

From
Tom Lane
Date:
"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

Re: killing idle postgres instances

From
"Marcus Andree S. Magalhaes"
Date:
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




Re: killing idle postgres instances

From
Dave Cramer
Date:
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


Re: killing idle postgres instances

From
"Marcus Andree S. Magalhaes"
Date:
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




Re: killing idle postgres instances

From
Andrew Sullivan
Date:
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