[pgpool-general: 85] Re: num_int_children and max_pool

[Mario Splivalo]

On 12/09/2011 05:25 PM, Tatsuo Ishii wrote:
>> Yes, I have that set up to 60, but if I have, let's say, 1000 concurent
>> connections to the pgpool, all the time (let's say I have a pool of
>> webservers that can have a maximum of 1000 workers combined), and those
>> are short-lived, I need to either set num_init_children to 1000 and set
>> max_pool to 1, or set num_init_children to 500 and set max_pool to 2.
>> But there is no way to have pgpool accept 1000 connections, and have
>> only 100 (for instance) of them towards the backend servers?
> 
> Then you should set num_init_children to 100. This gurantees up to 100
> concurrent connections to pgpool. What about rest of 900? It will be
> in listen queue (assuming you set backlog to large enough number). As
> long as one of 100 disconnected to pgpool, one out of 900 will be
> accepted by pgpool.

That 'backlog' is something I need to set in source, right? I don't see
a configuration option for it.

>> I assumed it's a
>> 'frontend' connection, the connection that clients are making to pgpool.
>> Hence the 'num_init_children', which states how many connections can
>> pgpool take.
>>
>> But, then there is 'child_life_time', and description is 'pool exits
>> after being idle for that many seconds'. What is 'pool' in this context?
> 
>  In this context, 'pool' means the child process of pgpool.

Ah, good. thank you. So setting connection_life_time larger than
child_life_time actually makes no sense because child will die sooner
than its connection to the backend will expire?

> 
>> I'm asking all of this because, once we switched to pgpool we're getting
>> a lot of those:
>>
>>  OperationalError: could not connect to server: Connection timed out
>>      Is the server running on host "10.13.3.1" and accepting
>>      TCP/IP connections on port 9999?
>>
>> Pgpool is active, all the time, and I can connect to it always, but
>> these show up in the logs from time to time, and I'm not sure how to
>> hount them down. Before postgres was set up to take 500 connections, and
>> now I've increased that to 1500 so I can have num_init_children set up
>> to 600 and max_pool to 2. But still getting those errors...
> 
> I suspect "Connection timed out" error happens when one of connections
> to pgpool stays in the listen queue too long. Talking about TCP/IP
> level, your apache process sends SYN to pgpool port(9999). Kernel
> queues the request. Eventually one of pgpool child process issues
> accept(2) and take away the request from the queue. But if all of
> pgpool child are busy, the connection request keep on remaining in the
> listen queue. In this case expected SYN+ACK packet will not be
> returned to apache process within certain time, which is resulted in
> "Connection timed out" error. This is very similar symptom when your
> browser failed to connect to a apache server because all apache worker
> are busy. Solution is, keep each connection time (i.e. time taken from
> PQconnectdb and PQfinish, assuming you are using libpq)
> shorter. Probably you need to tune PostgreSQL.

Why do you say I need to tune PostgreSQL? Wouldn't I need to tune the
client library somehow, to allow it for longer timeout?

I'm confused here, because, my application is doing a lot of short
queries that are loadbalanced by pgpool. So, the actual connection never
lasts more then maybe a second. But, in my application logs I doo see
those timeouts. They are not pouring, but seldom occuring.

Btw, thank you very much for the clarification on the internal workings
of the pgpool.

	Mario