<html><body><div style="font-family: arial, helvetica, sans-serif; font-size: 12pt; color: #000000"><div>This is the pgpool log when icinga connects and response time is about 5 seconds:</div><div><br data-mce-bogus="1"></div><div><div>Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 26292: LOG: new connection received</div><div>Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 26292: DETAIL: connecting host=192.168.xxx.xxx port=47510</div><div>Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 1556: LOG: child process with pid: 26233 exits with status 256</div><div>Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 1556: LOG: fork a new child process with pid: 26366</div><div>Jul 6 03:44:47 xxxxxxx pgpool: 2021-07-06 03:44:47: pid 26292: LOG: DB node id: 1 backend pid: 16796 statement: SELECT version()</div><div>Jul 6 03:44:47 xxxxxxx pgpool: 2021-07-06 03:44:47: pid 26292: LOG: DB node id: 0 backend pid: 29378 statement: DISCARD ALL</div><div>Jul 6 03:44:47 xxxxxxx pgpool: 2021-07-06 03:44:47: pid 26292: LOG: DB node id: 1 backend pid: 16796 statement: DISCARD ALL</div><div>Jul 6 03:44:47 xxxxxxx pgpool: 2021-07-06 03:44:47: pid 26292: LOG: frontend disconnection: session time: 0:00:05.068 user=xxxxxx database=template1</div><div>host=192.168.xxx.xxx port=47510</div><div><br data-mce-bogus="1"></div><div>This is a icinga connection with fast response time:</div><div><br data-mce-bogus="1"></div></div><div data-marker="__SIG_PRE__"><div><div>Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: LOG: new connection received</div><div>Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: DETAIL: connecting host=192.168.xxx.xxx port=53152</div><div>Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: LOG: DB node id: 1 backend pid: 31761 statement: SELECT version()</div><div>Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: LOG: DB node id: 1 backend pid: 31761 statement: DISCARD ALL</div><div>Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: LOG: DB node id: 2 backend pid: 2980 statement: DISCARD ALL</div><div>Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: LOG: frontend disconnection: session time: 0:00:00.044 user=xxxxxx database=template1</div><div>host=192.168.xxx.xxx port=53152</div><div>Jul 6 03:49:47 xxxxxx pgpool: 2021-07-06 03:49:47: pid 1556: LOG: child process with pid: 26292 exits with status 256</div><div>Jul 6 03:49:47 xxxxxx pgpool: 2021-07-06 03:49:47: pid 1556: LOG: fork a new child process with pid: 26419</div></div><div><br></div></div><div>I don't see any difference.</div><div><br data-mce-bogus="1"></div><hr id="zwchr" data-marker="__DIVIDER__"><div data-marker="__HEADERS__"><b>Da: </b>"Bo Peng" <pengbo@sraoss.co.jp><br><b>A: </b>"fmazzi" <fmazzi@comune.genova.it><br><b>Cc: </b>"pgpool-general" <pgpool-general@pgpool.net><br><b>Inviato: </b>Martedì, 6 luglio 2021 7:59:43<br><b>Oggetto: </b>[pgpool-general: 7633] Re: Problems with response time<br></div><div><br></div><div data-marker="__QUOTED_TEXT__">Hello,<br><br>> I disabled connection pooling in HikariCP, but problem persists. Any ideas? <br>> Thanks <br><br>You may need to check pgpool and postresql logs<br>to figure out the cause.<br> <br>> Da: "fmazzi" <fmazzi@comune.genova.it> <br>> A: "valsaraj pv" <valsarajpv@gmail.com> <br>> Cc: "pgpool-general" <pgpool-general@pgpool.net> <br>> Inviato: Giovedì, 1 luglio 2021 12:29:28 <br>> Oggetto: [pgpool-general: 7614] Re: Problems with response time <br>> <br>> There is an application that uses HikariCP for connection pooling, maybe is this the problem? <br>> <br>> <br>> Da: "fmazzi" <fmazzi@comune.genova.it> <br>> A: "valsaraj pv" <valsarajpv@gmail.com> <br>> Cc: "pgpool-general" <pgpool-general@pgpool.net> <br>> Inviato: Giovedì, 1 luglio 2021 9:34:48 <br>> Oggetto: [pgpool-general: 7610] Re: Problems with response time <br>> <br>> Response time is checked every 5 minutes from icinga with a connection to db, see attachments with response time graph and alerts. <br>> <br>> <br>> Da: "valsaraj pv" <valsarajpv@gmail.com> <br>> A: "fmazzi" <fmazzi@comune.genova.it> <br>> Cc: "pgpool-general" <pgpool-general@pgpool.net> <br>> Inviato: Mercoledì, 30 giugno 2021 17:24:58 <br>> Oggetto: [pgpool-general: 7608] Re: Problems with response time <br>> <br>> Since there is only 2 unique connection set, max_pool can be set minimum 2. <br>> <br>> Would you share how did you check response time? <br>> <br>> <br>> On Wed, 30 Jun 2021, 7:19 pm Francesco Mazzi, < [ mailto:fmazzi@comune.genova.it | fmazzi@comune.genova.it ] > wrote: <br>> <br>> <br>> <br>> Sorry, I think I wrote wrong things. max pool=4 should be enough because there are only 2 unique couples of user/db <br>> <br>> <br>> Da: "fmazzi" < [ mailto:fmazzi@comune.genova.it | fmazzi@comune.genova.it ] > <br>> A: "Bo Peng" < [ mailto:pengbo@sraoss.co.jp | pengbo@sraoss.co.jp ] > <br>> Cc: "pgpool-general" < [ mailto:pgpool-general@pgpool.net | pgpool-general@pgpool.net ] > <br>> Inviato: Mercoledì, 30 giugno 2021 15:42:21 <br>> Oggetto: [pgpool-general: 7606] Re: Problems with response time <br>> <br>> 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. <br>> In this moment we have num_init_children=110 and max_pool=4 , should we increase max_pool to be at least 21? <br>> child_life_time and connection_life_time are set to zero. <br>> Thank you. <br>> <br>> <br>> Da: "Bo Peng" < [ mailto:pengbo@sraoss.co.jp | pengbo@sraoss.co.jp ] > <br>> A: "fmazzi" < [ mailto:fmazzi@comune.genova.it | fmazzi@comune.genova.it ] > <br>> Cc: "pgpool-general" < [ mailto:pgpool-general@pgpool.net | pgpool-general@pgpool.net ] > <br>> Inviato: Mercoledì, 30 giugno 2021 5:20:41 <br>> Oggetto: Re: [pgpool-general: 7601] Problems with response time <br>> <br>> Hello, <br>> <br>> On Tue, 29 Jun 2021 15:22:51 +0200 (CEST) <br>> Francesco Mazzi < [ mailto:fmazzi@comune.genova.it | fmazzi@comune.genova.it ] > 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 < [ mailto:pengbo@sraoss.co.jp | pengbo@sraoss.co.jp ] > <br>> SRA OSS, Inc. Japan <br>> [ http://www.sraoss.co.jp/ | http://www.sraoss.co.jp/ ] <br>> <br>> <br>> <br>> <br>> Click [ https://www.mailcontrol.com/sr/MZbqvYs5QwJvpeaetUwhCQ== | here ] to report this email as spam. <br>> _______________________________________________ <br>> pgpool-general mailing list <br>> [ mailto:pgpool-general@pgpool.net | pgpool-general@pgpool.net ] <br>> [ http://www.pgpool.net/mailman/listinfo/pgpool-general | http://www.pgpool.net/mailman/listinfo/pgpool-general ] <br>> _______________________________________________ <br>> pgpool-general mailing list <br>> [ mailto:pgpool-general@pgpool.net | pgpool-general@pgpool.net ] <br>> [ http://www.pgpool.net/mailman/listinfo/pgpool-general | http://www.pgpool.net/mailman/listinfo/pgpool-general ] <br>> <br>> <br>> <br>> <br>> _______________________________________________ <br>> pgpool-general mailing list <br>> pgpool-general@pgpool.net <br>> http://www.pgpool.net/mailman/listinfo/pgpool-general <br>> <br>> _______________________________________________ <br>> pgpool-general mailing list <br>> pgpool-general@pgpool.net <br>> http://www.pgpool.net/mailman/listinfo/pgpool-general <br>> <br>> _______________________________________________ <br>> pgpool-general mailing list <br>> pgpool-general@pgpool.net <br>> http://www.pgpool.net/mailman/listinfo/pgpool-general <br><br><br>-- <br>Bo Peng <pengbo@sraoss.co.jp><br>SRA OSS, Inc. Japan<br>http://www.sraoss.co.jp/<br>_______________________________________________<br>pgpool-general mailing list<br>pgpool-general@pgpool.net<br>http://www.pgpool.net/mailman/listinfo/pgpool-general<br></div></div></body></html>