[pgpool-general: 7636] Re: Pgpool replication delay

[Tatsuo Ishii]

> Hi,
> 
> Set delay_threshold = 30 and load tested. The error is not seen.

It seems Pgpool-II is avoiding the PostgreSQL backend having too much
replication delay. Good.

> The following appearing in log sometimes:
> 2021-07-08 04:05:13: pid 4285: LOG:  selecting backend connection
> 2021-07-08 04:05:13: pid 4285: DETAIL:  failover or failback event
> detected, discarding existing connections

This is normal. Pgpool detects there has been a failover/failback
event. This means that existing connections (connection pools) to
backend may not be usable any more. Thus pgpool creats new connections
to backend.

> Any thoughts on this?
> 
> Thanks!
> 
> On Sat, Jul 3, 2021 at 6:22 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> >>> Any recommendations for this kind of scenario?
>> >>
>> >> synchronous_commit = remote_apply will help at the expense of
>> >> performance.
>> >
>> > Another option is, setting delay_threshold of pgpool.conf to lower
>> > value. Default is 10000000 which allows large replication delay. So
>> > you might want to set smaller value for it.
>> >
>> > Note that observation of replication delay is done periodically,
>> > defined by sr_check_period. This means that if replication delay grows
>> > rapidly, the replication delay observed by pgpool could be outdated
>> > and pgpool may send SELECT to delayed standby, and you might see the
>> > error.
>> >
>> > In summary delay_threshold is not perfect but can be useful if you
>> > want to keep on using asynchronous replication.
>>
>> Actually Pgpool-II has many other options that might help you. Please
>> checkout the manual:
>>
>> https://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html#RUNTIME-CONFIG-LOAD-BALANCING-SETTINGS
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
> 
> 
> -- 
> Life is like this: "Just when we get all the answers of life.... God
> changes the question paper....
> 
> Valsaraj Viswanathan