[pgpool-general: 7640] Re: Problems with response time
Bo Peng
pengbo at sraoss.co.jp
Wed Jul 14 16:46:15 JST 2021
Thank you for sharing pgpool logs.
I can't figure out the cause from the pgpool log.
Was some messages output in the postgresql log during that time?
Is it possible to enbale pgpool debug log?
-----------
# vi /path/to/pgpool.conf
...
log_min_messages = debug1
# pgpool -f /path/to/pgpool.conf reload
-----------
> This is the pgpool log when icinga connects and response time is about 5 seconds:
Is the slow response time always 5 seconds?
If the logs don't show the cause, you may need to track the system calls using "strace".
On Tue, 6 Jul 2021 09:10:32 +0200 (CEST)
Francesco Mazzi <fmazzi at comune.genova.it> wrote:
> This is the pgpool log when icinga connects and response time is about 5 seconds:
>
> Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 26292: LOG: new connection received
> Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 26292: DETAIL: connecting host=192.168.xxx.xxx port=47510
> 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
> Jul 6 03:44:42 xxxxxxx pgpool: 2021-07-06 03:44:42: pid 1556: LOG: fork a new child process with pid: 26366
> 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()
> 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
> 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
> 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
> host=192.168.xxx.xxx port=47510
>
> This is a icinga connection with fast response time:
>
> Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: LOG: new connection received
> Jul 6 03:49:44 xxxxxx pgpool: 2021-07-06 03:49:44: pid 26366: DETAIL: connecting host=192.168.xxx.xxx port=53152
> 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()
> 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
> 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
> 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
> host=192.168.xxx.xxx port=53152
> 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
> Jul 6 03:49:47 xxxxxx pgpool: 2021-07-06 03:49:47: pid 1556: LOG: fork a new child process with pid: 26419
>
> I don't see any difference.
>
>
> Da: "Bo Peng" <pengbo at sraoss.co.jp>
> A: "fmazzi" <fmazzi at comune.genova.it>
> Cc: "pgpool-general" <pgpool-general at pgpool.net>
> Inviato: Martedì, 6 luglio 2021 7:59:43
> Oggetto: [pgpool-general: 7633] Re: Problems with response time
>
> 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/
> _______________________________________________
> 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/
More information about the pgpool-general
mailing list