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

[Tatsuo Ishii]

> 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?

Yes.

>>> 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.

If you see seldome errors, I think you are getting closer to
performance limit of the system. According to the waiting queue
theory, if the request rate(your application) getting closer to
service rate(pgpool and PostgreSQL), waiting time is getting longer
very quickly and reaches the connect timeout limit(timeout value is
varried depending on platforms. On Linux it is about 200 seconds). So
I guess you need to make application consume lower execution time.

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

You are welcome!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp