Dynamic Tracing

27.4.1. Compiling for Dynamic Tracing

By default, probes are not available, so you will need to explicitly tell the configure script to make the probes available in PostgreSQL . To include DTrace support specify --enable-dtrace to configure. See Section 15.4 for further information.

27.4.2. Built-in Probes

A number of standard probes are provided in the source code, as shown in Table 27-15 ; Table 27-16 shows the types used in the probes. More probes can certainly be added to enhance PostgreSQL 's observability.

27.4.3. Using Probes

The example below shows a DTrace script for analyzing transaction counts in the system, as an alternative to snapshotting pg_stat_database before and after a performance test:

#!/usr/sbin/dtrace -qs

postgresql$1:::transaction-start
{
      @start["Start"] = count();
      self->ts  = timestamp;
}

postgresql$1:::transaction-abort
{
      @abort["Abort"] = count();
}

postgresql$1:::transaction-commit
/self->ts/
{
      @commit["Commit"] = count();
      @time["Total time (ns)"] = sum(timestamp - self->ts);
      self->ts=0;
}

When executed, the example D script gives output such as:

# ./txn_count.d `pgrep -n postgres` or ./txn_count.d 
^C

Start                                          71
Commit                                         70
Total time (ns)                        2312105013

You should remember that DTrace scripts need to be carefully written and debugged, otherwise the trace information collected might be meaningless. In most cases where problems are found it is the instrumentation that is at fault, not the underlying system. When discussing information found using dynamic tracing, be sure to enclose the script used to allow that too to be checked and discussed.

More example scripts can be found in the PgFoundry dtrace project .

27.4.4. Defining New Probes

New probes can be defined within the code wherever the developer desires, though this will require a recompilation. Below are the steps for inserting new probes:

There are a few things to be careful about when adding trace macros to the C code:

• You should take care that the data types specified for a probe's parameters match the data types of the variables used in the macro. Otherwise, you will get compilation errors.

• On most platforms, if PostgreSQL is built with --enable-dtrace , the arguments to a trace macro will be evaluated whenever control passes through the macro, even if no tracing is being done . This is usually not worth worrying about if you are just reporting the values of a few local variables. But beware of putting expensive function calls into the arguments. If you need to do that, consider protecting the macro with a check to see if the trace is actually enabled:

  if (TRACE_POSTGRESQL_TRANSACTION_START_ENABLED())
      TRACE_POSTGRESQL_TRANSACTION_START(some_function(...));

  Each trace macro has a corresponding ENABLED macro.