Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kGjBf-0007Hh-9c for pgsql-admin@arkaria.postgresql.org; Fri, 11 Sep 2020 13:33:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kGjBe-0007YW-88 for pgsql-admin@arkaria.postgresql.org; Fri, 11 Sep 2020 13:33:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kGjBd-0007YP-VL for pgsql-admin@lists.postgresql.org; Fri, 11 Sep 2020 13:33:58 +0000 Received: from resqmta-ch2-09v.sys.comcast.net ([2001:558:fe21:29:69:252:207:41]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kGjBb-0002Ik-2i for pgsql-admin@postgresql.org; Fri, 11 Sep 2020 13:33:57 +0000 Received: from resomta-ch2-06v.sys.comcast.net ([69.252.207.102]) by resqmta-ch2-09v.sys.comcast.net with ESMTP id Ghazkp2R78whoGjBXk282j; Fri, 11 Sep 2020 13:33:51 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1599831231; bh=RXEkMm6MxZVhgIVCp62gjl4Zz/lUEiVp0y7cx8VNen4=; h=Received:Received:Received:From:To:Subject:Date:Message-ID: MIME-Version:Content-Type; b=gC4d8vKl4sgCtTPnvZHrIaKpx4h0C5E+I/CGj2dNZhB0gWko8zdVLg2bOMvNu2dwT V1m+TS1JyJmbLfCSi/SDN1C9SdNpYD4TehGYgAQ9yeQxaVg48Nfy+RGJh6jP+JnoQP WonqWVMY7pSvkdXAJ0zkLDXuL/VWvYcHzsAfCiKzyUGRLsROIeVnyHinDR1iezWOOF fiQfoTDtOQ8RSA8+LuPD3AGV+0GgJBOR9lexjnT2Spi+ldYEFOqwFFz1jHTDPP/3GI mzqft8Gvme175oXrlsJzl5US3lWEQDpKuXaM1or4y2+efnXBXyNCs1PYpsSucJHPD8 65MhYqMQJc3sA== Received: from jsievers.comcast.net ([12.187.116.132]) by resomta-ch2-06v.sys.comcast.net with ESMTPA id GjArkLn9zTyxcGjBEkEKIw; Fri, 11 Sep 2020 13:33:41 +0000 X-Xfinity-VAAS: gggruggvucftvghtrhhoucdtuddrgeduiedrudehledgieekucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecuvehomhgtrghsthdqtfgvshhipdfqfgfvpdfpqffurfetoffkrfenuceurghilhhouhhtmecufedttdenucenucfjughrpefhvffufhffjgfkfgggtgfgsehtqhertddtreejnecuhfhrohhmpeflvghrrhihucfuihgvvhgvrhhsuceoghhsihgvvhgvrhhsudelsegtohhmtggrshhtrdhnvghtqeenucggtffrrghtthgvrhhnpeevhfelgfeuudevudetvefgtdekheeggfehgeetheekkefhvdegvdehueehvdekveenucfkphepuddvrddukeejrdduudeirddufedvnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehhvghlohepjhhsihgvvhgvrhhsrdgtohhmtggrshhtrdhnvghtpdhinhgvthepuddvrddukeejrdduudeirddufedvpdhmrghilhhfrhhomhepghhsihgvvhgvrhhsudelsegtohhmtggrshhtrdhnvghtpdhrtghpthhtohepghhsihgvvhgvrhhsudelsegtohhmtggrshhtrdhnvghtpdhrtghpthhtohepjhgrhihknhhofihsuhhnihigsehgmhgrihhlrdgtohhmpdhrtghpthhtohepphhgshhqlhdqrggumhhinhesphhoshhtghhrvghsqhhlrdhorhhg X-Xfinity-VMeta: sc=0.00;st=legit Received: from jsievers.enova.com (localhost [127.0.0.1]) by jsievers.comcast.net (Postfix) with ESMTP id DE57B26005A; Fri, 11 Sep 2020 08:33:08 -0500 (CDT) From: Jerry Sievers To: John Scalia Cc: Jerry Sievers , pgsql-admin@postgresql.org Subject: Re: Pg11 server w/parallel backends unresponsive to cancel/terminate... References: <87imcko3zv.fsf@jsievers.enova.com> <3BE8A1FC-D3E4-4782-BC54-D1992182F92C@gmail.com> Date: Fri, 11 Sep 2020 08:33:08 -0500 In-Reply-To: <3BE8A1FC-D3E4-4782-BC54-D1992182F92C@gmail.com> (John Scalia's message of "Fri, 11 Sep 2020 09:15:48 -0400") Message-ID: <877dt0o2uj.fsf@jsievers.enova.com> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk John Scalia writes: > 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 those parenthesis. John, none of the backends respond to pg_(cancel|terminate)_backend(PID)... as per $subject :-) Thx > =E2=80=94 > Jay=20 > > Sent from my iPad > >> On Sep 11, 2020, at 9:08 AM, Jerry Sievers wrot= e: >>=20 >> =EF=BB=BFGreetings! One of my $power users launched big-nasty job on ou= r ~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, comp= iled 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 > > > --=20 Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net