PostgreSQL User-Space Markers
Newer versions of PostgreSQL include support for SystemTap user-space markers. The PostgreSQL user-space markers are enabled in Fedora 11 and Red Hat Enterprise Linux 6. The markers allow SystemTap to probe various PostgreSQL events including the start of a query, the completion of a query and lock operations. You can refer to the documentation in Monitoring Database Activity for a list of probe points available. You can list the available probe points with the following command:
stap -L 'process("postgres").mark("*")' | sort
Using PostgreSQL User-Space Markers
To use the PostgreSQL user-space markers on a Fedora you will need:
- RPMs:
- postgresql
- postgresql-server
postgresql-debuginfo (only required in versions built with older versions of SystemTap)
- systemtap
- Membership to the stapdev group (or root access)
- A uprobes module built for the currently running kernel
(SystemTap will print instruction on how to build the uprobes module if one is not currently available)
Examples and Demonstrations of Markers
Some short SystemTap example scripts for PostgreSQL:
postgresql-locks.stp summarizes the lock contention
postgresql-query.stp summarizes the query time for each process
postgresql-slowq.stp records querys requiring more than a specified time
A video demonstration of the PostgreSQL user-space markers.