Hi
We have good API and tools for monitoring slow queries. What is very good.
But I miss a monitoring fast queries what is usually major number and where relatively small slowdown can to produce unhappy latencies on user interface. More, the slowdowns here can shows some issues of database health - bloated tables, indexes, overloading, ..
Because these queries are usually fast, the proposed interface should not to add any hard overhead, and it should not be too complex, because simple things are just better.
My idea is collect few metrics for any query in local memory - when query tracing will be enabled. Now I am thinking mainly about:
* session start time
* transaction start time
* query start time
* query signature
* planning interval
* lock interval
* execution interval
* finish time
* query status
.. maybe more
These metrics can be stored in local memory and I think so collecting these numbers should be pretty fast. Some of mentioned metrics can be taken now, but more than one hood should be assigned.
When query will be finished - then some new hook can be executed, and there can be a access to mentioned metrics. The hook should be evaluated under working transaction or with own transaction if previous query fails. This API should to work with failed, cancelled, cancelled by timeout queries too.
Maybe similar hooks can be after transaction, and after session - where some metrics can be processed before will be replaced for new transaction or lost by disconnect.
What do you think about this proposal?
Regards
Pavel