Postgres pgpool increase num_init_children

Question

I am trying to tune postgre to allow many concurrent queries from clients as i am checking how many parallel connections with there queries it can handle.
I read A good PgPool II configurataion , but what really can help to handle many concurrent connections with queries is to set up (num_init_children = 1000 , for example 1000 parallel queries )

If i set up num_init_children = 2000 server fails to start.
My pgpool config is:

num_init_children = 1000                        
max_pool = 4     
child_life_time = 120
child_max_connections = 100
connection_life_time = 120
client_idle_limit = 10

How can i increase num_init_children or tune some parameters to allow 2, 5 or 10k parallel queries at the same time, cause it's really frustrating that postgre stucks with more that 1000 queries?

Answer 1

You're increasing num_init_children, but there's no sign you've set the PostgreSQL server backend's max_connections high enough to allow that number of connections from PgPool.

Please see the PgPool-II documentation to learn more about configuring PgPool-II. You might find this article useful.

You really don't want thousands of parallel queries to the backend database. What you need is for PgPool to queue them up and send them a few hundred at a time to PostgreSQL. Otherwise you'll tend to see terrible performance; see number of database connections in the Pg wiki. So you need to limit the number of back-end connections PgPool-II creates, while allowing lots of front-end connections. That means you want a low num_init_children. Personally I'd use something like:

num_init_children = 100                        
max_pool = 1

because, per the documentation:

If more than num_init_children clients try to connect to pgpool-II, they are blocked (not rejected) until a connection to any pgpool-II process is closed.

This is what you want for good performance.

That said, I use PgBouncer for this. I find it much simpler to work with, and it tends to perform very well. It also offers transaction-pooling mode, which can be really useful when you have applications that don't close connections, instead leaving them idle for ages. If you're not using PgPool's cluster features I strongly recommend just using PgBouncer, it's way simpler.