[pgpool-general: 7607] Re: Problems with response time

[Francesco Mazzi]

Sorry, I think I wrote wrong things. max pool=4 should be enough because there are only 2 unique couples of user/db 


Da: "fmazzi" <fmazzi at comune.genova.it> 
A: "Bo Peng" <pengbo at sraoss.co.jp> 
Cc: "pgpool-general" <pgpool-general at pgpool.net> 
Inviato: Mercoledì, 30 giugno 2021 15:42:21 
Oggetto: [pgpool-general: 7606] Re: Problems with response time 

Hi, we enabled connections and disconnections log. There are about total 56 connections, 42 of them are from an unique user, half to a db and another half to another db, so I think we need at least 21 connections cached for this application. 
In this moment we have num_init_children=110 and max_pool=4 , should we increase max_pool to be at least 21? 
child_life_time and connection_life_time are set to zero. 
Thank you. 


Da: "Bo Peng" <pengbo at sraoss.co.jp> 
A: "fmazzi" <fmazzi at comune.genova.it> 
Cc: "pgpool-general" <pgpool-general at pgpool.net> 
Inviato: Mercoledì, 30 giugno 2021 5:20:41 
Oggetto: Re: [pgpool-general: 7601] Problems with response time 

Hello, 

On Tue, 29 Jun 2021 15:22:51 +0200 (CEST) 
Francesco Mazzi <fmazzi at comune.genova.it> wrote: 

> Hello, we have two instances of pgpool 4.2.2, one leader and one stand by, in front of a postgresql cluster of 3 nodes (one read/write and two read only), version 12. 
> We noticed a strange behaviour about connection response time, both with active and standby pgpool: sometimes response time (database connection) suddenly changes from about 0,02s to about 5s and then it comes back to 0,02s. CPU load on server remains very low. 
> We raised num_init_children to 110 but nothing changed. In this moment database it isn't very used, so why there are these low response times? 

> We noticed a strange behaviour about connection response time, both with active and standby pgpool: sometimes response time (database connection) suddenly changes from about 0,02s to about 5s and then it comes back to 0,02s. CPU load on server remains very low. 
> We raised num_init_children to 110 but nothing changed. In this moment database it isn't very used, so why there are these low response times? 

The difference in database connection response time may be caused by the effect of connection cache. 
Creating a new connection to PostgreSQL takes longer than reusing the cached connections. 

In the following cases, Pgpool-II creates new connections to PostgreSQL: 

- The connections to the specified database has not been created yet. 
- If the number of cached connections exceeds specified max_pool, 
the oldest connection will be discarded, then Pgpool-II use that slot for a new connection. 
- Duo to the settings of child_life_time, connection_life_time, 
the cached connections are terminated. 

To figure out the cause, you can check pgpool logs if it is a new connection to PostgreSQL. 

-- 
Bo Peng <pengbo at sraoss.co.jp> 
SRA OSS, Inc. Japan 
http://www.sraoss.co.jp/ 




Click [ https://www.mailcontrol.com/sr/R4ZAHTHLbfPGX2PQPOmvUizKrmxxhcEGDEgAcCDkx1JX987gvPlvhoxZFJ1e5_1Cj-ibO5yjqHMoKRuEdgZyEA== | here ] to report this email as spam. 
_______________________________________________ 
pgpool-general mailing list 
pgpool-general at pgpool.net 
http://www.pgpool.net/mailman/listinfo/pgpool-general 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210630/59e3b1ad/attachment.htm>