pg_wait_sampling
pg_wait_sampling copied to clipboard
postgrespro
additional dimensions
The only dimensions captured right now for wait events are pid and queryid. It would be helpful to capture additional dimensions for troubleshooting issues.
roleId and databaseId should be easy to grab from PGPROC while in pgws_should_sample_proc (corresponding with usesysid and datid in pg_stat_activity). lockGroupLeader->pid would be useful for sessions executing parallel queries.
There are also a number of fields from the backend status that would be very useful as dimensions: the application name, client addr/host/port and backend/bgworker type
Finally, (backend) proc_start_timestamp would be useful because technically this is needed together with pid to uniquely identify sessions (and disambiguate a new connection that reuses a PID)
And activity_start_timestamp would be useful to identify if the same query showing up in repeated samples is a single execution, or multiple executions of the same query
oh - and of course - the actual query text would be useful in some cases! (pgstat_clip_activity(beentry->st_activity_raw))
Might be able to copy code from pg_stat_get_activity() if needed
Greetings
Those are very interesting suggestions, but currently we don't know how to add those columns (not in the implementation way). If we try to add those to profile we would break our aggregation in profile - instead of two dimensions we would have, like, 8-10 and our profile will have several orders of magnitude more rows in profile (with most of them having count=1)
Hey everyone that's interesting coincidence, I also wanted similar enhancement to explain my idea better I created this PR https://github.com/postgrespro/pg_wait_sampling/pull/95
if you thing it's good idea, I would add code for history and probably profile
FWIW, I'm most interested in the history - sometimes pulling it out to an external system, sometimes directly analyzing it. So I don't feel like it's a requirement to add these fields to the profile.
also note that while it takes a very different approach to implementation, the extension https://github.com/pgsentinel/pgsentinel has all of these fields (and i have used that extension successfully)
We will add new columns that were discussed here (maybe all of them, maybe some of them, maybe also some additional ones) to pg_wait_sampling_current/_history/_profile ourselves after we decide on a way to turn them on/off if needed (probably some kind of GUC, but the details are still being worked on)
pgsentinel is an interesting extension, thanks for telling about it!
I will keep you updated
@ardentperf, @DmitryNFomin, you are welcome to review my PR that adds new dimensions to pg_wait_sampling's views (for some details why not all fields that were suggested here are implemented look at the PR description) - #97