[Pgpool-general] pgpool problems

[Tatsuo Ishii]

> Hi all,
> 
> we are running a large website with postgresql as backend and php as 
> frontend.
> 
> Postgresql is running in version 7.4.6 on a dual-xeon (4GB RAM, Debian 
> woody) with max connections set to 500. We are reaching this limit 
> during peak hours (we use php with pg_pconnect).
> 
> For lowering the overhead managed by postgresql I installed 
> pgpool-2.5.1. I removed -i from postgresql startup, configured pgpool to 
> listen to my main ip:5432 (not localhost nor unix socket) and confirmed 
> that with netstat.
> 
> When letting the webservers connect to the database (with pg_pconnect or 
> pg_connect) everything works fine for some minutes. After around 5 
> minutes ps ax only show idle postgres connections, the load on the 
> server goes down and NOTHING happens at all. The website just stalls. 
> Seems like pgpool just hangs.
> 
> my config looks like this:
> listen_addresses = 'xxx.xxx.xxx.xxx'
> port = 5432
> socket_dir = '/var/run' #/tmp is occupied by pgsql
> backend_host_name = ''
> backend_port = 5432
> backend_socket_dir = '/tmp'
> secondary_backend_host_name = ''
> secondary_backend_port = 0
> num_init_children = 64
> max_pool = 10
> child_life_time = 300
> connection_life_time = 0
> logdir = '/tmp'
> replication_mode = false
> replication_strict = true
> replication_timeout = 5000
> load_balance_mode = false
> weight_master = 0.5
> weight_secondary = 0.5
> replication_stop_on_mismatch = false
> reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
> print_timestamp = true
> master_slave_mode = false
> connection_cache = true
> health_check_timeout = 20
> health_check_period = 0
> health_check_user = 'nobody'
> 
> Any hints?

pg_pconnect will eat up all pgpool child processes(number of them is
64, in your case). If you want to keep using pg_pconnect, you need to
increase num_init_chidren until it exceeds number of possible Apache
child processes (probably this is defined by MaxClients directive in
httpd.conf).

Or changing all pg_pconnect calls to pg_connect should solve your
problem.
--
Tatsuo Ishii