<!DOCTYPE html>
<html data-lt-installed="true">
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body style="padding-bottom: 1px;">
<p>Hi,</p>
<p>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).</p>
<p>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).</p>
<p>Here the PG12 configuration:</p>
<p>max_connections = 550<br>
shared_buffers = 32256MB<br>
effective_cache_size = 96768MB<br>
maintenance_work_mem = 2GB<br>
checkpoint_completion_target = 0.9<br>
wal_buffers = 16MB<br>
default_statistics_target = 100<br>
random_page_cost = 4<br>
effective_io_concurrency = 2<br>
work_mem = 23592kB<br>
min_wal_size = 1GB<br>
max_wal_size = 4GB<br>
max_worker_processes = 32<br>
max_parallel_workers_per_gather = 4<br>
max_parallel_workers = 32<br>
max_parallel_maintenance_workers = 4<br>
# pghero<br>
shared_preload_libraries = 'pg_stat_statements'<br>
pg_stat_statements.track = all<br>
pg_stat_statements.max = 10000<br>
track_activity_query_size = 2048<br>
# repmgr<br>
archive_mode = on<br>
archive_command = '/bin/true'<br>
wal_log_hints = on # pg_rewind<br>
wal_keep_segments = 4<br>
</p>
<p><br>
</p>
<p>And the PgPool2 configuration:</p>
<p>backend_clustering_mode = 'streaming_replication'<br>
listen_addresses = '10.3.99.42'<br>
pcp_listen_addresses = '10.3.99.42'</p>
<p>enable_pool_hba = on<br>
num_init_children = 55<br>
max_pool = 10</p>
<p>child_max_connections = 500<br>
connection_life_time = 180<br>
sr_check_user = 'repmgr'<br>
sr_check_password = ''<br>
health_check_user = 'postgres'<br>
hostname0 = ''<br>
backend_hostname0 = '10.3.99.8'<br>
backend_port0 = 5432<br>
backend_weight0 = 0.5<br>
backend_application_name0 = 'riper'<br>
backend_hostname1 = '10.3.99.5'<br>
backend_port1 = 5432<br>
backend_application_name1 = 'palourde'<br>
backend_weight1 = 0.3<br>
backend_hostname2 = '10.3.99.9'<br>
backend_port2 = 5434<br>
backend_application_name2 = 'flamboyante'</p>
<p><br>
</p>
<p>I'd be happy to follow any pointer you could suggest being able
to use PGPool correctly.</p>
<p>Thanks by advance,</p>
<p>Thomas<br>
</p>
</body>
<lt-container></lt-container>
</html>