Re: pg_basebackup blocking all queries with horrible performance

On Sun, Jun 10, 2012 at 11:10 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Sun, Jun 10, 2012 at 4:08 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Sun, Jun 10, 2012 at 10:34 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>> On Sun, Jun 10, 2012 at 9:25 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>>> On Sun, Jun 10, 2012 at 7:43 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>>>>> On Sat, Jun 9, 2012 at 2:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>>>> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
>>>>>>> This seems a bug. I think we should prevent pg_basebackup from
>>>>>>> becoming synchronous standby. Thought?
>>>>>>
>>>>>> Absolutely.  If we have replication clients that are not actually
>>>>>> capable of being standbys, there *must* be a way for the master
>>>>>> to know that.
>>>>>
>>>>> I thought we fixed this already by sending InvalidXlogRecPtr as flush
>>>>> location? And that this only applied in 9.2?
>>>>>
>>>>> Are you saying we picked pg_basebackup *in backup mode* (not log
>>>>> streaming) as synchronous standby?
>>>>
>>>> Yes.
>>>>
>>>>> If so then yes, that is
>>>>> *definitely* a bug that should be fixed. We should never select a
>>>>> connection that's not even streaming log as standby!
>>>>
>>>> Agreed. Attached patch prevents pg_basebackup from becoming sync
>>>> standby. Also this patch fixes another problem: currently only walsender
>>>> which reaches STREAMING state can become sync walsender. OTOH,
>>>> sync walsender thinks that walsender with higher priority will be sync one
>>>> whether its state is STREAMING, and switches to potential sync walsender.
>>>> So when the standby with higher priority connects to the master, we
>>>> might have no sync standby until it reaches the STREAMING state.
>>>> To fix this problem, the patch switches walsender's state from sync to
>>>> potential *after* walsender with higher priority has reached the
>>>> STREAMING state.
>>>>
>>>> We also should not select (1) background stream process forked from
>>>> pg_basebackup and (2) pg_receivexlog as sync standby because they
>>>> don't send back replication progress. To address this, I'm thinking to
>>>> introduce new option "NOSYNC" in "START_REPLICATION" command
>>>> as follows, and to change (1) and (2) so that they specify NOSYNC.
>>>>
>>>>    START_REPLICATION XXX/XXX [NOSYNC]
>>>>
>>>> If the standby specifies NOSYNC option, it's never assigned as sync
>>>> standby even if its name is in synchronous_standby_names. Thought?
>>>
>>> The standby which always sends InvalidXLogRecPtr back should not
>>> become sync one. So instead of NOSYNC option, by checking whether
>>> InvalidXLogRecPtr is sent, we can avoid problematic sync standby.
>>
>> We should not do this because Magnus is proposing the patch
>> (http://archives.postgresql.org/pgsql-hackers/2012-06/msg00348.php)
>> which breaks the above assumption at all. So we should introduce
>> something like NOSYNC option.
>
> Wouldn't the better choice there in that case be to give a switch to
> pg_receivexlog if you *want* it to be able to become a sync replica,
> and by default disallow it? And then keep the backend just treating
> InvalidXlogRecPtr as don't-become-sync-replica.

I don't object to making pg_receivexlog as sync standby at all. So at least
for me, that switch is not necessary. What I'm worried about is the
background stream process forked from pg_basebackup. I think that
it should not run as sync standby but sending back its replication progress
seems helpful because a user can see the progress from pg_stat_replication.
So I'm thinking that something like NOSYNC option is required.

Regards,

--
Fujii Masao