[pgpool-general: 7641] Re: Problems with response time
Francesco Mazzi
fmazzi at comune.genova.it
Wed Jul 14 18:10:42 JST 2021
There is no log in postgresql.
The slow response time is about 5s, not exactly 5, but always different (5.02 5.03 5.06 and similar).
I'll enable debug log and I'll look for other messages
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/
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210714/fde78b0d/attachment.htm>
More information about the pgpool-general
mailing list