Description:
PERFORMANCE_SCHEMA provides meaningful timing information for various operations performed in servicing a given connection, but there's no information exposed for the timestamp when the connection was established. This would be useful information for answering queries such as, "what percentage of connection time is spent doing X?". Right now, this can only be roughly approximated by summing the SUM_TIMER_WAIT values for all instruments per thread, but that's subject to which instruments are enabled and timed (as well as what instrumentation exists).
How to repeat:
See above.
Try executing the following SQL batch file:
SELECT ps_helper.format_time(SUM(sum_timer_wait)) total_time
FROM events_waits_summary_by_thread_by_event_name ews
JOIN threads t
ON (t.thread_id = ews.thread_id)
WHERE t.processlist_id = CONNECTION_ID()\G
SELECT SLEEP(30);
SELECT ps_helper.format_time(SUM(sum_timer_wait)) total_time
FROM events_waits_summary_by_thread_by_event_name ews
JOIN threads t
ON (t.thread_id = ews.thread_id)
WHERE t.processlist_id = CONNECTION_ID()\G
Then enable timing for the "stage/sql/User sleep" instrument and re-run, notice that time spent in SLEEP() is included in second execution, but not the original.
Suggested fix:
Add and expose a TIMESTAMP (or connection_duration column) to indicate when a connection was established. A connection_statistics_duration column might be better, and would be the later of when the connection was established (take COM_CHANGE_USER into consideration) or when the P_S table was last truncated.