For such issues, I find this view very useful (the first one):
https://wiki.postgresql.org/wiki/Lock_Monitoring

Examine blocking_pid's ,  and tell us what kind of operation is blocking
the other processes . Also, are there many long running transactions in
your server?


2018-03-06 21:24 GMT-06:00 Scott Frazer <sfra...@couponcabin.com>:

> Hi, we have a Postgres 9.6 setup using replication that has recently
> started seeing a lot of processes stuck in "SubtransControlLock" as a
> wait_event on the read-replicas. Like this, only usually about 300-800 of
> them:
>
>
>  179706 | LWLockNamed     | SubtransControlLock
>
>  186602 | LWLockNamed     | SubtransControlLock
>
>  186606 | LWLockNamed     | SubtransControlLock
>
>  180947 | LWLockNamed     | SubtransControlLock
>
>  186621 | LWLockNamed     | SubtransControlLock
>
> The server then begins to crawl, with some queries just never finishing
> until I finally shut the server down.
>
> Searching for that particular combo of wait_event_type and wait_event only
> seems to turn up the page about statistics collection, but no helpful
> information on troubleshooting this lock.
>
> Restarting the replica server clears the locks and allows us to start
> working again, but it's happened twice now in 12 hours and I'm worried it
> will happen again.
>
> Does anyone have any advice on where to start looking?
>
> Thanks,
> Scott
>
>


-- 
El genio es 1% inspiraciĆ³n y 99% transpiraciĆ³n.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Reply via email to