Lets say wanted to understand how understand Postgres, beyond the regular trace file. Or you have this nasty performance issue that makes no sense to you and the regular logging and tracing are not giving the results, where do you go? Well, you could always try running perf and pipe the export to Brendan Gregg’s flamegraph tool to get a better understanding. If that doesn’t work for you, don’t worry PostgreSQL has you covered. PostgreSQL facilitates the use of dynamic tracing within the database. Dynamic tracing supports on PostgreSQL supports 2 utilities: Dtrace and Systemtap.
PostgreSQL dynamic tracing comes with probes/trace points already build in to the code, however in most cases these probes are not compiled into PostgreSQL so you need explicitly tell your configure script you want to have these probes compiled with PostgreSQL. To compile PostgreSQL for Systemtap you need to give the configure script the --enable-dtrace flag.
Before you start compiling your PostgreSQL deployment, you must make sure that you have the development tools installed as well as a set of header files and systemtap dtrace compatibility packages
dnf -y group install "Development Tools"
dnf -y install wget libtermcap-devel readline-devel systemtap-sdt-devel systemd-devel
Then next step would be to download the PostgreSQL as normal, in this case I am downloading 14.4
wget https://ftp.postgresql.org/pub/source/v14.4/postgresql-14.4.tar.gz
tar -xvf postgresql-14.4.tar.gz
cd postgresql-14.4/
Now we have to source code we can start compiling, besides the already mentioned I am configuring PostgreSQL with some additional flags as personal preference and for use of use. --enable-debug compiles PostgreSQL with all debugging symbols to make life easier if you want attache a debugger at some point. The --enable-profiling flag is enabled just in case I want to do some profiling. Enabling the –with-systemd does the obvious thing, personal preference to let PostgreSQL be controlled through systemd.
./configure --enable-dtrace --enable-debug --enable-profiling --with-systemd
make
make install
After this we have our postgres binaries ready and we can continue configuring PostgreSQL as you normally would do by creating a postgres user, creating a postgres systemd service, create your initDB etc.
To test if your PostgreSQL now has the probes configured you can run a quick test. Open one session as root and run the systemtap online below and make sure it points to your postgres binary and keep this session open. In a second session run a simple query, eg. a select from pg_stat_activity. After this query is done you should see a backtrace in your root session. You can cancel out of this stap command now if you want.
[root@postgres-host ~]# stap --ldd -ve 'probe process("/usr/local/pgsql/bin/postgres").mark("transaction__start") { printf ("PID: %d, CPU: %d, BACKTRACE^ %s\n", pid(), cpu(), print_ubacktrace()) }'
Pass 1: parsed user script and 480 library scripts using 295156virt/95944res/17692shr/79972data kb, in 150usr/20sys/171real ms.
Pass 2: analyzed script: 1 probe, 3 functions, 0 embeds, 0 globals using 296740virt/98392res/18544shr/81556data kb, in 10usr/0sys/6real ms.
Pass 3: using cached /root/.systemtap/cache/69/stap_69a99e95568af9ea57019817040be984_2398.c
Pass 4: using cached /root/.systemtap/cache/69/stap_69a99e95568af9ea57019817040be984_2398.ko
Pass 5: starting run.
0x574bb0 : StartTransaction+0x1a0/0x2d0 [/usr/local/pgsql/bin/postgres]
0x576025 : StartTransactionCommand+0x55/0x60 [/usr/local/pgsql/bin/postgres]
0x7d7255 : start_xact_command+0x95/0xc0 [/usr/local/pgsql/bin/postgres]
0x7d9030 : exec_simple_query+0x50/0x620 [/usr/local/pgsql/bin/postgres]
0x7daaad : PostgresMain+0x14ad/0x2320 [/usr/local/pgsql/bin/postgres]
0x75d40a : ServerLoop+0xd9a/0xfa0 [/usr/local/pgsql/bin/postgres]
0x75e1a4 : PostmasterMain+0xb14/0xd80 [/usr/local/pgsql/bin/postgres]
0x4ec195 : main+0x445/0x4b0 [/usr/local/pgsql/bin/postgres]
0x7feef3ea7cf3 : __libc_start_main+0xf3/0x1c0 [/usr/lib64/libc-2.28.so]
0x4ec22e : _start+0x2e/0x30 [/usr/local/pgsql/bin/postgres]
PID: 5755, CPU: 3, BACKTRACE^
^CPass 5: run completed in 20usr/40sys/8911real ms.
This systemtap oneliner dumps a user backtrace for all sessions, in this particular sampling window we only captured my PostgreSQL session. We can verify it is the same, the statement that I did in the other session that triggered this backtrace tells us the PID of that session:
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
5755
(1 row)
The PID that pg_backend_pid() gives is 5755, which is the same as what systemtap gives. The simple one-liner marks a static probe in the PostgreSQL, in this case its marks the transaction_start call in PostgreSQL. We then print the backtrace for the stack for the process with print_ubacktrace function.
We can expand a bit on this by adding variables, operators such as counts so make systemtap a bit more flexible in its use cases. Have a look at the following simple systemtap script:
global count_calls
global calls_pids
global hist
probe process("/usr/local/pgsql/bin/postgres").function(@1)
{
count_calls[pid()]++
}
probe end
{
printf("\n")
foreach (count in count_calls)
printf( "PID: %d - Call count: %d\n", count, count_calls[count] )
}
This scripts probes a user-space function in the PostgreSQL process. The function that we are calling is @1 which is means we are expecting a command line argument here. Sytemtap makes a difference between string arguments and integer arguments. A string argument is being revered to with a @, and integer needs a $. The script then continues by counting the times the function name that was given through the command line argument in variable count_calls with the PID that executed that function.
}[root@postgres-host vagrant]# stap test.stp StartTransaction
^C
PID: 6733 - Call count: 3
[root@postgres-host vagrant]#
These are just some of the basic things you can with Systemtap to monitor and trace your PostgreSQL environment. These scripts can be expanded with additional build-in functions or even expand with embedded your custom C functions into Systemtap if you run Systemtap in guru (-g) mode but that is for another blogpost. Have fun playing around with Systemtap and PostgreSQL.
Future Veterans 