<HTML><BODY>
<div>Hello Everyone,</div><div><br></div><div>Happy holidays!</div><div>Can anyone assist on how to set-up pgpool to distribute connections between the backend database nodes , in other words to split the connection count between them. Currently it seems that every connection to pgpool in turn is a connection to every database backend.</div><div><br></div><div>Our setup is:</div><div> /----- postgresql - master</div><div>pgpool--------postgresql - slave 1</div><div> \-----postgresql - slave 2</div><div><br></div><div>When I check the connections I get 60 to pgpool from the Tomcat client which pools that much and 60 to each of the database servers.</div><div>How can I configure it so I have 20 per backend database node.</div><div><br></div><div>As this would be a really heavy loaded system with many DB connections we want to limit the connections to not more than 900 per server, as postgresql advise not to have more than 1000 connections per database, because performance radically drops.</div><div>When the system gets under load and the connections reach 900 the backends start to reports "too many clients already", which triggers a "degeneration" of the node, which in pgpool terms means to take out the node from balancing and mark it with status "3".</div><div>Isn't there a way to just keep 300 connections per server and a total of 900 from the client to pgpool only?</div><div><br></div><div>I can send my config if that helps:</div><div><br></div><div><br></div><div>listen_addresses = '0.0.0.0'</div><div>port = 9999</div><div>socket_dir = '/tmp'</div><div>listen_backlog_multiplier = 2</div><div>pcp_listen_addresses = '*'</div><div>pcp_port = 9898</div><div>pcp_socket_dir = '/tmp'</div><div>backend_hostname0 = '192.168.10.38'</div><div>backend_port0 = 5432</div><div>backend_weight0 = 1</div><div>backend_data_directory0 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag0 = 'ALLOW_TO_FAILOVER'</div><div>backend_hostname1 = '192.168.10.40'</div><div>backend_port1 = 5432</div><div>backend_weight1 = 1</div><div>backend_data_directory1 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag1 = 'ALLOW_TO_FAILOVER'</div><div>backend_hostname2 = '192.168.10.41'</div><div>backend_port2 = 5432</div><div>backend_weight2 = 1</div><div>backend_data_directory2 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag2 = 'ALLOW_TO_FAILOVER'</div><div>enable_pool_hba = on</div><div>pool_passwd = 'pool_passwd'</div><div>authentication_timeout = 60</div><div>ssl = on</div><div>ssl_key = './server.key'</div><div>ssl_cert = './server.crt'</div><div>num_init_children = 3000</div><div>max_pool = 2</div><div>child_life_time = 600</div><div>child_max_connections = 0</div><div>connection_life_time = 0</div><div>client_idle_limit = 0</div><div>log_destination = 'stderr syslog'</div><div>log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.</div><div>log_connections = off</div><div>log_hostname = off</div><div>log_statement = off</div><div>log_per_node_statement = off</div><div>log_standby_delay = 'none'</div><div>syslog_facility = 'LOCAL0'</div><div>syslog_ident = 'pgpool'</div><div>debug_level = 1</div><div>log_error_verbosity = verbose # terse, default, or verbose messages</div><div>pid_file_name = '/var/run/pgpool/pgpool.pid'</div><div>logdir = '/var/log/pgpool'</div><div>connection_cache = off</div><div>reset_query_list = 'ABORT; DISCARD ALL'</div><div>replication_mode = off</div><div>replicate_select = off</div><div>insert_lock = on</div><div>lobj_lock_table = ''</div><div>replication_stop_on_mismatch = off</div><div>failover_if_affected_tuples_mismatch = off</div><div>load_balance_mode = on</div><div>ignore_leading_white_space = on</div><div>white_function_list = ''</div><div>black_function_list = 'nextval,setval,nextval,setval'</div><div>database_redirect_preference_list = ''</div><div>app_name_redirect_preference_list = ''</div><div>allow_sql_comments = off</div><div>master_slave_mode = on</div><div>master_slave_sub_mode = 'stream'</div><div>sr_check_period = 0</div><div>sr_check_user = 'postgres'</div><div>sr_check_password = 'hiddenpass'</div><div>delay_threshold = 1000</div><div>follow_master_command = '/usr/local/pgsql/scripts/followmaster.sh %d %h %P %H 9898'</div><div>health_check_period = 5</div><div>health_check_timeout = 20</div><div>health_check_user = 'postgres'</div><div>health_check_password = 'hiddenpass'</div><div>health_check_max_retries = 3</div><div>health_check_retry_delay = 2</div><div>connect_timeout = 10000</div><div>failover_command = '/usr/local/pgsql/scripts/failover.sh %d %H /var/lib/pgsql/9.4/data/mastertrigger.txt'</div><div>failback_command = '/usr/local/pgsql/scripts/failback.sh %d %M'</div><div>fail_over_on_backend_error = off</div><div>search_primary_node_timeout = 10</div><div>recovery_user = 'postgres'</div><div>recovery_password = ''</div><div>recovery_1st_stage_command = ''</div><div>recovery_2nd_stage_command = ''</div><div>recovery_timeout = 90</div><div>client_idle_limit_in_recovery = 0</div><div>use_watchdog = on</div><div>trusted_servers = '192.168.10.43,192.168.10.44'</div><div>ping_path = '/bin'</div><div>wd_hostname = '192.168.10.43'</div><div>wd_port = 9000</div><div>wd_authkey = ''</div><div>delegate_IP = '192.168.10.100'</div><div>ifconfig_path = '/usr/bin'</div><div>if_up_cmd = 'sudo /sbin/ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'</div><div>if_down_cmd = 'sudo /sbin/ifconfig eth0:0 down'</div><div>arping_path = '/usr/bin' # arping command path</div><div>arping_cmd = 'sudo /usr/sbin/arping -I eth0 -U $_IP_$ -w 1'</div><div>clear_memqcache_on_escalation = on</div><div>wd_escalation_command = ''</div><div>wd_lifecheck_method = 'heartbeat'</div><div>wd_interval = 10</div><div>wd_heartbeat_port = 9694</div><div>wd_heartbeat_keepalive = 2</div><div>wd_heartbeat_deadtime = 30</div><div>heartbeat_destination0 = '192.168.10.44'</div><div>heartbeat_destination_port0 = 9694</div><div>heartbeat_device0 = 'eth0'</div><div>wd_life_point = 3</div><div>wd_lifecheck_query = 'SELECT 1'</div><div>wd_lifecheck_dbname = 'template1'</div><div>wd_lifecheck_user = 'postgres'</div><div>wd_lifecheck_password = 'hiddenpass'</div><div>other_pgpool_hostname0 = '192.168.10.44'</div><div>other_pgpool_port0 = 9999</div><div>other_wd_port0 = 9000</div><div>relcache_expire = 0</div><div>relcache_size = 256</div><div>check_temp_table = on</div><div>check_unlogged_table = on</div><div>memory_cache_enabled = off</div><div>memqcache_method = 'shmem'</div><div>memqcache_memcached_host = 'localhost'</div><div>memqcache_memcached_port = 11211</div><div>memqcache_total_size = 67108864</div><div>memqcache_max_num_cache = 1000000</div><div>memqcache_expire = 0</div><div>memqcache_auto_cache_invalidation = on</div><div>memqcache_maxcache = 409600</div><div>memqcache_cache_block_size = 1048576</div><div>memqcache_oiddir = '/var/log/pgpool/oiddir'</div><div>white_memqcache_table_list = ''</div><div>black_memqcache_table_list = ''</div><div><br></div></BODY></HTML>