pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Many queries stuck for waiting MultiXactOffsetControlLock on PostgreSQL 9.6.1 replica
4+ messages / 4 participants
[nested] [flat]

* Many queries stuck for waiting MultiXactOffsetControlLock on PostgreSQL 9.6.1 replica
@ 2021-07-31 19:30  Rui An <rueian.huang@gmail.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Rui An @ 2021-07-31 19:30 UTC (permalink / raw)
  To: pgsql-bugs

Hi, I have recently encountered the problem, as title, on some of my
PostgreSQL 9.6.1 hot standby replicas when serving readonly queries.

The problem makes a lot of PostgreSQL processes stuck on waiting
for MultiXactOffsetControlLock and eventually runs out of
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 MultiXactOffsetControlLock even 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 have also recorded some strace and perf top output on stuck processes:

strace:

> strace: Process 555 attached
> select(0, NULL, NULL, NULL, {0, 78}       = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> semop(60981276, [{0, -1, 0}, 1])               = 0
> semop(60981276, [{0, -1, 0}, 1])               = 0
> semop(61734963, [{14, -1, 0}, 1])             = 0
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> semop(61505580, [{12, -1, 0}, 1])             = 0
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> semop(60981276, [{0, -1, 0}, 1])               = 0
> semop(63471720, [{9, -1, 0}, 1])               = 0
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> semop(60981276, [{0, -1, 0}, 1])               = 0
> semop(63471720, [{9, -1, 0}, 1])               = 0
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> select(0, NULL, NULL, NULL, {0, 1000}   = 0 (Timeout)
> semop(60981276, [{0, -1, 0}, 1])               = 0


perf top:

> Samples: 208  of event 'cpu-clock', Event count (approx.): 36045672
> Overhead  Shared Object   Symbol
>     30.74%  postgres            [.] perform_spin_delay
>     13.70%  [kernel]              [k] finish_task_switch
>     13.66%  [kernel]              [k] 0x00007fff81838f35
>       8.59%  postgres            [.] 0x0000000000319107
>       4.02%  postgres            [.] 0x0000000000318f04
>       2.47%  postgres            [.] pg_usleep
>       2.10%  libc-2.23.so       [.] __select
>       1.68%  postgres            [.] PGSemaphoreLock
>       1.68%  postgres            [.] 0x0000000000318eb2
>       1.54%  [kernel]              [k] pvclock_clocksource_read
>       1.53%  postgres            [.] GetMultiXactIdMembers
>       1.41%  postgres            [.] 0x0000000000318ea4
>       1.31%  postgres            [.] LWLockAcquire
>       1.30%  postgres            [.] 0x0000000000318e17
>       1.23%  [kernel]              [k] do_select
>       1.07%  postgres            [.] finish_spin_delay
>       1.06%  [kernel]              [k] poll_freewait
>       1.05%  libc-2.23.so       [.] semop
>       0.96%  [kernel]              [k] poll_schedule_timeout
>       0.72%  [kernel]              [k] SYSC_semtimedop
>       0.69%  [kernel]              [k] core_sys_select
>       0.69%  [kernel]              [k] pid_vnr
>       0.61%  [kernel]              [k] dev_gro_receive
>       0.61%  libc-2.23.so       [.] random
>       0.61%  postgres            [.] 0x0000000000318e3d
>       0.53%  [kernel]              [k] hrtimer_active
>       0.53%  [kernel]              [k] ipc_obtain_object_check
>       0.46%  postgres            [.] 0x0000000000318f00
>       0.46%  postgres            [.] 0x0000000000319100
>       0.46%  postgres            [.] 0x0000000000319103
>       0.41%  postgres            [.] LWLockRelease
>       0.41%  postgres            [.] 0x0000000000318f85
>       0.36%  [kernel]              [k] run_timer_softirq
>       0.36%  [kernel]              [k] sys_semop

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Many queries stuck for waiting MultiXactOffsetControlLock on PostgreSQL 9.6.1 replica
@ 2021-08-02 04:57  Andrey Borodin <x4mmm@yandex-team.ru>
  parent: Rui An <rueian.huang@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Andrey Borodin @ 2021-08-02 04:57 UTC (permalink / raw)
  To: Rui An <rueian.huang@gmail.com>; +Cc: pgsql-bugs

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 serving readonly queries.
> 
> The problem makes a lot of PostgreSQL processes stuck on waiting for MultiXactOffsetControlLock and eventually runs out of 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 MultiXactOffsetControlLock even 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 related to 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't put 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 settings will 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...
[1] https://www.postgresql.org/message-id/flat/20200515.090333.24867479329066911.horikyota.ntt%40gmail.c...
[2] https://github.com/postgres/postgres/blob/REL9_6_STABLE/src/include/access/multixact.h#L32-L33




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Many queries stuck for waiting MultiXactOffsetControlLock on PostgreSQL 9.6.1 replica
@ 2021-08-02 05:48  David G. Johnston <david.g.johnston@gmail.com>
  parent: Rui An <rueian.huang@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: David G. Johnston @ 2021-08-02 05:48 UTC (permalink / raw)
  To: Rui An <rueian.huang@gmail.com>; +Cc: pgsql-bugs

On Saturday, July 31, 2021, Rui An <rueian.huang@gmail.com> wrote:

>
> I have tried use pg_terminate_backend to kill the stuck prcoesses, but no
> help. They still stuck on waiting for MultiXactOffsetControlLock even if
> the pg_terminate_backend(pid) returns true.
>

All the true is telling you is your process sent the requested signal.  It,
and related functions, provide no insight as to if/how receivers of said
signal may have responded.

As for the question at hand - the axiom is “if it ain’t broke don’t fix
it”…but as your system is basically broke you need to fix it by running a
supported version of the software.

And you should also be prepared for the possibility that 9.6 is
discontinued before there is a fix in which case you’d have to upgrade to
v10 (at least) to get said fix.

David J.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Many queries stuck for waiting MultiXactOffsetControlLock on PostgreSQL 9.6.1 replica
@ 2021-08-02 14:18  Tom Lane <tgl@sss.pgh.pa.us>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2021-08-02 14:18 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Rui An <rueian.huang@gmail.com>; pgsql-bugs

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> And you should also be prepared for the possibility that 9.6 is
> discontinued before there is a fix in which case you’d have to upgrade to
> v10 (at least) to get said fix.

If the fix is as complex as what's being discussed over at [1], there
is zero chance that it'd be back-patched at all.

Nonetheless, still being on 9.6.1 at this late date is next door to
software malpractice.  There are a *lot* of known bugs in that version
that have been fixed, including performance issues [2].

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/2BEC2B3F-9B61-4C1D-9FB5-5FAB0F05EF86%40yandex-team.ru
[2] https://www.postgresql.org/docs/release/





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2021-08-02 14:18 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-31 19:30 Many queries stuck for waiting MultiXactOffsetControlLock on PostgreSQL 9.6.1 replica Rui An <rueian.huang@gmail.com>
2021-08-02 04:57 ` Andrey Borodin <x4mmm@yandex-team.ru>
2021-08-02 05:48 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-02 14:18   ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox