| PostgreSQL 8.4devel Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 26. Monitoring Database Activity | Fast Forward | Next |
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. Currently, this facility is primarily intended for use by database developers, as it requires substantial familiarity with the code.
A number of probes or trace points are already inserted into the source code. By default these probes are not compiled into the binary, and the user needs to explicitly tell the configure script to make the probes available in PostgreSQL.
Currently, only the DTrace utility is supported, which is available on Solaris Express, Solaris 10, and Mac OS X Leopard. It is expected that DTrace will be available in the future on FreeBSD. 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 15.5 for further information.
A few standard probes are provided in the source code (of course, more can be added as needed for a particular problem). These are shown in Table 26-3.
Table 26-3. Built-in Probes
| Name | Parameters | Overview |
|---|---|---|
| transaction-start | (int transactionId) | The start of a new transaction. |
| transaction-commit | (int transactionId) | The successful completion of a transaction. |
| transaction-abort | (int transactionId) | The unsuccessful completion of a transaction. |
| lwlock-acquire | (int lockid, int mode) | An LWLock has been acquired. |
| lwlock-release | (int lockid, int mode) | An LWLock has been released. |
| lwlock-startwait | (int lockid, int mode) | An LWLock was not immediately available and a backend has begun to wait for the lock to become available. |
| lwlock-endwait | (int lockid, int mode) | A backend has been released from its wait for an LWLock. |
| lwlock-condacquire | (int lockid, int mode) | An LWLock was successfully acquired when the caller specified no waiting. |
| lwlock-condacquire-fail | (int lockid, int mode) | An LWLock was not successfully acquired when the caller specified no waiting. |
| lock-startwait | (int locktag_field2, int lockmode) | A request for a heavyweight lock (lmgr lock) has begun to wait because the lock is not available. |
| lock-endwait | (int locktag_field2, int lockmode) | A request for a heavyweight lock (lmgr lock) has finished waiting (i.e., has acquired the lock). |
The example below shows a DTrace script for analyzing transaction counts on 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 <PID> ^C Start 71 Commit 70 Total time (ns) 2312105013
You should remember that DTrace scripts need to be carefully written and debugged prior to their use, 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
Insert a one-line 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.
Name the probe transaction-start and give it a parameter of type integer (type of transaction ID)
Add "probe transaction__start(int);" to src/backend/utils/probes.d, and it should look like the following:
provider postgresql {
...
probe transaction__start(int);
...
};Note the use of the double underline in the probe name. In the DTrace script, the double underline needs to be replaced with a hyphen.
You should take care that the data types specified for the probe arguments match the data types of the variables used in the macro. Otherwise, you will get compilation errors.
At compile time, transaction__start is converted to a macro called TRACE_POSTGRESQL_TRANSACTION_START, and it resides in src/backend/utils/probes.h. Before recompiling, add the single line macro to the appropriate location in the source code. In this case, it looks like the following:
static void
StartTransaction(void)
{
...
/*
* Advertise it in the proc array. We assume assignment of
* LocalTransactionID is atomic, and the backendId should be set already.
*/
Assert(MyProc->backendId == vxid.backendId);
MyProc->lxid = vxid.localTransactionId;
TRACE_POSTGRESQL_TRANSACTION_START(vxid.localTransactionId);
...
} Note how the transaction ID is made available to the dynamic tracing utility.
After recompiling and running the new binary, check that your newly added probe is available by executing the following DTrace command, and you should see similar output.
# dtrace -ln transaction-start ID PROVIDER MODULE FUNCTION NAME 18705 postgresql49878 postgres StartTransactionCommand transaction-start 18755 postgresql49877 postgres StartTransactionCommand transaction-start 18805 postgresql49876 postgres StartTransactionCommand transaction-start 18855 postgresql49875 postgres StartTransactionCommand transaction-start 18986 postgresql49873 postgres StartTransactionCommand transaction-start