pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jerry Sievers <jerry@jerrysievers.com>
Cc: pgsql-admin@postgresql.org
Cc: Peter Brant <Peter.Brant@wicourts.gov>
Subject: Re: pg_stat_activity showing non-existent processes
Date: Mon, 03 Apr 2006 17:57:53 -0500
Message-ID: <44316221.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <14779.1144083156@sss.pgh.pa.us>
References: <m3veu2qzh8.fsf@prod01.jerrysievers.com>
	<m3veu2qzh8.fsf@prod01.jerrysievers.com>
	<22874.1143340808@sss.pgh.pa.us>
	<443109B6.EE98.0025.0@wicourts.gov>
	<443109B6.EE98.0025.0@wicourts.gov>
	<14779.1144083156@sss.pgh.pa.us>

>>> On Mon, Apr 3, 2006 at 11:52 am, in message
<14779.1144083156@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote: 
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> Is there any way to tweak this in favor of more accurate
information,
>> even if has a performance cost?  We're finding that during normal
>> operations we're not seeing most connections added to the
>> pg_stat_activity table.  We would like to be able to count on
accurate
>> information there.
> 
> That's basically a non- starter because of the delay in reporting
from
> the stats collector process (ie, even if the information was
"completely
> accurate" it'd still be stale by the time that your code gets its
hands
> on it).  I think you'd be talking about a complete redesign of the
stats
> subsystem to be able to use it that way.

We want this for our monitoring software, to raise an alert when the
connection pool diverges from its nominal configuration beyond
prescribed limits or in excess of a prescribed duration.  What we're
looking for is not necessarily a table which is accurate immediately,
but one which won't entirely miss a connection.  Even then, if it only
misbehaves under extreme load, that would be OK; such extreme usage
might be worthy of note in and of itself.

Since we have converted to PostgreSQL we have not had this monitoring,
and folks are nervous that we will not detect a struggling middle tier
before it fails.  (Not something that happens often, but we really hate
having users tell us that something is broken, versus spotting the
impending failure and correcting it before it fails.)

> Having said that, though, I'd be pretty surprised if the stats
subsystem
> was dropping more than a small fraction of messages ---  I would
think
> that could only occur under very heavy load, and if that's your
normal
> operating state then it's time to upgrade your hardware ;- ).

We have a pair of database servers for our transaction repository. 
Each has four Xeon processors.  One of these is Windows, one is Linux. 
On the Windows machine, I see 10% CPU utilization.  On the Linux machine
I see a load average of 0.30.  The Linux machine seems to be very
reliable about showing the connections.  The Windows machine, when I
refresh a 20-connection pool, I either get no connections showing, or
only a few.

>  Maybe you
> should investigate a bit more closely to find out why it's dropping
so
> much.

It is probably related to something we've been seeing in the PostgreSQL
logs on the Windows servers:

[2006-04-03 08:28:25.990 ] 2072 FATAL:  could not read from statistics
collector pipe: No error
[2006-04-03 08:28:26.068 ] 2012 LOG:  statistics collector process (PID
3268) was terminated by signal 1

We're going to patch to try to capture more info from WinSock.

In src/port/pipe.c we plan to add before return ret in piperead():

if (ret == SOCKET_ERROR)
{
       ereport(LOG, (errmsg_internal("SOCKET ERROR: %ui",
WSAGetLastError())));
}

I hope to post more info, and possibly a patch, tomorrow.

-Kevin




view thread (12+ messages)  latest in thread

Message-ID: <44316221.EE98.0025.0@wicourts.gov>
Permalink:  ../44316221.EE98.0025.0@wicourts.gov/
Also on:    postgresql.org/message-id/44316221.EE98.0025.0@wicourts.gov

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: Kevin.Grittner@wicourts.gov, tgl@sss.pgh.pa.us, jerry@jerrysievers.com, Peter.Brant@wicourts.gov
  Subject: Re: pg_stat_activity showing non-existent processes
  In-Reply-To: <44316221.EE98.0025.0@wicourts.gov>
  References: <m3veu2qzh8.fsf@prod01.jerrysievers.com> <22874.1143340808@sss.pgh.pa.us> <443109B6.EE98.0025.0@wicourts.gov> <14779.1144083156@sss.pgh.pa.us> <44316221.EE98.0025.0@wicourts.gov>

* 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