[pgpool-general: 846] Re: pgpool just hangs up after reaching "num_init_children" connections

[Tatsuo Ishii]

Pgpool is waiting for some of users disconnects to pgpool. Until
someone disconnects to pgpool, new user cannot connect to pgpool.

See FAQ(http://www.pgpool.net/mediawiki/index.php/FAQ), especially this entry:

	When I run pgbench to test pgpool-II, pgbench hangs. If I directly
	run pgbench against PostgreSQL, it works fine. Why?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hi,
> 
> Our Production is running PostgreSQL 8.2.22 and pgpool-II- 3.1.1.  In our
> case, pgpool is exclusively used for connection pooling purpose and we're
> not using other features of pgpool like replication, load balance, parallel
> query, etc.
> 
> Recently, in our Production server, number of "concurrent database access
> and/or users" in the application has reached more than "num_init_children"
> defined in pgpool, that is more than 450 in my case.  Once it has reached
> "num_init_children" connections, the application has totally stopped
> responding.  pgpool has just hung up and was not able to release any
> existing connection and distribute new connection.  But at "this same"
> moment, I could able to login to database directly on 5432 port and when I
> tried to get a list of running queries using: 
> 
> select date_trunc('second', current_timestamp - query_start) as runtime,
> datname as database_name, usename as user_name, current_query from
> pg_stat_activity where current_query != '<IDLE>' order by 1 desc;
> 
> I don't see any queries being held up running for a very long time (I can
> even say, there were only 4 rows returned) .  But eventually, we ended up
> restarting pgpool.
> 
> NOTE: We've only one user connecting to the database from our application,
> so "max_pool" is set to '1'.
> 
> On running 'ps -ef |grep pgpool', I'm finding *all* the 450 processes are
> showing up in "idle" as given below:
> 
> 		myuser    8947  2363  0 17:16 ?        00:00:00 pgpool:
> myuser mydb 127.0.0.1(45877) idle
> 
> My questions are:
> 	1) Why pgpool just hangs up/freezes after reaching
> "num_init_children" connections?
> 	2) In order to set the right value for "num_init_children", we want
> to find out the maximum connection reached at any given timeframe from
> pgpool log, in 	   case if we decide to increase "num_init_children" value.
> Is it possible to determine this information from pgpool?
> 	   We even tried out setting "debug_level" to '1' but no info/clues
> here.
> 
> Important pgpool parameters:
> # - Pool size -
> num_init_children = 450
> max_pool = 1
> 
> # - Life time -
> child_life_time = 300
> child_max_connections = 20
> connection_life_time = 120
> client_idle_limit = 0
> 
> Regards,
> Gnanam
> 
> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general