Skip site navigation (1) Skip section navigation (2)

Re: Stats Collector Oddity

From: Christopher Browne <cbbrowne(at)acm(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Stats Collector Oddity
Date: 2006-12-22 03:12:35
Message-ID: 874prolj30.fsf@wolfe.cbbrowne.com (view raw or flat)
Thread:
Lists: pgsql-hackers
A long time ago, in a galaxy far, far away, tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
> Chris Browne <cbbrowne(at)acm(dot)org> writes:
>> There isn't any way, short of restarting the postmaster, to get rid of
>> that PID, is there?
>
> The entry will get overwritten when that BackendId slot gets re-used,
> so just starting enough concurrent backends should do it.  (Since
> incoming backends always take the lowest free slot, the fact that the
> dead entry has persisted awhile means that it must have a number higher
> than your normal number of concurrent sessions ... which is evidence
> in favor of the idea that it happened during a load spike ...)

Cool.  I started up a nice little bunch of psql sessions in the
background, and then once they were all up, shut down my shell
session, thereby eliminating them.  And that did, indeed, clear out
that pg_stat_activity entry.

... And five minutes later, Nagios sent me message indicating that
node had recovered from having an "ancient" open connection.

I'll re-add a few gratuitous details here in the hopes that that makes
this easily findable if anyone else should search for the issue...

The Problem:
 - pg_stat_activity was reporting an elderly transaction in progress

 - that backend process wasn't running anymore

 - pg_stat_activity *was* reporting other legitimate activity; this
   was not the scenario where it had gotten deranged (normally due to
   excessive load)

 - Per Tom's comments, there evidently *was* some load spike where
   the closing of this particular connection did not get logged by
   the stats collector

The Solution: 

 - We needed to roll the stats collector through a bunch of its slots
   in order to clean the apparently-still-populated entry out.

 - Ran, in a shell:
     for i in `seq 100`; do
         psql &
     done

   That left 100 psql sessions in the background, all connected to the
   database backend.

 - Closed the shell.  That then HUPped the 100 psql sessions.

That got the offending pg_stat_activity entry cleared out.
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/finances.html
"Temporary tattoos  are a CRITICAL  ELEMENT  of our security strategy.
To suggest otherwise is sheer lunacy."  -- Reid Fleming, cDc

In response to

pgsql-hackers by date

Next:From: ITAGAKI TakahiroDate: 2006-12-22 03:19:49
Subject: Re: Load distributed checkpoint
Previous:From: ITAGAKI TakahiroDate: 2006-12-22 02:48:43
Subject: Re: [PATCHES] Load distributed checkpoint patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group