User-Level DTrace Probes in PostgreSQL

I'm excited to announce that PostgreSQL 8.2 now has user-level DTrace probes embedded in the source code. These probes will enable users to easily observe the behavior of PostgreSQL with simple D scripts, even in production. As you may already know, DTrace is being ported to FreeBSD and Mac OS X, so PostgreSQL users will be able to use the embedded probes on these OSes as well besides Solaris. My hope is that the presence of DTrace probes will not only help users identify one-off performance problem but will also enable developers to identify systemic performance and scalability issues on big multi-cpu/core/thread systems.

Here's the current list of available probes.
provider postgresql {
        probe transaction__start(int);
        probe transaction__commit(int);
        probe transaction__abort(int);
        probe lwlock__acquire(int, int);
        probe lwlock__release(int); 
        probe lwlock__startwait(int, int);
        probe lwlock__endwait(int, int);
        probe lwlock__condacquire(int, int);
        probe lwlock__condacquire__fail(int, int);
        probe lock__startwait(int, int);
        probe lock__endwait(int, int);

As you can see, the number of probes is small initially, but more will be added over time, and I encourage the community to identify areas in PostgreSQL where more observability is needed, for both developers and admins.

PostgreSQL runs on many operating systems, and the community is quite strict about keeping the code generic. To accomodate this, we created a higher level of abstraction whereby generic macro names are used instead of the DTrace specific macros. For example, we define the following macros PG_TRACE, PG_TRACE1, etc. which ultimately translate to DTRACE_PROBE, DTRACE_PROBE1, ... when used on system with DTrace. Doing this allow the tracing code to use generic macro names and these macros can be mapped to other tracing facilities for other operating systems.

The next few sections explains how to:
  • Compile PostgreSQL with DTrace
  • Use the existing DTrace probes
  • Add new DTrace probes

Compile PostgreSQL with DTrace

By default DTrace probes are disabled, and the user needs to explicitly tell the configure script to make the probes available in PostgreSQL. Certainly, enabling DTrace only makes sense on Operating Systems with DTrace facility. Currently DTrace is available on Solaris 10+ and soon on FreeBSD and Mac OS X.

To include DTrace probes in a 32 bit binary, specify --enable-dtrace to configure. For example:
        $ configure --enable-dtrace ...

To include DTrace probes in a 64 bit binary, specify --enable-dtrace and DTRACEFLAGS="-64" to configure. For example:

         Using gcc compiler:
        $ configure CC='gcc -m64' --enable-dtrace DTRACEFLAGS='-64' ...
         Using Sun compiler:
        $ configure CC='/path_to_sun_compiler/cc -xtarget=native64' --enable-dtrace DTRACEFLAGS='-64' ...

a) To successfully compile PostgreSQL 8.2 with --enable-dtrace, you need to run Solaris Express. The DTrace version in Solaris 10 (up until 11/06) does not allow probes to be added to static functions. This limitation will be fixed in the next update of Solaris 10.
b) When using DTRACEFLAGS='-64', you also have to tell the compiler to build 64 bit binary as shown in the configure lines above; otherwise, you will get compilation errors.

Use Existing DTrace Probes

Using the probes in PostgreSQL is similar to using probes in other DTrace providers. Below is an example of a simple D script using the transaction-start, transaction-commit, and transaction-abort probes. The script prints out the total number of started, committed, and aborted transactions.

#!/usr/sbin/dtrace -qs 

        @start["Start"] = count();
        self->ts  = timestamp;

        @abort["Abort"] = count();

        @commit["Commit"] = count();
        @time["Total time (ns)"] = sum(timestamp - self->ts);

Executing the above script produces the following output.

# ./txn_count.d `pgrep -n postgres`

  Start                                         71
  Commit                                   70
  Total time (ns)                        2312105013

A number of sample D scripts are available from the DTrace's PgFoundry project

To learn more about DTrace, refer to the HowTo and DTrace Guides.

Add New DTrace Probes

New DTrace probes can easily be added to PostgreSQL. For example, if you were to add transaction-start probe, follow these simple steps:

1) Add the probe definitions to src/backend/utils/probes.d
   provider postgresql {
        probe transaction__start(int);

When a dash (-) is used in the probe name, it needs to be converted to double underscores (__) in the probe definition file. So, the above probe is called transaction-start in the D script.

2) Add "PG_TRACE1 (transaction__start, s->transactionId);" to backend/access/transam/xact.c
   static void

         \* generate a new transaction id
        s->transactionId = GetNewTransactionId(false);


        PG_TRACE1 (transaction__start, s->transactionId);


a) PG_TRACE1 is mapped to DTRACE_PROBE1. See src/include/pg_trace.h
b) The provider name for all probes in PostgreSQL is called postgresql per the decision by the developers, so it's not specified in PG_TRACE. See src/include/pg_trace.h.
c) Make sure the data types in the probe definition match the argument passed to the probe. In this case s->transactionId has to be an integer (int).

When you have probes that might be useful to the community at large, send a proposal/patch to to get feedback from the developers.

3) Check to make sure the new probe is available

After recompiling, run the new binary, and as root, execute the following DTrace command to check that your newly added probe is available.

# dtrace -l -n transaction-start

More details which led to DTrace inclusion in PostgreSQL

1) Proposal submitted to the developer community.
2) Presented the proposal at the PostgreSQL Anniversary Summit (first developers conference). The timing of the conference was perfect, and I was fortunate to have the opportunity to present the proposal and demo'ed DTrace to a live audience. I think the discussion with the developers/hackers after the conference was key in solidifying the proposed implementation and getting the thumbs up for inclusion into 8.2.
3) Patch submitted and follow-on discussions
4) Patch was updated by Peter Eisentraut
5) Patch was finally committed by Peter Eisentraut


Many people from the community and at Sun provided excellent feedback on the proposal and implementation, but without the help for the following individuals, it would not have been possible to get DTrace into PostgreSQL.

Gavin Sherry - Gavin was the first person in the community to help us identify locations in PostgreSQL to insert probes. We initially wanted to create a demo, and with a short notice, Gavin made himself available to talk with us.

Tom Lane - At the PostgerSQL Anniverary Summit, Tom helped verify the probe locations and corrected a few of them and provide excellent feedback on how the framework should be implemented.

Peter Eisentraut - Peter stepped up to help incorporate DTrace into PostgreSQL build system. His help was invaluable in getting DTrace in 8.2 before code freeze.

Angelo Rajadurai - Angelo is a DTrace guru in MDE, and he was a great help in getting me up to speed with adding user-level probes and DTrace in general.

Adam Leventhal and Bryan Cantrill (The DTrace creators) - For making themselves available to answer questions and provide feedback.


This is waaaaay cool.

It sure seems like it'd be nice to have --enable-dtrace in the configure script to be on by default (if dtrace is present) rather than off, don't you think?

Posted by guest on September 14, 2006 at 01:21 PM CDT #

We did consider checking for the presence of DTrace and enable it by default, but there's an issue. DTrace was recently enhanced to allow probes to be placed in static functions, and this feature is only in Solaris Express currently, so to successfully compile PostgreSQL 8.2 with --enable-dtrace, you need to run Solaris Express, although the binary will work on Solaris 10.

Posted by Robert Lor on September 23, 2006 at 12:51 AM CDT #

Hrrm. I just performed a live upgrade to Solaris 11/06 (u3), but I still get the DTrace compile problem. If anybody reading this blog entry knows the rhyme/reason for this, I'd love to know. Thanks

Posted by michael on December 20, 2006 at 12:01 PM CST #

Current DTrace can't handle static functions. I also got "missing symbol errors" on 3 functions while linking. I've heard that future update will solve this.

Posted by Satoshi Nagayasu on January 07, 2007 at 01:06 AM CST #

Current DTrace can't handle static functions. I also got "missing symbol errors" on 3 functions while linking. I've heard that future update will solve this.

The documentation should probably be updated. It seems to indicate that Solaris/DTrace newer than 10 6/06 have the requisite features.

Posted by michael on January 08, 2007 at 10:21 AM CST #

Solaris 10 update 3 still fails with the same error message during compilation. Would there be a patch available that solves this problem?

Posted by Hans Nijbacker on January 18, 2007 at 08:52 PM CST #

My apologies for being on hiatus for the later part of December and January! Unfortunately the DTrace fix for static function didn't get into Solaris 10 11/06 (aka update 3). I will be in update 4.

Posted by Robert Lor on January 31, 2007 at 05:10 AM CST #


Posted by سلمان قنبری on July 17, 2010 at 06:53 PM CDT #;jsessionid=56b3ea91ca0f39d271fc3f81fc47?bug_id=6672627

You need to add a dummy statement below any dtrace probes that end a function. I used
int i = 0;

Posted by Adam on February 28, 2011 at 02:40 AM CST #

Post a Comment:
  • HTML Syntax: NOT allowed



« April 2014