[Pgpool-general] how to force pgpool to release client connections?

[Marcelo Martins]

Tatsuo, first of all thanks for your time on this.
Please see below
-
Marcelo

On Oct 30, 2008, at 12:02 AM, Tatsuo Ishii wrote:

>> ;) that is my same problem and the only way I have now is by
>> restarting apache ( in your case mongrel I guess)  which then closes
>> the connections which sucks cause it kicks out all clients even the
>> ones not doing anything with pgpool. Apache reload won't work.
>>
>> I have also tried setting client_idle_limit but the problem with that
>> is that will kill not only just idle but also idle in transaction at
>> any time.
>
> Changing the behaviro of client_idle_limit in that it will not kill
> clients in the idle_in_transaction_state is not too hard. Is this what
> you want? Or should we have new switch to keep the current behavior
> (kill idle clients in whatever state?
>

I think that keeping client_idle_limit as is should be done since  
others may have a need for that the way it functions now
The best would be a new switch as you mentioned below



>> it would be nice if there was a variable like that is just picked up
>> during online recovery, so that if online recovery is occurring and
>> there are idle connections or idle in transactions get ready of them.
>
> Ok, what about having new switch:
>
> client_idle_limit_in_recovery
>
> BTW, do we have to kill clients immediately even if they are *not*
> idle in online recovery as Tiago said?

That switch would be awesome ...  In regards to what Tiago said, that  
would indeed be necessary but it really depends on how an app talks to  
pgpool.

For example:
I  haver a script that does ResT calls that apache (the client) sends  
to pgpool right. If I see ResT call activity going on and I need to do  
an online recovery I would most likely do the online recovery during   
a pre-set maintenance window and if I have the script doing ResT calls  
during the online-recovery, too bad because pgpool needs to kill those  
client connections coming from apache into pgpool in order to proceed  
with online recovery.  I'm fine with that.



>
>
>> I tried the patch below on the CVS head but that will take care of
>> other connection issues I guess.
>
> Right.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>
>> Marcelo
>>
>> On Oct 29, 2008, at 11:37 AM, Tiago Macedo wrote:
>>
>>> Hi,
>>>
>>> I'm having a similar issue in the beginning of the 2nd recovery
>>> stage. If I have open connections (in this case ActiveRecord
>>> connections) that never disconnect by themselves, the recovery will
>>> timeout. Is there anyway to force pgpool-II to close those
>>> connections?
>>>
>>> At the moment my client_idle_limit is 0 in order for pgpool and
>>> rails to get along well but I don't think tweaking that value would
>>> actually help if the connection isn't idle.
>>>
>>> I'm not familiar with the pgpool source so I'm not sure what this
>>> patch achieves, does it do what I need and kill the client  
>>> connection?
>>>
>>> Thanks,
>>> Tiago Macedo
>>>
>>> On Thu, Oct 23, 2008 at 2:53 PM, Tatsuo Ishii <ishii at sraoss.co.jp>
>>> wrote:
>>>> Hello,
>>>>
>>>> I have faced with the problem of "zombie" connections that
>>> prevents starting
>>>> the second stage of on-line recovery process.
>>>> The zombie connections are between clients behind the firewall
>>> that are
>>>> unexpectedly disapeared.
>>>> The pgpool sends RESEST after client_idle_limit seconds, but if no
>>> client
>>>> exists on the remote site this connection is waiting in CLOSE-WAIT
>>> state
>>>> till ...
>>>
>>> pgpool does not send RESET request to the client, but to the
>>> backend. So your problem seems to be caused by pgpool calling  
>>> close(2)
>>> the connection to client.
>>>
>>> Can you try included patches which try to avoid the problem by using
>>> shutdown(2)?
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>>
>>> Index: pool_stream.c
>>> ===================================================================
>>> RCS file: /cvsroot/pgpool/pgpool-II/pool_stream.c,v
>>> retrieving revision 1.12
>>> diff -c -r1.12 pool_stream.c
>>> *** pool_stream.c       4 Jul 2008 05:27:30 -0000       1.12
>>> --- pool_stream.c       23 Oct 2008 13:51:26 -0000
>>> ***************
>>> *** 95,101 ****
>>> --- 95,107 ----
>>> */
>>> void pool_close(POOL_CONNECTION *cp)
>>> {
>>> +       /*
>>> +        * shutdown connection to the client so that pgpool is not
>>> blocked
>>> +        */
>>> +       if (!cp->isbackend)
>>> +               shutdown(cp->fd, 1);
>>>       close(cp->fd);
>>> +
>>>       free(cp->wbuf);
>>>       free(cp->hp);
>>>       if (cp->sbuf)
>>>
>>> _______________________________________________
>>> Pgpool-general mailing list
>>> Pgpool-general at pgfoundry.org
>>> http://pgfoundry.org/mailman/listinfo/pgpool-general
>>>
>>>
>>> _______________________________________________
>>> Pgpool-general mailing list
>>> Pgpool-general at pgfoundry.org
>>> http://pgfoundry.org/mailman/listinfo/pgpool-general
>>