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

Поиск
Список
Период
Сортировка
От Chinh Nguyen
Тема Queries on async replicas locked each other after index rename on master
Дата
Msg-id CAB=EggGSZ3RKtC279728STmDvkgxZFYJD3NcYieWthvm+76d1w@mail.gmail.com
обсуждение исходный текст
Ответы Re: Queries on async replicas locked each other after index rename on master
Список pgsql-general
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?

--
With best regards,
Chinh Nguyen


В списке pgsql-general по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Do function calls the cached?
Следующее
От: Venkata B Nagothi
Дата:
Сообщение: Re: Queries on async replicas locked each other after index rename on master