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

[Bo Peng]

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/