[pgpool-general: 4263] Re: pgpool doesn't split the connections between the backends

[the Rat]

Hello Tatsuo,

Thanks for your quick answer.
Unfortunately the JDBC connection pool is build in the application, we can only set the number of initial and max connections. Even I had to switch off pgpool pooling as it lead to piling up idle connections on the backends that didn't close, eventually hitting the max. Probably because of the double pooling.

If I set a lower init children value, then this limitation on pgpool will be hit instead of the backend and it's even worse as all new connections stay in this waiting mode for a connection to get free, so the app actually hangs instead of giving an error.

Unfortunately it seems that for the targeted amount of simultaneous users a total of 1000 connections is not sufficient. We've calculated we'll need about 1600. If I reduce the num_init_children it will still connect to every backend so I'll need at least 1600 per backend as well. I wanted to split that between the backends but it seems it's not possible.

If I think about it, it might make sense for pgpool to open a connection to the master and at least one slave, as it doesn't know that this connection will only be used for read queries so it should be prepared to send each statement it gets from this client connection to either a master or a slave. But still isn't there a way to change this behavior to at least not connect to all slaves or to connect only on request to the backed and then disconnect and free the connection.

Best regards






 >-------- Оригинално писмо --------

 >От: Tatsuo Ishii ishii at postgresql.org

 >Относно: Re: [pgpool-general: 4261] pgpool doesn't split the connections
 between the backends

 >До: therat at abv.bg

 >Изпратено на: 25.12.2015 01:10


>  Hello Everyone,  

>   Happy holidays!  Can anyone assist on how to set-up pgpool to distribute connections between the backend database nodes , in other words to split the connection count between them. Currently it seems that every connection to pgpool in turn is a connection to every database backend.  

>   Our setup is:  

>  

>  

>  

>  /----- postgresql - master  pgpool--------postgresql - slave 1  

>  

>  

>  

>  \-----postgresql - slave 2  

>   When I check the connections I get 60 to pgpool from the Tomcat client which pools that much and 60 to each of the database servers.  How can I configure it so I have 

> 20 per backend database node.  

>   As this would be a really heavy loaded system with many DB connections we want to limit the connections to not more than 900 per server, as postgresql advise not to have more than 1000 connections per database, because performance radically drops.  When the system gets under load and the connections reach 900 the backends start to reports "too many clients already", which triggers a "degeneration" of the node, which in pgpool terms means to take out the node from balancing and mark it with status "3".  Isn't there a way to just keep 300 connections per server and a total of 900 from the client to pgpool only?  



I think if you could turn off the connection pooling of Tomcat, you

could achieve the goal (you can set num_init_children below 1000).



Best regards,

--

Tatsuo Ishii

SRA OSS, Inc. Japan

English:  http://www.sraoss.co.jp/index_en.php 

Japanese: http://www.sraoss.co.jp 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20151225/60f40677/attachment.html>