Re: Many queries stuck for waiting MultiXactOffsetControlLock on PostgreSQL 9.6.1 replica

Hi!

> 1 авг. 2021 г., в 00:30, Rui An <rueian.huang@gmail.com> написал(а):
>
> Hi, I have recently encountered the problem, as title, on some of my PostgreSQL 9.6.1 hot standby replicas when
servingreadonly queries. 
>
> The problem makes a lot of PostgreSQL processes stuck on waiting for MultiXactOffsetControlLock and eventually runs
outof the max_connections quota. 
>
> For example:
> mydb=# select count(*) from pg_stat_activity where state = 'active' and wait_event = 'MultiXactOffsetControlLock';
>  count
> ------
>     956
> (1 row)
>
> I have tried use pg_terminate_backend to kill the stuck prcoesses, but no help. They still stuck on waiting for
MultiXactOffsetControlLockeven if the pg_terminate_backend(pid) returns true. 
>
> Currently, the only thing I can do to resolve the problem is to restart my replicas.
>
> Can someone help me find out how could the problem occurred and is there any other way to resolve the problem?
>

I highly recommend you to update PG to 9.6.22. Your system lacks almost 5 years of updates. This may or may not be
relatedto the issue you observe, but still... 

I suspect that you observe effects of this sleep [0]. Kyotaro Horiguchi wrote draft patch to fix this [1], but I
haven'tput enough efforts to make it commit-ready yet. 
I think that you can mitigate the problem by increasing size of MultixactOffset buffers here [2]. I hope in future this
settingswill be configurable, thread [1] is just about this. 

Thanks!

Best regards, Andrey Borodin.

[0] https://github.com/postgres/postgres/blob/REL9_6_STABLE/src/backend/access/transam/multixact.c#L1289-L1319
[1]
https://www.postgresql.org/message-id/flat/20200515.090333.24867479329066911.horikyota.ntt%40gmail.com#855f8bb7205890579a363d2344b4484d
[2] https://github.com/postgres/postgres/blob/REL9_6_STABLE/src/include/access/multixact.h#L32-L33