Hi all,
I've created a script which helps me as a DBA to trace down performance
problems, especially on clusters with multiple databases installed. It
creates timestamped copies of pg_stat_statements and pg_stat_activity
and provides two views that contain the execution delta of every
query/dbid between timestamps, e.g. how many rows were affected. Basic
workflow is:
- Install:
pgsql
\i pgstat_snap.sql
- collect, e.g. every 1 second 60 times:
CALL pgstat_snap.create_snapshot(1, 60);
- analyze - the _d columsn are the difference between snapshots
select * from pgstat_snap_diff order by 1;
snapshot_time queryid query datname
usename wait_event_type wait_event rows_d calls_d exec_ms_d
sb_hit_d sb_read_d sb_dirt_d sb_write_d
2025-03-25 11:00:19 4380144606300689468 UPDATE pgbench_tell
postgres postgres Lock transactionid 4485 4485
986.262098 22827 0 0 0
2025-03-25 11:00:20 4380144606300689468 UPDATE pgbench_tell
postgres postgres Lock transactionid 1204 1204
228.822413 6115 0 0 0
2025-03-25 11:00:20 7073332947325598809 UPDATE pgbench_bran
postgres postgres Lock transactionid 1204 1204
1758.190499 5655 0 0 0
2025-03-25 11:00:21 7073332947325598809 UPDATE pgbench_bran
postgres postgres Lock transactionid 1273 1273
2009.227575 6024 0 0 0
2025-03-25 11:00:22 2931033680287349001 UPDATE pgbench_acco
postgres postgres Client ClientRead 9377 9377
1818.464415 66121 3699 7358 35
2025-03-25 11:00:22 7073332947325598809 UPDATE pgbench_bran
postgres postgres Lock transactionid 1356 1356
1659.806856 6341 0 0 0
2025-03-25 11:00:23 7073332947325598809 UPDATE pgbench_bran
postgres postgres Lock transactionid 1168 1168
1697.322874 5484 0 0 0
- when done, uninstall:
SELECT pgstat_snap.uninstall();
DROP SCHEMA cascade;
The wait_event is a bit wonky for queries that are executed multiple
times per interval, it's the one the query was suffering from at the
time when the snapshot was taken.
The script and full documentation is here:
https://github.com/raphideb/pgstat_snap
Thought this might be useful for some, please let me know if there's
something I could improve.
have fun
raphi
