<html><body><div id="zimbraEditorContainer" style="font-family: arial, helvetica, sans-serif; font-size: 12pt; color: #000000" class="22"><div>There is no log in postgresql.</div><div>The slow response time is about 5s, not exactly 5, but always different (5.02 5.03 5.06 and similar).</div><div>I'll enable debug log and I'll look for other messages </div><div><br data-mce-bogus="1"></div><blockquote><div data-marker="__QUOTED_TEXT__">Thank you for sharing pgpool logs.<br><br>I can't figure out the cause from the pgpool log.<br>Was some messages output in the postgresql log during that time? <br><br>Is it possible to enbale pgpool debug log?<br><br> -----------<br> # vi /path/to/pgpool.conf<br> ...<br> log_min_messages = debug1<br><br> # pgpool -f /path/to/pgpool.conf reload<br> -----------<br><br>> This is the pgpool log when icinga connects and response time is about 5 seconds: <br><br>Is the slow response time always 5 seconds?<br><br>If the logs don't show the cause, you may need to track the system calls using "strace".<br><br>On Tue, 6 Jul 2021 09:10:32 +0200 (CEST)<br>Francesco Mazzi <fmazzi@comune.genova.it> wrote:<br><br>> This is the pgpool log when icinga connects and response time is about 5 seconds: <br>> <br>> Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 26292: LOG: new connection received <br>> Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 26292: DETAIL: connecting host=192.168.xxx.xxx port=47510 <br>> 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 <br>> Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 1556: LOG: fork a new child process with pid: 26366 <br>> 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() <br>> 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 <br>> 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 <br>> 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 <br>> host=192.168.xxx.xxx port=47510 <br>> <br>> This is a icinga connection with fast response time: <br>> <br>> Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: LOG: new connection received <br>> Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: DETAIL: connecting host=192.168.xxx.xxx port=53152 <br>> 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() <br>> 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 <br>> 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 <br>> 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 <br>> host=192.168.xxx.xxx port=53152 <br>> 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 <br>> Jul 6 03:49:47 xxxxxx pgpool: 2021-07-06 03:49:47: pid 1556: LOG: fork a new child process with pid: 26419 <br>> <br>> I don't see any difference. <br>> <br>> <br>> Da: "Bo Peng" <pengbo@sraoss.co.jp> <br>> A: "fmazzi" <fmazzi@comune.genova.it> <br>> Cc: "pgpool-general" <pgpool-general@pgpool.net> <br>> Inviato: Martedì, 6 luglio 2021 7:59:43 <br>> Oggetto: [pgpool-general: 7633] Re: Problems with response time <br>> <br>> 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><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></blockquote></div></body></html>