Bug #69880 Track and expose connection creation timestamp
Submitted: 31 Jul 2013 16:14
Reporter: Todd Farmer (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S4 (Feature request)
Version:5.6.11 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any

[31 Jul 2013 16:14] Todd Farmer
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.