Re: Long running DDL statements blocking all queries

Поиск
Список
Период
Сортировка
От Ashu Pachauri
Тема Re: Long running DDL statements blocking all queries
Дата
Msg-id CA+C_EDiyMs9ZBAZEcJY=c6_D-0gH=81SWvKh=uHW294me2Wf-w@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Long running DDL statements blocking all queries  (Fabio Pardi <f.pardi@portavita.eu>)
Ответы Re: Long running DDL statements blocking all queries  (Fabio Pardi <f.pardi@portavita.eu>)
Список pgsql-general
Thanks Fabio for the reply.  
The queries are blocked in the sense that I can see them in pg_stat_activity.

Please find the query and its output for correlating the blocked activity with blocking query from pg_state_activity and pg_locks: https://gist.github.com/ashu210890/c39cd7a38ce37f4baab2f58e1ade1403

This output was captured after stopping all writes to our postgres database and the only thing talking to postgres was our webserver that only does metadata reads. As you can see from the above gist, even the 'SET' statements are blocked waiting for the ALTER statement to finish.


Thanks,
Ashu


On Thu, May 31, 2018 at 4:38 PM Fabio Pardi <f.pardi@portavita.eu> wrote:
Hi Ashu,

when you say 'almost every query in our application starts getting blocked'...


'blocked' as in 'they are stuck and can be seen in pg_stat_activity'? (in this case, please post the full content of pg_stat_activity)

or

'blocked' as in 'they are waiting in pgbouncer pool?


regards,

fabio pardi


On 31/05/18 12:38, Ashu Pachauri wrote:
> We have been using Postgres 9.5.12 behind PGBouncer and facing some weird issues. Whenever we running long running DDL statements (e.g. 'add index concurently' or 'Alter table alter column type'), after some time, we start seeing that almost every query in our application starts getting blocked.
> I understand that the operations I mentioned cab be unsafe, but the queries being blocked are on completely unrelated tables.  I used the instructions given on Postgres wiki (https://wiki.postgresql.org/wiki/Lock_Monitoring) to correlate the blocking and blocked statements and there seems to be absolutely no correlation.
>
> Thanks,
> Ashu

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

Предыдущее
От: C GG
Дата:
Сообщение: Re: LDAP authentication slow
Следующее
От: C GG
Дата:
Сообщение: Re: Insert UUID GEN 4 Value