Description:
The optimizer has a very useful feature in optimizer_switch, in that in upgrade testing you find a query that causes a regression, you can often disable the specific new optimization that is causing the query to become slow; thus restoring performance.
This feature becomes even more important with the new query hint infrastructure in 5.7, where many of the optimizer_switches can be disabled on a per-query basis.
What my specific FR is, is that because there are ~20 switches, it is very difficult to be able to tell which ones need to be disabled in order to change how the query executes. I would like to see this information included in EXPLAIN FORMAT=JSON
How to repeat:
1) Attempt upgrade to new major version
2) Notice some queries are slower
3) Attempt to find which optimizer switch you need to disable to restore query performance:
mysql [localhost] {msandbox} (test) > select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)
Suggested fix:
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
“optimizations_used”: [“use_index_extensions”, “derived_merge”], <-- NEW
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "t1",
"access_type": "ref”,
Having this information in the EXPLAIN FORMAT=JSON will assist automated tools that can replay queries on an old and new version server in parallel and report on improvements Vs regressions (improving the MySQL upgrade story significantly).
So for example:
- I can replay a query log of real traffic on new server version, finding all queries which will now use derived_merge=on in MySQL 5.7. I can then compare these with my MySQL 5.6 server and check that performance is acceptable.
- If I am considering disabling ICP (due to regressions), I can find all queries that are currently using it and inspect further.
Description: The optimizer has a very useful feature in optimizer_switch, in that in upgrade testing you find a query that causes a regression, you can often disable the specific new optimization that is causing the query to become slow; thus restoring performance. This feature becomes even more important with the new query hint infrastructure in 5.7, where many of the optimizer_switches can be disabled on a per-query basis. What my specific FR is, is that because there are ~20 switches, it is very difficult to be able to tell which ones need to be disabled in order to change how the query executes. I would like to see this information included in EXPLAIN FORMAT=JSON How to repeat: 1) Attempt upgrade to new major version 2) Notice some queries are slower 3) Attempt to find which optimizer switch you need to disable to restore query performance: mysql [localhost] {msandbox} (test) > select @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on 1 row in set (0.00 sec) Suggested fix: *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, “optimizations_used”: [“use_index_extensions”, “derived_merge”], <-- NEW "cost_info": { "query_cost": "1.20" }, "table": { "table_name": "t1", "access_type": "ref”, Having this information in the EXPLAIN FORMAT=JSON will assist automated tools that can replay queries on an old and new version server in parallel and report on improvements Vs regressions (improving the MySQL upgrade story significantly). So for example: - I can replay a query log of real traffic on new server version, finding all queries which will now use derived_merge=on in MySQL 5.7. I can then compare these with my MySQL 5.6 server and check that performance is acceptable. - If I am considering disabling ICP (due to regressions), I can find all queries that are currently using it and inspect further.