Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 3 at 14:52

This question came from our site for professional and enthusiast programmers.

    
Don't do that. PostgreSQL won't perform well with more than a few hundred parallel queries. Queue them up instead, that's why you use a connection pooler. –  Craig Ringer Jan 23 at 4:36
    
instead postgre does not queue queries on client and i get error. Client 430 aborted in state 1. Probably the backend died while processing. –  user3134392 Jan 23 at 5:19
1  
You need to configure PgPool so it queues queries, and you don't exceed the server's max_connections. –  Craig Ringer Jan 23 at 5:21

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.