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

[Tatsuo Ishii]

> On 12/09/2011 06:55 AM, Tatsuo Ishii wrote:
>>> Now, is there a way to tell pgpool to stop accepting connections after
>>> certain limit? I need pgpool to reject connections.
>> 
>> Pgpool does something like this when setting up socket to accept
>> connections from pgpool clients.
>> 
>> 	backlog = pool_config->num_init_children * 2;
>> 	status = listen(fd, backlog);
>> 
>> So I guess it is up to OSs behavior when pgpool set backlog parameter
>> of listen(2). I would like to know if there is any better way to
>> control listen queue.
> 
> I will play with that a bit, and let you know my results. I was running
> this on a debian Linux inside virtualbox. Anyhow, we'll see.
> 
>>> Also, I'm not sure what should I set max_pool to. If I allow 1000
>>> connections to pgpool, and set max_pool to 5, I actually need to allow
>>> postgres to accept 5000 connections to be sure that everything will be
>>> working ok. And then when that 1000 connections disconnect from pgpool,
>>> pgpool will still maintain 5000 connections to the postgres backend, no
>>> matter that the most I'll be able to utilize is 1/5 of those.
>>>
>>> Am I missing something here?
>> 
>> First of all, you can set max_pool lower than 5 (say 2) even if you
>> have 5 different users. In this case if 3rd user is comming, the
>> oldest connection to backend is disconnected and new connection is
>> establised for the user. So functionaly you will have no problem, the
>> pernalty is establishing connection overhead.
>> 
>> Also pgpool has following directives to control idle connections to
>> backend.
>> 
>> connection_life_time = 0
>>                                    # Connection to backend closes after being idle for this many seconds
>>                                    # 0 means no close
> 
> 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.

> Btw, just one clarification, what is a child?

Preforked child process of pgpool. It's similar to Apache's worker
proccess.

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

> 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.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp