[pgpool-general: 7608] Re: Problems with response time

[valsaraj pv]

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, <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" <fmazzi at comune.genova.it>
> *A: *"Bo Peng" <pengbo at sraoss.co.jp>
> *Cc: *"pgpool-general" <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" <pengbo at sraoss.co.jp>
> *A: *"fmazzi" <fmazzi at comune.genova.it>
> *Cc: *"pgpool-general" <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 <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 <pengbo at sraoss.co.jp>
> SRA OSS, Inc. Japan
> http://www.sraoss.co.jp/
>
>
> Click here
> <https://www.mailcontrol.com/sr/R4ZAHTHLbfPGX2PQPOmvUizKrmxxhcEGDEgAcCDkx1JX987gvPlvhoxZFJ1e5_1Cj-ibO5yjqHMoKRuEdgZyEA==>
> to report this email as spam.
>
> _______________________________________________
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210630/ff1426c3/attachment.htm>