[pgpool-general: 7634] Re: Problems with response time
Francesco Mazzi
fmazzi at comune.genova.it
Tue Jul 6 16:10:32 JST 2021
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210706/143438a4/attachment.htm>
More information about the pgpool-general
mailing list