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

Tatsuo Ishii ishii at postgresql.org
Sat Dec 26 00:26:48 JST 2015


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

Then there's no hope to solve your problem. Sorry.

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

The idea behind pgpool's connection pooling is, in most web
applications "simultaneous users" is not actually simultaneous: web
applications just need to connect to pgppool (or DB) util draw a web
page. So by multiplexing user connections will a lot reduce the number
of connections to DB. On the other hand, if your web app have their
own connection pooling, they need to keep on connecting to pgpool,
which prevents the multiplexing.

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

Why does it matter? Your concern is, number of connections to each DB,
not the total connections to DBs, right?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

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


More information about the pgpool-general mailing list