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

[Bo Peng]

Hello,

> I disabled connection pooling in HikariCP, but problem persists. Any ideas? 
> Thanks 

You may need to check pgpool and postresql logs
to figure out the cause.
 
> 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 12:29:28 
> Oggetto: [pgpool-general: 7614] Re: Problems with response time 
> 
> 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 
> 
> _______________________________________________ 
> pgpool-general mailing list 
> pgpool-general at pgpool.net 
> http://www.pgpool.net/mailman/listinfo/pgpool-general 


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