[pgpool-general: 9323] Re: pgpool setup throws "Unable to acquire JDBC Connection" Errors

[Tatsuo Ishii]

> I have setup 2 HOT standby's off a Primary PostgreSQL instance,
> 
> then I setup pgpool on another server with a config that points pgpool
> only at the 2 standby's for read only load balancing
> 
> 
> The pgpool config is below, which includes a max_pool of 4 (default)
> and num_init_children = 300
> 
> 
> The backend hot standby's have max_connections = 2000
> 
> 
> 
> ########## pgpool config #############
> 
> #
> # Ansible managed
> #
> backend_clustering_mode = 'streaming_replication'
> listen_addresses = '*'
> port = 5432
> 
> unix_socket_directories = '/run/pgpool-II'
> pcp_socket_dir = '/tmp'
> 
> backend_hostname0 = '***********'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/var/lib/pgsql/14/data'
> backend_flag0 = 'DISALLOW_TO_FAILOVER'
> backend_application_name0 = 'Standby001'
> 
> backend_hostname1 = '************'
> backend_weight1 = 1
> backend_data_directory1 = '/var/lib/pgsql/14/data'
> backend_flag1 = 'DISALLOW_TO_FAILOVER'
> backend_application_name1 = 'primaryData1'
> 
> 
> enable_pool_hba = off
> pool_passwd = ''
> allow_clear_text_frontend_auth = on
> log_destination = 'stderr'
> log_connections = off
> log_disconnections = off
> log_hostname = off
> log_statement = off
> logging_collector = on
> log_directory = '/tmp/log'
> log_filename = 'pgpool-%d.log'
> log_truncate_on_rotation = on
> log_rotation_size = 0
> pid_file_name = '/tmp//pgpool.pid'
> logdir = '/tmp/log'
> load_balance_mode = on
> ignore_leading_white_space = on
> sr_check_period = 10
> sr_check_user = 'postgres'
> sr_check_database = 'postgres'
> health_check_period = 5
> health_check_timeout = 20
> health_check_user = 'postgres'
> health_check_password = ''
> health_check_user0 = 'postgres'
> failover_on_backend_shutdown = on
> 
> num_init_children = 300
> 
> 
> 
> However when the app team runs thier java apps, which include 18 pods
> and each pod can use multiple connections, every pod uses below
> configs (min = 1, max = 50)
> 
> so in theory a max of 900 connections.

Then num_init_children must be higher than 900. Moreover if you change
num_init_children to 900, max_connections must be higher than
num_init_children * max_pool = 900 * 4 = 3600.

> they keep getting these errors, see below
> 
> 
> Thanks in advance for any help

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