public inbox for [email protected]
help / color / mirror / Atom feedpgstat_snap - create adhoc pg_stat_statements snapshots
2+ messages / 1 participants
[nested] [flat]
* pgstat_snap - create adhoc pg_stat_statements snapshots
@ 2025-06-25 14:55 raphi <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: raphi @ 2025-06-25 14:55 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pgstat_snap - create adhoc pg_stat_statements snapshots
@ 2025-07-10 21:53 raphi <[email protected]>
parent: raphi <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: raphi @ 2025-07-10 21:53 UTC (permalink / raw)
To: [email protected]
Hi all,
After some feedback I changed the script into an extension. Just copy
the files to the extension directory and load it with:
create extension pgstat_snap;
It can be installed in any schema and does not require super user
rights, only requirement is that the pg_stat_statement extension is loaded.
Usage is more or less the same, I've written a detailed description for
how to work with the extension on my github.io:
https://raphideb.github.io/postgres/pgstat_snap/#drill-down
The extension replaced the script and is available here:
https://github.com/raphideb/pgstat_snap
have fun ;)
raphi
Am 25.06.2025 um 16:55 schrieb raphi:
> 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
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-07-10 21:53 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-25 14:55 pgstat_snap - create adhoc pg_stat_statements snapshots raphi <[email protected]>
2025-07-10 21:53 ` raphi <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox