Обсуждение: postgres process got stuck in "notify interrupt waiting" status
Hi. We use LISTEN/NOTIFY quite a bit but today something unusual (bad) happened. Number of processes waiting for a lock just started to go up up up. I finally found the object being locked was pg_listener which RhodiumToad on IRC kindly informed happens during LISTEN/NOTIFY. The process that had the lock (in pg_locks it had granted = t ) was shown by ps in status "notify interrupt waiting" and has had the lock for over half an hour. (Usually these notifications are very quick.) the process would not respond to kill, so I kill -9'ed The only reference I could find to a similar problem was at http://archives.postgresql.org/pgsql-performance/2008-02/msg00345.php which seemed to indicate a process should not be in this state for very long. We are on postgres 8.4.12. I'd like to figure out what happened. There is a web server that talks to this database server (amongst other clients), and the client addr and port mapped to this web server, but there was no process on the web server matching the port number. that's when I decided to kill the postgres process. Anything I should know or read up on? Any suggestions? I'd like the system to be able to recover, and for the process to terminate if the client is no longer around. Best, Aleksey
BTW, after I signalled TERM, the process status changed from notify interrupt waiting to notify interrupt waiting waiting which I thought looked kind of odd. Then I signalled KILL. Aleksey On Tue, Sep 4, 2012 at 6:21 PM, Aleksey Tsalolikhin <atsaloli.tech@gmail.com> wrote: > Hi. > > We use LISTEN/NOTIFY quite a bit but today something unusual (bad) happened. > > Number of processes waiting for a lock just started to go up up up. > > I finally found the object being locked was pg_listener which > RhodiumToad on IRC kindly informed happens during LISTEN/NOTIFY. The > process that had the lock (in pg_locks it had granted = t ) was shown > by ps in status "notify interrupt waiting" and has had the lock for > over half an hour. (Usually these notifications are very quick.) > > the process would not respond to kill, so I kill -9'ed > > The only reference I could find to a similar problem was at > http://archives.postgresql.org/pgsql-performance/2008-02/msg00345.php > which seemed to indicate a process should not be in this state for > very long. > > We are on postgres 8.4.12. > > I'd like to figure out what happened. > > There is a web server that talks to this database server (amongst > other clients), and the client addr and port mapped to this web > server, but there was no process on the web server matching the port > number. that's when I decided to kill the postgres process. > > Anything I should know or read up on? Any suggestions? > > I'd like the system to be able to recover, and for the process to > terminate if the client is no longer around. > > Best, > Aleksey -- Upcoming Trainings: "Editing with vi" 31 Aug 2012 at LinuxCon North America in San Diego, CA (http://lcna2012.sched.org/speaker/alekseytsalolikhin) "Time Management for System Administrators" 28 Sep 2012 at Ohio Linux Fest (http://ohiolinux.org/register) "Editing with vi" 28 Sep 2012 at Ohio Linux Fest (http://ohiolinux.org/register) "Automating System Administration with CFEngine 3" 22-25 Oct 2012 in Palo Alto, CA (http://www.eventbrite.com/event/3388161081)
On 09/04/12 7:09 PM, Aleksey Tsalolikhin wrote: > BTW, after I signalled TERM, the process status changed from > > notify interrupt waiting > > to > > notify interrupt waiting waiting > > which I thought looked kind of odd. > > Then I signalled KILL. was this a client process or a postgres process? kill -9 on postgres processes can easily trigger data corruption. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Tue, Sep 4, 2012 at 7:21 PM, John R Pierce <pierce@hogranch.com> wrote: > On 09/04/12 7:09 PM, Aleksey Tsalolikhin wrote: >> >> BTW, after I signalled TERM, the process status changed from >> >> notify interrupt waiting >> >> to >> >> notify interrupt waiting waiting >> >> which I thought looked kind of odd. >> >> Then I signalled KILL. > > > was this a client process or a postgres process? kill -9 on postgres > processes can easily trigger data corruption. This was a postgres process. i certainly won't signal KILL anymore to postgres processes, thanks for that warning, John. Aleksey
John R Pierce wrote: > was this a client process or a postgres process? kill -9 on postgres > processes can easily trigger data corruption. It definitely shouldn't cause data corruption, otherwise PostgreSQL would not be crash safe. Yours, Laurenz Albe
On 09/05/2012 12:21 PM, John R Pierce wrote: > was this a client process or a postgres process? kill -9 on postgres > processes can easily trigger data corruption. It certainly shouldn't. kill -9 of the postmaster, deletion of postmaster.pid, and re-starting postgresql *might* but AFAIK even then you'll have to bypass the shared memory lockout (unless you're on Windows). -- Craig Ringer
Craig Ringer <ringerc@ringerc.id.au> writes:
> On 09/05/2012 12:21 PM, John R Pierce wrote:
>> was this a client process or a postgres process? kill -9 on postgres
>> processes can easily trigger data corruption.
> It certainly shouldn't.
> kill -9 of the postmaster, deletion of postmaster.pid, and re-starting
> postgresql *might* but AFAIK even then you'll have to bypass the shared
> memory lockout (unless you're on Windows).
Correction on that: manually deleting postmaster.pid *does* bypass the
shared memory lock. If there are still any live backends from the old
postmaster, you can get corruption as a result of this, because the old
backends and the new ones will be modifying the database independently.
This is why we recommend that you never delete postmaster.pid manually,
and certainly not as part of an automatic startup script.
Having said that, a kill -9 on an individual backend (*not* the
postmaster) should be safe enough, if you don't mind the fact that
it'll kill all your other sessions too.
regards, tom lane
On Wed, Sep 5, 2012 at 7:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Having said that, a kill -9 on an individual backend (*not* the > postmaster) should be safe enough, if you don't mind the fact that > it'll kill all your other sessions too. > Got it, thanks. Why will it kill all your other sessions too? Isn't there a separate backend process for each session? Best, -at
Aleksey Tsalolikhin <atsaloli.tech@gmail.com> wrote: > Why will it kill all your other sessions too? Isn't there a > separate backend process for each session? When stopped that abruptly, the process has no chance to clean up its pending state in shared memory. A fresh copy of shared memory is needed, so it is necessary to effectively do an immediate restart on the whole PostgreSQL instance. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Aleksey Tsalolikhin <atsaloli.tech@gmail.com> wrote:
>> Why will it kill all your other sessions too? Isn't there a
>> separate backend process for each session?
> When stopped that abruptly, the process has no chance to clean up
> its pending state in shared memory. A fresh copy of shared memory
> is needed, so it is necessary to effectively do an immediate restart
> on the whole PostgreSQL instance.
Right. On seeing one child die unexpectedly, the postmaster forcibly
SIGQUITs all its other children and initiates a crash recovery sequence.
The reason for this is exactly that we can't trust the contents of
shared memory anymore. An example is that the dying backend may have
held some critical lock, which there is no way to release, so that every
other session will shortly be stuck anyway.
regards, tom lane
Got it, thanks, Kevin, Tom. So how about that this process that was in "notify interrupt waiting waiting" status after I SIGTERM'ed it. Is the double "waiting" expected? Aleksey
Aleksey Tsalolikhin <atsaloli.tech@gmail.com> writes:
> So how about that this process that was in "notify interrupt waiting
> waiting" status after I SIGTERM'ed it. Is the double "waiting"
> expected?
That sounded a bit fishy to me too. But unless you can reproduce it in
something newer than 8.4.x, nobody's likely to take much of an interest.
The LISTEN/NOTIFY infrastructure got completely rewritten in 9.0, so
any bugs in the legacy version are probably just going to get benign
neglect at this point ... especially if we don't know how to reproduce
them.
regards, tom lane
On Wed, Sep 5, 2012 at 10:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > That sounded a bit fishy to me too. But unless you can reproduce it in > something newer than 8.4.x, nobody's likely to take much of an interest. > The LISTEN/NOTIFY infrastructure got completely rewritten in 9.0, so > any bugs in the legacy version are probably just going to get benign > neglect at this point ... especially if we don't know how to reproduce > them. Got it, thanks, Tom! Will urge our shop to upgrade to 9.1. Best, -at