[pgpool-general: 7939] Re: Pgpool does not accept new connections

[Tatsuo Ishii]

> Hello,
> 
> we have the problem, that Pgpool does not accept new connections when some 
> longer running SQLs are on their way.
> 
> We are using Pgpool 4.2.5 till now only for automated fail over and online 
> recovery - connection pooling, load balancing, in memory query cache are 
> switched off. PostgreSQL streaming replication is used. 
> 
> We can provoke the effect with a SQL like
> 
>         select field_a, field_b, pg_sleep(120)
>           from table_a
>           where field_a = 12345 
> 
> The statement simulates long running statements we are using.
> 
> When we start the above statement 3 times or more with a delay of 1 second 
>  Pgpool does not accept new connections for some minutes. We do not get an 
> error message or time out. Direct connections to Postgres are as fast as 
> always. 
> 
> The effect exists in a 2-node cluster and in a degenerated, single machine 
> cluster too. 

It is possible that all pgpool child process are busy.  You can check
by using pcp_proc_info command. Try:

pcp_proc_info -p [port number specified by pcp_port] -h localhost -v -a|grep Wait

If you get no lines at all, then all pgpool child process are occupied
by the long running queries. In this case you can:

- increase num_init_children (requires restarting pgpool)

- decrease the chance to issue the long running query.

Best reagards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp