Re: many backends hang on MultiXactOffsetSLRU

Hi,

I encountered this in a project we migrated to PostgreSQL
before, and unfortunately, it’s a situation that completely degrades
performance. We identified the cause as savepoints being used excessively
and without control. Once they reduced the number of savepoints, the issue
was resolved. However, the documentation also mentions that it could be
caused by foreign keys.

Kind regards..

James Pang <jamespang886(at)gmail(dot)com>, 10 Eyl 2024 Sal, 10:33 tarihinde şunu
yazdı:

> Hi experts,
> we have a Postgresql v14.8 database, almost thousands of backends hang
> on MultiXactOffsetSLRU at the same time, all of these sessions running same
> query "SELECT ....", from OS and postgresql slow log, we found all of these
> query on "BIND" stage.
> LOG: duration: 36631.688 ms bind S_813: SELECT
> LOG: duration: 36859.786 ms bind S_1111: SELECT
> LOG: duration: 35868.148 ms bind <unnamed>: SELECT
> LOG: duration: 36906.471 ms bind <unnamed>: SELECT
> LOG: duration: 35955.489 ms bind <unnamed>: SELECT
> LOG: duration: 36833.510 ms bind <unnamed>: SELECT
> LOG: duration: 36839.535 ms bind S_1219: SELECT
> ...
>
> this database hang on MultiXactOffsetSLRU and MultiXactOffsetBuffer long
> time.
>
> could you direct me why they are hanging on 'BIND‘ stage with
> MultiXactOffsetSLRU ?
>
> Thanks,
>
> James
>
>
>
>
>