[pgpool-general: 2665] Re: Blocking of excessive connections

[Sam Wouters]

Thanks for the answers.
I'll check if we can make some resources available to work on the 
"doable" code.

On 26-03-14 08:12, Tatsuo Ishii wrote:
>> Is it possible to change the behaviour of handling of client connections
>> when more than num_init_children try to connect?
> No.
>
>> We have a number of
>> applications using a drop wizard connection pool to connect to the pgpool-II
>> cluster. In an ideal situation these applications would be sized to the
>> maximum amount of available connection, but as said, this would be an ideal
>> situation. In real life, some of these application try to allocate more
>> connections then available, but instead of a connection error the
>> application pool just waits for the connection to become active. As such,
>> one misconfigured application can hold all the connections of the pool,
>> blocking all other applications.
>>
>> It would be great if we could mimic the standard postgresql rejection
> This behavior can be emulated by setting:
>
> fail_over_on_backend_error = off
> backend_flag0 = 'DISALLOW_TO_FAILOVER'
> health_check_period = 0
>
> and set num_init_children > max_connections.
>
> Unfortunately you cannot use the failover feature of pgpool-II though.
>
>> or
>> even better, put some timeout value for the queued connections.
> This is not possible because the queue is inside the kernel. There's
> no API to control the timeout in the queue.
>
>> For
>> instance, queue a new connection if no available pool processes for x time,
>> reject after x time if no processes are freed to handle the queued
>> connection. As such, the application pool would know that the extra
>> connection didn't succeed and would pool the request to one of the already
>> open connections.
>>
>>   
>>
>> An even better way of coping with this would be to be able to limit the
>> amount of connections per user/database/ip.
> This is doable (with moderate amount of codes).
>
>> I still need to test what
>> happens if you put a user limit in postgresql, for example, if you put a
>> connection limit of 4 for user test on postgresql level, what happens if you
>> try to open 5 connections to the pgpool with some processes left to handle
>> connections. Does it reject the connection or does it queue it?
>>
>>   
>>
>> Regards,
>>
>> Sam
>>
>>   
>>

-- 
Sam Wouters
Ericom Computers
--------------------------------------------------
*Ericom Computers*
Tiensestraat 178
3000 Leuven

Tel : +32 (0) 16 23 77 55
Fax : +32 (0) 16 23 48 05
	Ericom Website <http://www.ericom.be>
*
http://www.ericom.be*




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140326/839c5842/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: ericom-logo-small.jpg
Type: image/jpeg
Size: 20990 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140326/839c5842/attachment-0001.jpg>