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. Details: 1. probably only long-query-time should enable the history dump, not log-queries-not-using-indexes 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. References: 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
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.