[pgpool-general: 9027] Load balancing failure

[Thomas Lecavelier]

Hi,

I have a relatively "big" (220Gb on disk, half a dozen of tables with 
tens of millions tuples each) PG 12 in production, on an honest server 
(40 cores 2,6GHz, 189Gb RAM). The website started to have hard time 
handling the customer peaks, so I wanted to exploit the streaming 
replica via PGPool2 (v4.3) running on a twin of the primary server 
(streaming replication maintained by repmgr).

I'm pretty sure I missed something in the documentation, because from 
the moment I switch to PGPool, performances break, replica got delayed a 
lot, then PgPool consider the replica offline. Meanwhile, I see only 14 
active PGPool processes, the others being "Ready to accept connection", 
while I can't connect to PgPool (to be more precise: the psql command 
run, but hangs before getting the prompt).

Here the PG12 configuration:

max_connections = 550
shared_buffers = 32256MB
effective_cache_size = 96768MB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 23592kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 32
max_parallel_workers_per_gather = 4
max_parallel_workers = 32
max_parallel_maintenance_workers = 4
# pghero
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_activity_query_size = 2048
# repmgr
archive_mode = on
archive_command = '/bin/true'
wal_log_hints = on # pg_rewind
wal_keep_segments = 4


And the PgPool2 configuration:

backend_clustering_mode = 'streaming_replication'
listen_addresses = '10.3.99.42'
pcp_listen_addresses = '10.3.99.42'

enable_pool_hba = on
num_init_children = 55
max_pool = 10

child_max_connections = 500
connection_life_time = 180
sr_check_user = 'repmgr'
sr_check_password = ''
health_check_user = 'postgres'
hostname0 = ''
backend_hostname0 = '10.3.99.8'
backend_port0 = 5432
backend_weight0 = 0.5
backend_application_name0 = 'riper'
backend_hostname1 = '10.3.99.5'
backend_port1 = 5432
backend_application_name1 = 'palourde'
backend_weight1 = 0.3
backend_hostname2 = '10.3.99.9'
backend_port2 = 5434
backend_application_name2 = 'flamboyante'


I'd be happy to follow any pointer you could suggest being able to use 
PGPool correctly.

Thanks by advance,

Thomas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240226/64b4999a/attachment.htm>