<div dir="auto"><div>Since there is only 2 unique connection set, max_pool can be set minimum 2.</div><div dir="auto"><br></div><div dir="auto">Would you share how did you check response time?</div><div dir="auto"><br><br><div class="gmail_quote" dir="auto"><div dir="ltr" class="gmail_attr">On Wed, 30 Jun 2021, 7:19 pm Francesco Mazzi, <<a href="mailto:fmazzi@comune.genova.it">fmazzi@comune.genova.it</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="font-family:arial,helvetica,sans-serif;font-size:12pt;color:#000000"><div>Sorry, I think I wrote wrong things. max pool=4 should be enough because there are only 2 unique couples of user/db</div><div><br></div><hr id="m_1378589737750796296zwchr"><div><b>Da: </b>"fmazzi" <<a href="mailto:fmazzi@comune.genova.it" target="_blank" rel="noreferrer">fmazzi@comune.genova.it</a>><br><b>A: </b>"Bo Peng" <<a href="mailto:pengbo@sraoss.co.jp" target="_blank" rel="noreferrer">pengbo@sraoss.co.jp</a>><br><b>Cc: </b>"pgpool-general" <<a href="mailto:pgpool-general@pgpool.net" target="_blank" rel="noreferrer">pgpool-general@pgpool.net</a>><br><b>Inviato: </b>Mercoledì, 30 giugno 2021 15:42:21<br><b>Oggetto: </b>[pgpool-general: 7606] Re: Problems with response time<br></div><div><br></div><div><div style="font-family:'arial','helvetica',sans-serif;font-size:12pt;color:#000000"><div>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.</div><div>In this moment we have num_init_children=110 and max_pool=4 , should we increase max_pool to be at least 21?</div><div> <span style="color:#000000;font-family:'arial','helvetica',sans-serif;font-size:16px;font-style:normal;font-weight:400;letter-spacing:normal;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;background-color:#ffffff;display:inline!important;float:none">child_life_time and connection_life_time are set to zero.</span></div><div>Thank you.</div><br><hr id="m_1378589737750796296zwchr"><div><b>Da: </b>"Bo Peng" <<a href="mailto:pengbo@sraoss.co.jp" target="_blank" rel="noreferrer">pengbo@sraoss.co.jp</a>><br><b>A: </b>"fmazzi" <<a href="mailto:fmazzi@comune.genova.it" target="_blank" rel="noreferrer">fmazzi@comune.genova.it</a>><br><b>Cc: </b>"pgpool-general" <<a href="mailto:pgpool-general@pgpool.net" target="_blank" rel="noreferrer">pgpool-general@pgpool.net</a>><br><b>Inviato: </b>Mercoledì, 30 giugno 2021 5:20:41<br><b>Oggetto: </b>Re: [pgpool-general: 7601] Problems with response time<br></div><br><div>Hello,<br><br>On Tue, 29 Jun 2021 15:22:51 +0200 (CEST)<br>Francesco Mazzi <<a href="mailto:fmazzi@comune.genova.it" target="_blank" rel="noreferrer">fmazzi@comune.genova.it</a>> wrote:<br><br>> 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. <br>> 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. <br>> 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? <br><br>> 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. <br>> 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? <br><br>The difference in database connection response time may be caused by the effect of connection cache.<br>Creating a new connection to PostgreSQL takes longer than reusing the cached connections.<br><br>In the following cases, Pgpool-II creates new connections to PostgreSQL:<br><br>- The connections to the specified database has not been created yet.<br>- If the number of cached connections exceeds specified max_pool, <br> the oldest connection will be discarded, then Pgpool-II use that slot for a new connection.<br>- Duo to the settings of child_life_time, connection_life_time, <br> the cached connections are terminated.<br><br>To figure out the cause, you can check pgpool logs if it is a new connection to PostgreSQL.<br><br>-- <br>Bo Peng <<a href="mailto:pengbo@sraoss.co.jp" target="_blank" rel="noreferrer">pengbo@sraoss.co.jp</a>><br>SRA OSS, Inc. Japan<br><a href="http://www.sraoss.co.jp/" target="_blank" rel="noreferrer">http://www.sraoss.co.jp/</a><br></div></div><br><br>
<font style="background-color:#ffffff">
<p align="center"><font style="background-color:#ffffff">Click <a href="https://www.mailcontrol.com/sr/R4ZAHTHLbfPGX2PQPOmvUizKrmxxhcEGDEgAcCDkx1JX987gvPlvhoxZFJ1e5_1Cj-ibO5yjqHMoKRuEdgZyEA==" rel="nofollow noopener noreferrer noreferrer" target="_blank">here</a> to report this email as spam.</font></p></font>
<br>_______________________________________________<br>pgpool-general mailing list<br><a href="mailto:pgpool-general@pgpool.net" target="_blank" rel="noreferrer">pgpool-general@pgpool.net</a><br><a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank" rel="noreferrer">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br></div></div></div>_______________________________________________<br>
pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net" target="_blank" rel="noreferrer">pgpool-general@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</blockquote></div></div></div>