PGTracer is a collection of tools to trace queries, execution plans and more in PostgreSQL®, using eBPF.
PGTracer offers a way to instrument PostgreSQL, using the Linux eBPF facility. As it does advanced memory access, it needs the PostgreSQL debug symbols to resolve symbols and offsets in structs.
This project provides tools that allow you to gain deep insights into PostgreSQL’s locking activities and troubleshoot locking-related issues (e.g., performance problems or deadlocks).
pg_lock_tracer - is a lock tracer for PostgreSQL.
pg_lw_lock_tracer - is a tracer for PostgreSQL lightweight locks (LWLocks).
animate_lock_graph - creates animated locks graphs based on the pg_lock_tracer output.
Note: Most of these tools employ the BPF / eBPF (Extended Berkeley Packet Filter) technology. At the moment, PostgreSQL 12, 13, 14, and 15 are supported (see additional information below).
root@iZ2ze8fs957s9uk6mrjdi8Z:~# pg_lock_tracer -x /usr/pgsql-15/bin/postgres -p 1455 ===> Compiling BPF program ===> Attaching BPF probes ===> Ready to trace queries 256574124323 [Pid 1455] Query begin 'select * from t1 where id = 99;' 256574149788 [Pid 1455] Transaction begin 256574158097 [Pid 1455] Accept invalidation messages 256574249581 [Pid 1455] Table open (by range value) .t1 AccessShareLock 256574256778 [Pid 1455] Accept invalidation messages 256574266066 [Pid 1455] Table open 1259 AccessShareLock 256574272434 [Pid 1455] Lock object 1259 AccessShareLock 256574281101 [Pid 1455] Lock granted (fastpath) 1259 AccessShareLock 256574287386 [Pid 1455] Lock granted (local) 1259 AccessShareLock (Already hold local 0) 256574292847 [Pid 1455] Accept invalidation messages 256574298780 [Pid 1455] Lock was acquired in 26346 ns 256574302740 [Pid 1455] Lock object 2663 AccessShareLock 256574308188 [Pid 1455] Lock granted (fastpath) 2663 AccessShareLock 256574313346 [Pid 1455] Lock granted (local) 2663 AccessShareLock (Already hold local 0) 256574319175 [Pid 1455] Accept invalidation messages 256574325472 [Pid 1455] Lock was acquired in 22732 ns 256574392573 [Pid 1455] Lock ungranted (fastpath) 2663 AccessShareLock 256574402855 [Pid 1455] Lock ungranted (local) 2663 AccessShareLock (Hold local 0) 256574410605 [Pid 1455] Table close 1259 AccessShareLock 256574420131 [Pid 1455] Lock ungranted (fastpath) 1259 AccessShareLock 256574427096 [Pid 1455] Lock ungranted (local) 1259 AccessShareLock (Hold local 0) 256574431193 [Pid 1455] Lock object 16384 AccessShareLock 256574440514 [Pid 1455] Lock granted (fastpath) 16384 AccessShareLock 256574447612 [Pid 1455] Lock granted (local) 16384 AccessShareLock (Already hold local 0) 256574454227 [Pid 1455] Accept invalidation messages 256574461670 [Pid 1455] Lock was acquired in 30477 ns 256574466583 [Pid 1455] Table open 1259 AccessShareLock 256574473439 [Pid 1455] Lock object 1259 AccessShareLock ...
root@iZ2ze8fs957s9uk6mrjdi8Z:~# pg_lock_tracer -x /usr/pgsql-15/bin/postgres -p 1455 --statistic ===> Compiling BPF program ===> Attaching BPF probes ===> Ready to trace queries 305594728170 [Pid 1455] Query begin 'select * from t1 where id = 99;' 305594743006 [Pid 1455] Transaction begin 305594751718 [Pid 1455] Accept invalidation messages 305594803943 [Pid 1455] Table open (by range value) .t1 AccessShareLock 305594810926 [Pid 1455] Accept invalidation messages 305594823343 [Pid 1455] Lock object 16384 AccessShareLock 305594832930 [Pid 1455] Lock granted (fastpath) 16384 AccessShareLock 305594839275 [Pid 1455] Lock granted (local) 16384 AccessShareLock (Already hold local 0) 305594844630 [Pid 1455] Accept invalidation messages 305594850512 [Pid 1455] Lock was acquired in 27169 ns 305594856780 [Pid 1455] Table close 16384 NoLock 305594885491 [Pid 1455] Table open 16384 NoLock 305594890861 [Pid 1455] Table close 16384 NoLock 305594896054 [Pid 1455] Table open 16384 NoLock 305594901948 [Pid 1455] Table close 16384 NoLock 305594923263 [Pid 1455] Table open 16384 NoLock 305594935576 [Pid 1455] Table close 16384 NoLock 305595011436 [Pid 1455] Table open 16384 NoLock 305639384394 [Pid 1455] Table close 16384 NoLock 305639417928 [Pid 1455] Transaction commit 305639433088 [Pid 1455] Lock ungranted (fastpath) 16384 AccessShareLock 305639439356 [Pid 1455] Lock ungranted (local) 16384 AccessShareLock (Hold local 1) 305639456018 [Pid 1455] Query done
Lock statistics: ================
Locks per OID +-----------+----------+------------------------------+ | Lock Name | Requests | Total Lock Request Time (ns) | +-----------+----------+------------------------------+ | 16384 | 1 | 27169 | +-----------+----------+------------------------------+
Lock types +-----------------+---------------------------+ | Lock Type | Number of requested locks | +-----------------+---------------------------+ | AccessShareLock | 1 | +-----------------+---------------------------+
Processing of cache invalidation messages (e.g., AcceptInvalidationMessages)
ERROR
Error related events (e.g., bpf_errstart)
除此之外，pg_lock_tracer还支持五种”事件”的追踪👆🏻
另外一个提供的工具是pg_lw_lock_trace，顾名思义，用于追踪LWLock的
1 2 3 4 5 6 7 8 9 10 11 12
examples: # Trace the LW locks of the PID 1234 pg_lw_lock_tracer -p 1234 # Trace the LW locks of the PIDs 1234 and 5678 pg_lw_lock_tracer -p 1234 -p 5678 # Trace the LW locks of the PID 1234 and be verbose pg_lw_lock_tracer -p 1234 -v # Trace the LW locks of the PID 1234 and collect statistics pg_lw_lock_tracer -p 1234 -v --statistics
