Re: Queries on async replicas locked each other after index rename on master

On Wed, Aug 31, 2016 at 8:07 AM, Chinh Nguyen <chinhngt(at)gmail(dot)com> wrote:

> Hello All,
>
> We recently tried to reindex on a heavy used database cluster (master
> + multiple hot-standby async replicas, all taking traffic) and
> replicas were effectively blocked for 10 minutes resulting in drop of
> a lot of read traffic. We reindex by create new index concurrently,
> then rename old and new index and drop old index afterwards.
> After we execute the renaming on master, we found a quick burst of
> exclusive locks as expected:
>
> Process 34482 waits for AccessShareLock on relation 4153657 of
> database 16420; blocked by process 18953.
> Process 18953: ALTER INDEX public.old_index RENAME TO temp_index;
>
> But all hot-standby replicas started to see queries locking up for 10
> minutes, sample log from a replica is below:
>
> LOG: process 41040 still waiting for AccessShareLock on relation
> 4153657 of database 16420 after 1000.072 ms
>
> We have in config:
>
> max_standby_streaming_delay = 10min
> lock_timeout = 10s
>
> Any idea what happened with the replicas? Why queries on replica
> locked for so long vs sub-second on master?
>

I think, it is not the lock release wait time, it is the delay in WAL
replay on standby from master due to the parameter
"max_standby_streaming_delay".
If you have read queries running on standby, WAL replay waits for
"max_standby_streaming_delay" duration (which is 10 mins in your case)
before timing out queries on replica.

Regards,
Venkata B N

Fujitsu Australia