Investigate details about CrateDB jobs

[amotl]

Dear @BaurzhanSakhariev,

at https://github.com/crate/support/issues/701#issuecomment-3280488967, you said:

Took a random job, found corresponding Session with matching mostRecentJobID and checked Session.connectionProperties: It's POSTGRES.

This sounds interesting. Can you share a guideline about how to investigate this? Do you think this task applicable for corresponding tool support?

With kind regards,
Andreas.

/cc @WalBeh

[BaurzhanSakhariev]

This sounds interesting. Can you share a guideline about how to investigate this? Do you think this task applicable for corresponding tool support?

OQL queries for fetching queries and their durations and also operations are posted in the ticket.

For Session I did it in UI (manually did the join of Session and operation). Also, aggregation for summing up all sizes of Task-s somehow didn't work for me (https://stackoverflow.com/questions/6518230/how-to-sum-retainedheapsize-with-oql-in-the-eclipse-memory-analyzer-tool) even though OQL supports aggregations.

Do you think this task applicable for corresponding tool support?

I think we could use https://codeberg.org/mfussenegger/hprofdap and integrate our usual support routine to the tool.

Also, if OQL can't aggregate retained sizes, we could use the tool to do it, probably can even pass a param
(smth like duration_threshold) which would be our upper limit when we consider a query is not stuck, say 10 mins or maybe 30 mins and then tool would report all queries running longer than duration_threshold, group by HTTP/PG, aggregate sum of all referenced Task instances and some other info that can be useful

I will try to generalize my usual routine and use CLI tool I mentioned above (instead of MAT),
so that we can back it in into our toolkit and post my update later (not today, have some other things to look into)

Also, I manually compute duration based on heap dump time

 - Name: crate-data-hot-473e51a5-3437-41b0-b62d-c067e5a02c06-6.hprof.gz
  - Last Modified: 2025-09-11_09_17_30 UTC -

and use it in OQL queries, which is also annoying, would be good to pass heap dump timestamp to the tool and let it convert human readable time to UTC timestamp.