Latest MySQL Statistics for Slow Queries
To log slow query is the important longstanding technique of footage slow query, so the database administrator can control which queries are in the utmost need for optimization. Since MySQL 5.6, it must some outspread been outshined by the Routine Schema which has minor directly above and thus allows gathering statistics about all requests. The slow query log has one main benefit though: the data is persevered. In MySQL 8.0.14 which was newly out, there is an development for the slow query log: extra statistics about the logged requests.
The additional info is not logged by default. To allow the feature, allow the log_slow_extra choice:
mysql> SET PERSIST log_slow_extra = ON;
Here, SET PERSIST is castoff, so the arrangement change is kept on when MySQL is resumed. If you just need to try the feature, you can custom SET GLOBAL, then choose later whether you need to keep it allowed.
That is all that is essential. You can now complete a “slow” query and yield a look at the logged data. An informal way to accomplish a slow query is to perform DO SLEEP(...) where you can substitute ... with the number of instants you want to snooze. However, that is not a very thought-provoking request. Additional option is to inferior long_query_time to confirm your query is captured If you usual long_query_time to zero, all requests are logged:
Now SET SESSION is used so only the existing connection is artificial.
Finally, execute a query:
The resultant log slow query best is:
The additional data can be a slight solid to see due to the organizing of the blog – the additional fields are:
As judgement, here is the info for the same request with log_slow_extra = OFF: