PostgreSQL provides facilities to support dynamic tracing of the database server. This allows an external utility to be called at specific points in the code and thereby trace execution.
A number of probes or trace points are already inserted into the source code. These probes are intended to be used by database developers and administrators. By default the probes are not compiled into PostgreSQL; the user needs to explicitly tell the configure script to make the probes available.
Currently, the DTrace utility is supported, which, at the time of this writing, is available on Solaris, macOS, FreeBSD, NetBSD, and Oracle Linux. The SystemTap project for Linux provides a DTrace equivalent and can also be used. Supporting other dynamic tracing utilities is theoretically possible by changing the definitions for the macros in src/include/utils/probes.h
.
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 17.4 for further information.
A number of standard probes are provided in the source code, as shown in Table 28.47; Table 28.48 shows the types used in the probes. More probes can certainly be added to enhance PostgreSQL's observability.
Name | Parameters | Description |
---|---|---|
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:
When executed, the example D script gives output such as:
SystemTap uses a different notation for trace scripts than DTrace does, even though the underlying trace points are compatible. One point worth noting is that at this writing, SystemTap scripts must reference probe names using double underscores in place of hyphens. This is expected to be fixed in future SystemTap releases.
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.
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:
Decide on probe names and data to be made available through the probes
Add the probe definitions to src/backend/utils/probes.d
Include pg_trace.h
if it is not already present in the module(s) containing the probe points, and insert TRACE_POSTGRESQL
probe macros at the desired locations in the source code
Recompile and verify that the new probes are available
Example: Here is an example of how you would add a probe to trace all new transactions by transaction ID.
Decide that the probe will be named transaction-start
and requires a parameter of type LocalTransactionId
Add the probe definition to src/backend/utils/probes.d
:
Note the use of the double underline in the probe name. In a DTrace script using the probe, the double underline needs to be replaced with a hyphen, so transaction-start
is the name to document for users.
At compile time, transaction__start
is converted to a macro called TRACE_POSTGRESQL_TRANSACTION_START
(notice the underscores are single here), which is available by including pg_trace.h
. Add the macro call to the appropriate location in the source code. In this case, it looks like the following:
After recompiling and running the new binary, check that your newly added probe is available by executing the following DTrace command. You should see similar output:
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:
Each trace macro has a corresponding ENABLED
macro.
Type | Definition |
---|---|