[pgpool-general: 8759] Re: Clients disconnection when slave node is off

[Tatsuo Ishii]

What do you mean by "database is not available"?

1. You can connect to pgpool but pgpool does not reply back.

2. You can cannect to pgpool but pgpool immediately disconnects.

> Hi Tatsuo,
> 
> I'm working with your patch but I continue facing a problem because the
> database is not available during 1 second aprox (I have a script calling
> select query every 0.1 seconds to check the time is not available the
> database).
> 
> I will explain two different cases:
> 
> 1. Slave node (backend1 in pgpool.conf) is turn off. With your patch the
> database is always available. Without your patch the database is not
> available during 1 second.
> 2. Master node (backend0) is turn off. Failover is done to promote
> backend1. After that, I turn on again backend0, which is now slave node. If
> I turn off this slave node (backend0), the database is not available during
> 1 second (with or without your patch)
> 
> Do you have any idea why is this behaviour?
> 
> Thanks in advance.
> 
> Best,
> Jesús
> 
> El vie, 14 abr 2023 3:41, Tatsuo Ishii <ishii at sraoss.co.jp> escribió:
> 
>> Hi Jesús,
>>
>> > Hi Tatsuo,
>> >
>> > At first, thank you so much for your time to investigate this issue.
>>
>> No problem.
>>
>> > I have compiled pgpool 4.3.2 with your patch and the problem with pgbench
>> > is solved.
>> > I still need to test it in my environment.
>> >
>> > Anyway, I had a look your code and I have seen that the session is closed
>> > only if failover is not completed in 30 seconds.
>> > I have the following doubt related to this change. Is this session
>> > operative during the failover? I mean, if failover spends 20 seconds, is
>> > this session blocked during this time or this session can accept any
>> > transaction?
>>
>> It is likely the session is blocked. The reason for "likely" is the
>> function which has the logic inside can be called frequently during
>> session but it is not always. It is possible that a pgpool process
>> already called the function by the time when failover starts, then
>> proceeds and sends a query to backend.
>>
>> > Let me another question. Should we add this issue as a bug?
>>
>> No you don't need. Developers already recognize this a bug report.
>>
>> > Thanks in advance.
>> >
>> > Best,
>> > Jesús
>> >
>> >
>> > El mié, 12 abr 2023 3:33, Tatsuo Ishii <ishii at sraoss.co.jp> escribió:
>> >
>> >> > However a downside of this is, while failover clients cannot process
>> >> > queries or at least slow down processing. Below is the log from
>> >> > pgbench using "-P 1" option to show progress. As you can see from 170
>> >> > s pgbench starts to slow down and recovers at 194 s. That is, the
>> >> > slowdown continued for 24 seconds.
>> >> >
>> >>
>> >> After more research, I suspect the slow down is due to effect of
>> >> checkpointing. If I add "-S" option to change the transaction time, I
>> >> don't see the slow down anymore.
>> >>
>> >> Best reagards,
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS LLC
>> >> English: http://www.sraoss.co.jp/index_en/
>> >> Japanese:http://www.sraoss.co.jp
>> >>
>>