pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jerry Sievers <gsievers19@comcast.net>
To: John Scalia <jayknowsunix@gmail.com>
Cc: Jerry Sievers <gsievers19@comcast.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Pg11 server w/parallel backends unresponsive to cancel/terminate...
Date: Fri, 11 Sep 2020 08:33:08 -0500
Message-ID: <877dt0o2uj.fsf@jsievers.enova.com> (raw)
In-Reply-To: <3BE8A1FC-D3E4-4782-BC54-D1992182F92C@gmail.com>
References: <87imcko3zv.fsf@jsievers.enova.com>
	<3BE8A1FC-D3E4-4782-BC54-D1992182F92C@gmail.com>

John Scalia <jayknowsunix@gmail.com> writes:

> Easiest thing I would do is find that processes PID by querying the
> pg_stat_activity table, then invoke “select 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


> —
> Jay 
>
> Sent from my iPad
>
>> On Sep 11, 2020, at 9:08 AM, Jerry Sievers <gsievers19@comcast.net> wrote:
>> 
>> Greetings!  One of my $power users launched big-nasty job on our ~50TB
>> warehouse.
>> 
>> 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.
>> 
>> Below are 10 lines captured by running strace over each of the PIDs.
>> 
>> What else to look for before we stop-immediate the system?
>> 
>> Thx!
>> 
>> PS: Will repost w/gdb output as soon as I can remember how to run it for
>> a case like this :-)
>> 
>> 
>> 
>> PostgreSQL 11.7 (Ubuntu 11.7-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit
>> 
>> 
>> 
>> # echo $#
>> 9
>> 
>> # for p; do strace -p $p 2>&1 | head -10; echo; done 
>> strace: Process 42079 attached
>> strace: [ Process PID=42079 runs in x32 mode. ]
>> strace: [ Process PID=42079 runs in 64 bit mode. ]
>> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8219365000
>> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8214364000
>> munmap(0x7f8219365000, 41947136)        = 0
>> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81ff856000
>> munmap(0x7f8214364000, 83890176)        = 0
>> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81eb855000
>> munmap(0x7f81ff856000, 167776256)       = 0
>> 
>> strace: Process 42372 attached
>> strace: [ Process PID=42372 runs in x32 mode. ]
>> strace: [ Process PID=42372 runs in 64 bit mode. ]
>> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81e9afa000
>> munmap(0x7f81fdafb000, 167776256)       = 0
>> munmap(0x7f81e9afa000, 335548416)       = 0
>> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8219a0b000
>> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8214a0a000
>> munmap(0x7f8219a0b000, 41947136)        = 0
>> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81fdafb000
>> 
>> strace: Process 42373 attached
>> strace: [ Process PID=42373 runs in x32 mode. ]
>> strace: [ Process PID=42373 runs in 64 bit mode. ]
>> munmap(0x7f81e9afa000, 335548416)       = 0
>> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8217a0a000
>> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8212a09000
>> munmap(0x7f8217a0a000, 41947136)        = 0
>> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81fdafb000
>> munmap(0x7f8212a09000, 83890176)        = 0
>> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81e9afa000
>> 
>> strace: Process 42374 attached
>> strace: [ Process PID=42374 runs in x32 mode. ]
>> strace: [ Process PID=42374 runs in 64 bit mode. ]
>> munmap(0x7f81e9afa000, 335548416)       = 0
>> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8219a0b000
>> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8214a0a000
>> munmap(0x7f8219a0b000, 41947136)        = 0
>> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81fdafb000
>> munmap(0x7f8214a0a000, 83890176)        = 0
>> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81e9afa000
>> 
>> strace: Process 42375 attached
>> strace: [ Process PID=42375 runs in x32 mode. ]
>> strace: [ Process PID=42375 runs in 64 bit mode. ]
>> munmap(0x7f81e9afa000, 335548416)       = 0
>> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8219a0b000
>> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8214a0a000
>> munmap(0x7f8219a0b000, 41947136)        = 0
>> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81fdafb000
>> munmap(0x7f8214a0a000, 83890176)        = 0
>> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81e9afa000
>> 
>> strace: Process 42376 attached
>> strace: [ Process PID=42376 runs in x32 mode. ]
>> strace: [ Process PID=42376 runs in 64 bit mode. ]
>> munmap(0x7f81e9afa000, 335548416)       = 0
>> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8217a0a000
>> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8212a09000
>> munmap(0x7f8217a0a000, 41947136)        = 0
>> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81fdafb000
>> munmap(0x7f8212a09000, 83890176)        = 0
>> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81e9afa000
>> 
>> strace: Process 42377 attached
>> munmap(0x7f81fdafb000, 167776256)       = 0
>> munmap(0x7f81e9afa000, 335548416)       = 0
>> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8217a0a000
>> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8212a09000
>> munmap(0x7f8217a0a000, 41947136)        = 0
>> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81fdafb000
>> munmap(0x7f8212a09000, 83890176)        = 0
>> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81e9afa000
>> munmap(0x7f81fdafb000, 167776256)       = 0
>> 
>> strace: Process 42378 attached
>> strace: [ Process PID=42378 runs in x32 mode. ]
>> strace: [ Process PID=42378 runs in 64 bit mode. ]
>> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81fdafb000
>> munmap(0x7f8214a0a000, 83890176)        = 0
>> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81e9afa000
>> munmap(0x7f81fdafb000, 167776256)       = 0
>> munmap(0x7f81e9afa000, 335548416)       = 0
>> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8219a0b000
>> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8214a0a000
>> 
>> strace: Process 42379 attached
>> strace: [ Process PID=42379 runs in x32 mode. ]
>> strace: [ Process PID=42379 runs in 64 bit mode. ]
>> munmap(0x7f81e9afa000, 335548416)       = 0
>> mmap(NULL, 41947136, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8217a0a000
>> mmap(NULL, 83890176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8212a09000
>> munmap(0x7f8217a0a000, 41947136)        = 0
>> mmap(NULL, 167776256, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81fdafb000
>> munmap(0x7f8212a09000, 83890176)        = 0
>> mmap(NULL, 335548416, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f81e9afa000
>> 
>> 
>> 
>> 
>> -- 
>> Jerry Sievers
>> Postgres DBA/Development Consulting
>> e: postgres.consulting@comcast.net
>> 
>> 
>
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net





view thread (5+ messages)  latest in thread

Message-ID: <877dt0o2uj.fsf@jsievers.enova.com>
Permalink:  ../877dt0o2uj.fsf@jsievers.enova.com/
Also on:    postgresql.org/message-id/877dt0o2uj.fsf@jsievers.enova.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: gsievers19@comcast.net, jayknowsunix@gmail.com
  Subject: Re: Pg11 server w/parallel backends unresponsive to cancel/terminate...
  In-Reply-To: <877dt0o2uj.fsf@jsievers.enova.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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