MySQL Bugs: #81020: performance_schema: Please add optimizer usage statistics

Bug #81020	performance_schema: Please add optimizer usage statistics
Submitted:	9 Apr 2016 6:34	Modified:	14 Apr 2016 8:13
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Optimizer, performance_schema, statistics

Description:
Currently it is not possible to see any metrics on any of the optimizer_switch settings used inside MySQL. As new functionality becomes available in the optimizer it would be useful to know if:
(1) you have enabled the feature
(2) if it is being used, and ideally
(3) if it works better than before

but we're blind and the only way to see this information now is to run queries by hand, change the optimizer settings, run them again and compare the EXPLAIN outputs.

That is fine but is not generic enough for a lot of people.

How to repeat:
See above.

Suggested fix:
So please add statistics in performance_schema which indicate this missing data for any optimizer settings:
- optimizer_switch settings
- optimizer_hint settings

probably including counts of times used and also count of latency and/or rows affected etc.
I am not sure of the full details and would be happy to discuss further if there are various ways of looking at this problem.

The bottom line is I'd like to be able to see if settings are used and hopefully be able to compare statistics to see if they work "better". That might involve comparing similarly configured servers using different settings but that would also be fine.

Hello Simon,

Thank you for the feature request!

Thanks,
Umesh

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0