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

[Francesco Mazzi]

There is an application that uses HikariCP for connection pooling, maybe is this the problem? 


Da: "fmazzi" <fmazzi at comune.genova.it> 
A: "valsaraj pv" <valsarajpv at gmail.com> 
Cc: "pgpool-general" <pgpool-general at pgpool.net> 
Inviato: Giovedì, 1 luglio 2021 9:34:48 
Oggetto: [pgpool-general: 7610] Re: Problems with response time 

Response time is checked every 5 minutes from icinga with a connection to db, see attachments with response time graph and alerts. 


Da: "valsaraj pv" <valsarajpv at gmail.com> 
A: "fmazzi" <fmazzi at comune.genova.it> 
Cc: "pgpool-general" <pgpool-general at pgpool.net> 
Inviato: Mercoledì, 30 giugno 2021 17:24:58 
Oggetto: [pgpool-general: 7608] Re: Problems with response time 

Since there is only 2 unique connection set, max_pool can be set minimum 2. 

Would you share how did you check response time? 


On Wed, 30 Jun 2021, 7:19 pm Francesco Mazzi, < [ mailto:fmazzi at comune.genova.it | fmazzi at comune.genova.it ] > wrote: 



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" < [ mailto:fmazzi at comune.genova.it | fmazzi at comune.genova.it ] > 
A: "Bo Peng" < [ mailto:pengbo at sraoss.co.jp | pengbo at sraoss.co.jp ] > 
Cc: "pgpool-general" < [ mailto:pgpool-general at pgpool.net | 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" < [ mailto:pengbo at sraoss.co.jp | pengbo at sraoss.co.jp ] > 
A: "fmazzi" < [ mailto:fmazzi at comune.genova.it | fmazzi at comune.genova.it ] > 
Cc: "pgpool-general" < [ mailto:pgpool-general at pgpool.net | 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 < [ mailto:fmazzi at comune.genova.it | 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 < [ mailto:pengbo at sraoss.co.jp | pengbo at sraoss.co.jp ] > 
SRA OSS, Inc. Japan 
[ http://www.sraoss.co.jp/ | http://www.sraoss.co.jp/ ] 




Click [ https://www.mailcontrol.com/sr/MZbqvYs5QwJvpeaetUwhCQ== | here ] to report this email as spam. 
_______________________________________________ 
pgpool-general mailing list 
[ mailto:pgpool-general at pgpool.net | pgpool-general at pgpool.net ] 
[ http://www.pgpool.net/mailman/listinfo/pgpool-general | http://www.pgpool.net/mailman/listinfo/pgpool-general ] 
_______________________________________________ 
pgpool-general mailing list 
[ mailto:pgpool-general at pgpool.net | pgpool-general at pgpool.net ] 
[ http://www.pgpool.net/mailman/listinfo/pgpool-general | http://www.pgpool.net/mailman/listinfo/pgpool-general ] 




_______________________________________________ 
pgpool-general mailing list 
pgpool-general at pgpool.net 
http://www.pgpool.net/mailman/listinfo/pgpool-general 

_______________________________________________ 
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/20210701/b64ec68b/attachment.htm>