Profiling with perf
From PostgreSQL wiki
How to profile
perf offers two major modes: record then report, or real-time "top" mode. Both are useful in different situations.
Record then report
To profile the system for a specific length of time, for example 60 seconds:
perf record -a -g -s sleep 60
To profile the system for the duration of a command:
perf record -a -g -s -- pg_ctl -D pgdata start
To profile the system until cancelled, just omit any command:
perf record -a -g
By default perf record will generate a perf.data file in the current working directory.
See "what to record" for ways to narrow what you record.
By default perf report will look for perf.data in the current working directory.
perf report -n
You can include call-graph output, if captured, with:
perf report -g
and a variety of options are available to sort, filter and aggregate the output; see the documentation for perf.
In real time
To profile system-wide in real time:
To profile in real time without accumulating stats, i.e. instantaneous profiling:
perf top -z
You can use the usual target-selection options with perf top, but it's most commonly used for system wide profiling with perf top -a.
What to profile
perf lets you choose what to profile in a variety of ways, the most useful of which are:
-a whole system, all CPUs -p pid one process ID -u user processes for one user, e.g. "postgres"
You can use these commands with perf record and perf top.
Including user-space stacks
Newer perf releases support capturing the whole user-space call stack, not just the top function on the stack. This lets it provide extremely useful call graphs. Use the -g dwarf option to perf record to enable this, and the -g option to perf report to display the results.
On x64 hosts, you must either rebuild PostgreSQL with ./configure CFLAGS="-fno-omit-frame-pointer -ggdb" ... or use a version of perf built with libunwind support.
More detail in Tracing PostgreSQL with perf.
Less common reports
Annotated source code, from a recorded session:
perf annotate -l -P
Trace output, from a recorded session:
perf script -L
Benchmarking and statistics
If you're interested in particular tracepoints or statistics, the perf stat command is also useful. For example, the following will count fsync and write calls, and block device activity, during a command. It will run the command five times and summarize the results.
sudo perf stat -e block:block_rq_*,syscalls:sys_enter_write,syscalls:sys_enter_fsync -a -r 5 -- psql -q -U postgres postgres -c "drop table if exists x; create table x as select a FROM generate_series(1,1000000) a;";
To learn more about using perf for tracing see tracing PostgreSQL with perf.
Availability of perf
The perf tool is available on recent Linux distributions such as:
- RedHat Linux Enterprise 6: perf package
- Debian Squeeze: add the linux-tools package.
It may be built and installed from the linux source code in the tools/perf subdirectory. You must use a perf that matches your running kernel version.