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

[valsaraj pv]

Hi,

Is there any option to specify read queries on specific tables to master
only?

Thanks!

On Thu, Jul 8, 2021 at 3:58 PM valsaraj pv <valsarajpv at gmail.com> wrote:

> Ok, thanks.
>
> On Thu, Jul 8, 2021 at 3:35 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
>> > 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
>>
>
>
> --
> Life is like this: "Just when we get all the answers of life.... God
> changes the question paper....
>
> Valsaraj Viswanathan
>


-- 
Life is like this: "Just when we get all the answers of life.... God
changes the question paper....

Valsaraj Viswanathan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210714/034a855a/attachment.htm>