Generic Monitoring Framework Proposal - Mailing list pgsql-hackers

Motivation:
----------

The main goal for this Generic Monitoring Framework is to provide a common interface for adding instrumentation points
orprobes to
 
Postgres so its behavior can be easily observed by developers and administrators even in production systems. This
frameworkwill allow Postgres to use the appropriate
 
monitoring/tracing facility provided by each OS. For example, Solaris and FreeBSD will use DTrace, and other OSes can
usetheir respective tool.
 

What is DTrace?
--------------

Some of you may have heard about or used DTrace already. In a nutshell, DTrace is a comprehensive dynamic tracing
facilitythat is built into
 
Solaris and FreeBSD (mostly working) that can be used by administrators and developers on live production systems to
examinethe behavior
 
of both user programs and of the operating system. 

DTrace can help answer difficult questions about the OS and the application itself. For example, you may want to ask:

- Show all functions that get invoked (userland & kernel) and execution time when my function foo() is called. Seeing
thepath a function 
 
takes into the kernel may provide clues for performance tuning.
- Show how many times a particular lock is acquired and how long it's held. This can help identity contentions in the
system.

The best way to appreciate DTrace capabilities is by seeing a demo or through hands-on experience, and I plan to show
someinteresting
 
demos at the PG Summit.

There are a numer of docs on Dtrace, and here's a quick start doc and a complete reference guide.
http://www.sun.com/software/solaris/howtoguides/dtracehowto.jsp
http://docs.sun.com/app/docs/doc/817-6223

Here is a recent DTrace for FreeBSD status
http://marc.theaimsgroup.com/?l=freebsd-current&m=114854018213275&w=2

Open source apps that provide user level probes (bottom of page)
http://uadmin.blogspot.com/2006/05/what-is-dtrace.html


Proposed Solution:
----------------

This solution is actually quite simple and non-intrusive.

1. Define macros PG_TRACE, PG_TRACE1, etc, in a new header file
called pg_trace.h with multiple #if defined(xxx) sections for Solaris,
FreeBSD, Linux, etc, and add pg_trace.h to c.h which is included in postgres.h
and included by every C file.

The macros will have the following format:

PG_TRACE[n](module_name, probe_name [, arg1, ..., arg5]) 

module_name = Name to identify PG module such as pg_backend, pg_psql, pg_plpgsql, etc
probe_name  = Probe name such as transaction_start, lwlock_acquire, etc
arg1..arg5  = Any args to pass to the probe such as txn id, lock id, etc    

2. Map PG_TRACE, PG_TRACE1, etc, to macros or functions appropriate for each OS.
For OSes that don't have suitable tracing facility, just map the macros to nothing - doing this will not have any
affecton performance or
 
existing behavior.

Sample of pg_trace.h

#if defined(sun) || defined(FreeBSD)

#include <sys/sdt.h>
#define PG_TRACE        DTRACE_PROBE
#define PG_TRACE1      DTRACE_PROBE1
...
#define PG_TRACE5      DTRACE_PROBE5

#elif defined(__linux__) || defined(_AIX) || defined(__sgi) ...

/* Map the macros to no-ops */
#define PG_TRACE(module, name)
#define PG_TRACE1(module, name, arg1)
...
#define PG_TRACE5(module, name, arg1, arg2, arg3, arg4, arg5)

#endif

3. Add any file(s) to support the particular OS tracing facility

4. Update the Makefiles as necessary for each OS

How to add probes:
-----------------

To add a probe, just add a one line macro in the appropriate location in the source. Here's an example of two probes,
onewith no argument
 
and the other with 2 arguments:

PG_TRACE (pg_backend, fsync_start);
PG_TRACE2 (pg_backend, lwlock_acquire, lockid, mode);

If there are enough probes embedded in PG, its behavior can be easily observed.

With the help of Gavin Sherry, we have added about 20 probes, and Gavin has suggested a number of other interesting
areasfor additional probes.
 
Pervasive has also added some probes to PG 8.0.4 and posted the patch on http://pgfoundry.org/projects/dtrace/.  I hope
tocombine the probes
 
using this generic framework for 8.1.4, and make it available for folks to try.

Since my knowledge of the PG source code is limited, I'm looking for assistance from experts to hep identify some new
interestingprobe points.
 


How to use probes:
----------------

For DTrace, probes can be enabled using a D script. When the probes are not enabled, there is absolutely no performance
hitwhatsoever. 
 
Here is a simple example to print out the number of LWLock counts for each PG process.

test.d

#!/usr/sbin/dtrace -s
pg_backend*:::lwlock-acquire
{    @foo[pid] = count();
}

dtrace:::END {    printf("\n%10s %15s\n", "PID", "Count");    printa("%10d %@15d\n",@foo);
}

# ./test.d
   PID           Count  1438               28  1447           7240  1448           9675  1449         11972


I have a prototype working, so if anyone wants to try it, I can provide a patch or give access to my test system.

This is a proposal, so comments, suggestions, feedbacks are certainly welcome. 

Regards,
Robert