High concurrency on PgPool + PostgreSQL Cluster

Question

I have a PgPool II cluster with 2 PostreSQL 9.5 backends (4vCores, 8gb ram) doing loadbalacing+replication. My use case is a website that provides just SSO-login/Registration, is a relative small database, the queries are very simple but it needs to support very high concurrency (several thousand concurrent users).

Before adding more backends, i want to make sure that the configuration of the current Cluster is optimal. Ran some test with pgbench (regular SELECT queries simulating the normal behaviour of the website) and i was able to overload the connection pool without too much effort (pgbench -c 64 -j 4 -t 1000 -f queries.sql) even when there was plenty of CPU/RAM available in the LB and the backends.

This are the relevant settings:

pgPool II

listen_backlog_multiplier = 3
connection_cache = on
num_init_children = 62
max_pool = 4
child_life_time = 0
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0

PostgreSQL

max_connections = 256
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 8MB
maintenance_work_mem = 512MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

Increasing the num_init_children/max_pool will force me to increase the max_connections on the backends, and that doesn't seems to be recommended. Any suggestions? Thanks!

Answer 1

There is no way to achieve more concurrent connections through Pgpool-II than the value of num_init_children.

num_init_children in Pgpool-II directly corresponds to the maximum number of concurrent client connections Pgpool-II can handle, So you can not set the num_init_children value lower than the maximum concurrent connection you want to support.

But to save the max_connections on PG side, you can use the lower value for max_pool config. As a Pgpool-II child process opens a new backend connection only if the requested [user,database] pair is not already in the cache. And if the application uses only one user to connect to only one database, Say [user1,db1], then you can set max_pool to 1, and can have max_connection in PG backend equal to (num_init_children +1).