WL#4906: performance schema histories in a slow log

Affects: Server-9.x   —   Status: Un-Assigned   —   Priority: Medium

often a performance optimization project "according to some advice by people
with real-world experience" consists of

1. determining what application/action exactly is slow
2. finding out the conditions when it is slow (every second week ? when the load
is higher than 100tps ? when a daily cron job is running?)
3. setting up performance monitoring to collect data for the given application
in a given time frame (it may be non-trivial to make sure that instrumentation
is activated at the exact point in time when the problematic query starts)

On the other hand, MySQL has a tool that automatically logs all "slow" queries -
with slow log one does not have to hunt for slow queries and enable general log
for them.

Expanding on this concept, and taking into account that performance monitoring
overhead is low (thus the instrumentation can be enabled all the time), a
following seems usable:

1. performance monitoring is always enabled
2. if a query is written to a slow log, the content of EVENTS_WAITS_HISTORY_LONG
(or EVENTS_WAITS_HISTORY, whatever available) is dumped into a slow log.

As compared to the above "hunting for the performance data" technique, this will
automatically generate properly scoped performance traces for all slow queries
on their *first* occurrence.


1. probably only long-query-time should enable the history dump, not
2. only the data for a current query (WHERE query_id) should be dumped
3. there's no guarantee that the dump will contain history for the whole query -
it depends on the size of the corresponding history table. But it's ok, if
long-query-time is, say, one second, and the history table is big enough to hold
the events from the last three seconds of query execution - it'll show at least
two seconds of extra execution time that should've not had happened. There is no
need to have the complete history to be able to optimize the query.


dev-private email thread "Re: Newly Added (by Sergei): performance schema
histories in a slow log (4906)"
[MySQL intranet] /secure/mailarchive/mail.php?folder=4&mail=28613