Thread

connection logging dtrace probe

Robert Treat <xzilla@users.sourceforge.net> — 2009-02-12T22:43:08Z

I whipped up a quick dtrace probe for one of our servers to monitor connection 
attempts.  My goal was to monitor for any connection attempts from a specific 
role within the database.  Unfortunatly you can't set logging of connections 
for a specific user, and logging all connections on that machine would be 
quite the logfile bloater... enter dtrace. With the probe, I can do something 
like this:

-bash-3.00$ /opt/csw/bin/sudo dtrace -n 'postgresql*:::connection 
{printf("connection attempt: %s@%s\n",copyinstr(arg0),copyinstr(arg1)) }' | 
grep robert
dtrace: description 'postgresql*:::connection ' matched 5 probes
  2  18984            ServerLoop:connection connection attempt: robert@robert
  2  16222            ServerLoop:connection connection attempt: robert@robert
  1  16876            ServerLoop:connection connection attempt: robert@pagila

which can be piped to logfile or whatever. I'm attaching a patch against 8.4 
as an idea of what I've implemented (actual implementation was against a 
custom build) but should be close to working (don't have a working pg repo on 
any solaris machines atm). Any feedback appreciated (mostly wondering about 
probe name or location). TIA

-- 
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com

Re: connection logging dtrace probe

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> — 2009-02-16T05:54:28Z

Hi,

Robert Treat <xzilla@users.sourceforge.net> wrote:

> I whipped up a quick dtrace probe for one of our servers to monitor connection 
> attempts.  My goal was to monitor for any connection attempts from a specific 
> role within the database.

How about adding probes not only for connection attempts but also for
connection performance? The name would be CONNECTION_[START|DONE] like
other existing probes.

I received a report from my client that Postgres didn't return responce
of heatbeat connection requests for *1 minute*, but there are few methods
to dig it now...

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center