[pgpool-general: 100] Re: "Connection Timed Out" on clients connecting to pgpool

[Mario Splivalo]

On 12/17/2011 10:04 AM, Tatsuo Ishii wrote:
>> On 12/15/2011 03:14 PM, Mario Splivalo wrote:
>>> Since then I have lowered the max_pool and increased num_init_children
>>> to 1200, but the 'connection timed out' rate has not declined.
>>
>> Moreover, here is what my pgpool connections look like:
>>
>> root at pool1:~# ps -ef | grep pgpool | cut -c49- | sort | uniq -c
>>       1 grep --color=auto pgpool
>>       1 pgpool: PCP: wait for connection request
>>       1 pgpool: megasite megasite 10.13.3.1(15557) idle in transaction
>>       1 pgpool: megasite megasite 10.13.3.1(15565) SELECT
>>       1 pgpool: megasite megasite 10.13.3.1(15571) idle in transaction
>>       1 pgpool: megasite megasite 10.13.3.1(15580) idle in transaction
>>       1 pgpool: megasite megasite 10.13.3.1(15607) idle in transaction
>>       1 pgpool: megasite megasite 10.13.3.1(15613) idle in transaction
>>       1 pgpool: megasite megasite 10.13.3.1(25865) idle in transaction
>>       1 pgpool: megasite megasite 10.13.3.1(65008) idle in transaction
>>       1 pgpool: megasite megasite 10.13.1.12(46056) idle in transaction
>>       1 pgpool: megasite megasite 10.13.1.12(46135) idle in transaction
>>       1 pgpool: megasite megasite 10.13.1.12(46143) idle in transaction
>>    1189 pgpool: wait for connection request
>>       1 pgpool: worker process
>>       1 su -c /usr/local/pgpool2/bin/pgpool -n
>>       1 /usr/local/pgpool2/bin/pgpool -n
>>
>>
>> The number of active connections change, but there is always plenty of
>> available pgpool childs that should be ready to take the connection.
> 
> Not sure what you try to indicate. Pgpool process in "idle in
> transaction" state are processing clients session, waiting for clients
> to send command to pgpool. So they are not ready to take the
> connection. Only "wait for connection request" process can accept
> clients connection.

Yes, and I had 1189 of them available and I was still getting
'connection timed out' on the client side.

I though that maybe 1200 pgpool childs was too much, so I lowered that
to 600, and now this is the situation:

root at pool1:~# ps -ef | grep pgpool | cut -c49- | sort | uniq -c
      1 grep --color=auto pgpool
      1 pgpool: PCP: wait for connection request
      1 pgpool: megasite megasite 130.13.3.1(38546) SELECT
      1 pgpool: megasite megasite 130.13.3.1(38551) idle
      1 pgpool: megasite megasite 130.13.3.1(38567) idle
      1 pgpool: megasite megasite 130.13.3.1(38569) idle in transaction
      1 pgpool: megasite megasite 130.13.3.1(38584) idle in transaction
      1 pgpool: megasite megasite 10.13.1.12(54756) idle in transaction
      1 pgpool: megasite megasite 10.13.1.12(54722) SELECT
    593 pgpool: wait for connection request
      1 pgpool: worker process
      1 su -c /usr/local/pgpool2/bin/pgpool -n
      1 /usr/local/pgpool2/bin/pgpool -n

Now, at the time that 'ps' with grep/cut/sort/uniq was run I had 600
pgpool childs - 7 of them were 'busy' (idle/idle in transaction/SELECT),
and 593 of them are in 'wait for connection request'). And still I am
getting Connection timed out on the client side. As I've said, those are
really occasional, one/two every 5-10 minutes, but they are there.

	Mario