Thread: Long running DDL statements blocking all queries
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,
AshuHi 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 DDLstatements (e.g. 'add index concurently' or 'Alter table alter column type'), after some time, we start seeing that almostevery 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 theblocking and blocked statements and there seems to be absolutely no correlation. > > Thanks, > Ashu
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
Ashu, please, provide full output of: \x select * from pg_stat_activity ; What you posted on github is only providing a list of blocked backends. If I read it correctly, then PID 18317 is the root cause of all the locks, but it does not show up in the list, not beingblocked by anything... regards, fabio pardi On 31/05/18 14:26, Ashu Pachauri wrote: > 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 andpg_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 wasour webserver that only does metadata reads. As you can see from the above gist, even the 'SET' statements are blockedwaiting for the ALTER statement to finish. > > > Thanks, > Ashu > > > On Thu, May 31, 2018 at 4:38 PM Fabio Pardi <f.pardi@portavita.eu <mailto: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 ofpg_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 runningDDL statements (e.g. 'add index concurently' or 'Alter table alter column type'), after some time, we start seeingthat 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 unrelatedtables. I used the instructions given on Postgres wiki (https://wiki.postgresql.org/wiki/Lock_Monitoring) to correlatethe blocking and blocked statements and there seems to be absolutely no correlation. > > > > Thanks, > > Ashu >
There was too much noise in the pg_stat_activity output, so I did not post it. I'll collect the output again and post.
But, when I checked in pg_stat_activity, PID 18317 is the session that's running the ALTER statement and it was showing up as "active". So, it's not blocked by anything, but the fact that the ALTER statement is long running and it's blocking the operations that are not even on the same table for the entire duration it's running is troubling.
Thanks,
AshuOn Thu, May 31, 2018 at 6:28 PM Fabio Pardi <f.pardi@portavita.eu> wrote:
Ashu,
please, provide full output of:
\x
select * from pg_stat_activity ;
What you posted on github is only providing a list of blocked backends.
If I read it correctly, then PID 18317 is the root cause of all the locks, but it does not show up in the list, not being blocked by anything...
regards,
fabio pardi
On 31/05/18 14:26, Ashu Pachauri wrote:
> 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 <mailto: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
>
On Thu, May 31, 2018 at 9:19 AM, Ashu Pachauri <ashu210890@gmail.com> wrote:
There was too much noise in the pg_stat_activity output, so I did not post it. I'll collect the output again and post.But, when I checked in pg_stat_activity, PID 18317 is the session that's running the ALTER statement and it was showing up as "active". So, it's not blocked by anything, but the fact that the ALTER statement is long running and it's blocking the operations that are not even on the same table for the entire duration it's running is troubling.
I think what you really need to know here is what lock it was holding which was blocking everyone. That information won't be found in the pg_stat_activity. It will be found in pg_locks, but the specific query you ran on that view did not display the columns with that information. You need to include all the columns in the output which you used to join the two pg_locks together. Yes, it will be voluminous, and most of them will not be relevant, but you don't know which ones are relevant until after you see the output.
Cheers,
Jeff