Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: John Scalia <jayknowsunix@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Pg11 server w/parallel backends unresponsive to
 cancel/terminate...
Date: Fri, 11 Sep 2020 09:15:48 -0400
Message-Id: <3BE8A1FC-D3E4-4782-BC54-D1992182F92C@gmail.com>
References: <87imcko3zv.fsf@jsievers.enova.com>
Cc: pgsql-admin@postgresql.org
In-Reply-To: <87imcko3zv.fsf@jsievers.enova.com>
To: Jerry Sievers <gsievers19@comcast.net>
Precedence: bulk

Easiest thing I would do is find that processes PID by querying the pg_stat_=
activity table, then invoke =E2=80=9Cselect from pg_terminate_backend(pid#);=
 where of course, you put the determined PID from your first query into thos=
e parenthesis.
=E2=80=94
Jay=20

Sent from my iPad

> On Sep 11, 2020, at 9:08 AM, Jerry Sievers <gsievers19@comcast.net> wrote:=

>=20
> =EF=BB=BFGreetings!  One of my $power users launched big-nasty job on our ~=
50TB
> warehouse.
>=20
> Long-running jobs on that box aren't unusual, however this one we want
> stopped and it's unresponsive to cancel or terminate signalling for
> several hours.
>=20
> Below are 10 lines captured by running strace over each of the PIDs.
>=20
> What else to look for before we stop-immediate the system?
>=20
> Thx!
>=20
> PS: Will repost w/gdb output as soon as I can remember how to run it for
> a case like this :-)
>=20
>=20
>=20
> PostgreSQL 11.7 (Ubuntu 11.7-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compil=
ed by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit
>=20
>=20
>=20
> # echo $#
> 9
>=20
> # for p; do strace -p $p 2>&1 | head -10; echo; done=20
> strace: Process 42079 attached
> strace: [ Process PID=3D42079 runs in x32 mode. ]
> strace: [ Process PID=3D42079 runs in 64 bit mode. ]
> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8219365000
> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8214364000
> munmap(0x7f8219365000, 41947136)        =3D 0
> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81ff856000
> munmap(0x7f8214364000, 83890176)        =3D 0
> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81eb855000
> munmap(0x7f81ff856000, 167776256)       =3D 0
>=20
> strace: Process 42372 attached
> strace: [ Process PID=3D42372 runs in x32 mode. ]
> strace: [ Process PID=3D42372 runs in 64 bit mode. ]
> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81e9afa000
> munmap(0x7f81fdafb000, 167776256)       =3D 0
> munmap(0x7f81e9afa000, 335548416)       =3D 0
> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8219a0b000
> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8214a0a000
> munmap(0x7f8219a0b000, 41947136)        =3D 0
> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81fdafb000
>=20
> strace: Process 42373 attached
> strace: [ Process PID=3D42373 runs in x32 mode. ]
> strace: [ Process PID=3D42373 runs in 64 bit mode. ]
> munmap(0x7f81e9afa000, 335548416)       =3D 0
> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8217a0a000
> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8212a09000
> munmap(0x7f8217a0a000, 41947136)        =3D 0
> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81fdafb000
> munmap(0x7f8212a09000, 83890176)        =3D 0
> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81e9afa000
>=20
> strace: Process 42374 attached
> strace: [ Process PID=3D42374 runs in x32 mode. ]
> strace: [ Process PID=3D42374 runs in 64 bit mode. ]
> munmap(0x7f81e9afa000, 335548416)       =3D 0
> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8219a0b000
> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8214a0a000
> munmap(0x7f8219a0b000, 41947136)        =3D 0
> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81fdafb000
> munmap(0x7f8214a0a000, 83890176)        =3D 0
> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81e9afa000
>=20
> strace: Process 42375 attached
> strace: [ Process PID=3D42375 runs in x32 mode. ]
> strace: [ Process PID=3D42375 runs in 64 bit mode. ]
> munmap(0x7f81e9afa000, 335548416)       =3D 0
> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8219a0b000
> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8214a0a000
> munmap(0x7f8219a0b000, 41947136)        =3D 0
> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81fdafb000
> munmap(0x7f8214a0a000, 83890176)        =3D 0
> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81e9afa000
>=20
> strace: Process 42376 attached
> strace: [ Process PID=3D42376 runs in x32 mode. ]
> strace: [ Process PID=3D42376 runs in 64 bit mode. ]
> munmap(0x7f81e9afa000, 335548416)       =3D 0
> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8217a0a000
> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8212a09000
> munmap(0x7f8217a0a000, 41947136)        =3D 0
> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81fdafb000
> munmap(0x7f8212a09000, 83890176)        =3D 0
> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81e9afa000
>=20
> strace: Process 42377 attached
> munmap(0x7f81fdafb000, 167776256)       =3D 0
> munmap(0x7f81e9afa000, 335548416)       =3D 0
> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8217a0a000
> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8212a09000
> munmap(0x7f8217a0a000, 41947136)        =3D 0
> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81fdafb000
> munmap(0x7f8212a09000, 83890176)        =3D 0
> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81e9afa000
> munmap(0x7f81fdafb000, 167776256)       =3D 0
>=20
> strace: Process 42378 attached
> strace: [ Process PID=3D42378 runs in x32 mode. ]
> strace: [ Process PID=3D42378 runs in 64 bit mode. ]
> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81fdafb000
> munmap(0x7f8214a0a000, 83890176)        =3D 0
> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81e9afa000
> munmap(0x7f81fdafb000, 167776256)       =3D 0
> munmap(0x7f81e9afa000, 335548416)       =3D 0
> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8219a0b000
> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8214a0a000
>=20
> strace: Process 42379 attached
> strace: [ Process PID=3D42379 runs in x32 mode. ]
> strace: [ Process PID=3D42379 runs in 64 bit mode. ]
> munmap(0x7f81e9afa000, 335548416)       =3D 0
> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8217a0a000
> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0=
) =3D 0x7f8212a09000
> munmap(0x7f8217a0a000, 41947136)        =3D 0
> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81fdafb000
> munmap(0x7f8212a09000, 83890176)        =3D 0
> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,=
 0) =3D 0x7f81e9afa000
>=20
>=20
>=20
>=20
> --=20
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consulting@comcast.net
>=20
>=20