MySQL Forums
Forum List  »  Performance

Re: query with bad performance after migration to mysql 5.6
Posted by: walter verdelocco
Date: December 10, 2015 08:09AM

Hi

here the optimizer trace with
optimizer_switch = 'block_nested_loop=off';



mysql> 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
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> set optimizer_switch = 'block_nested_loop=off';
Query OK, 0 rows affected (0.00 sec)

mysql> 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=off,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> SELECT @@global.optimizer_trace_max_mem_size;
+---------------------------------------+
| @@global.optimizer_trace_max_mem_size |
+---------------------------------------+
| 314572800 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> Set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT messaggio.id, messaggio.data, allegati.id FROM ( select m.id from messaggio m WHERE m.idarticolo='repnews_271330' AND m.idpadre=0 AND m.pubblica=1 ORDER BY m.data DESC LIMIT 50 ) as m_padre join messaggio on (m_padre.id=messaggio.idpadre OR m_padre.id=messaggio.id) left join allegati on messaggio.id = allegati.idpost ORDER BY messaggio.data DESC, messaggio.id desc, allegati.id asc;
+-------+------------+------+
| id | data | id |
+-------+------------+------+
| 20352 | 1169210692 | NULL |
.......
+-------+------------+------+
27 rows in set (29.84 sec)

mysql> select MISSING_BYTES_BEYOND_MAX_MEM_SIZE ,trace from Information_Schema.Optimizer_Trace;
| 0 | {
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select `m`.`id` AS `id` from `messaggio` `m` where ((`m`.`idarticolo` = 'repnews_271330') and (`m`.`idpadre` = 0) and (`m`.`pubblica` = 1)) order by `m`.`data` desc limit 50"
}
]
}
},
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `messaggio`.`id` AS `id`,`messaggio`.`data` AS `data`,`allegati`.`id` AS `id` from (((/* select#2 */ select `m`.`id` AS `id` from `messaggio` `m` where ((`m`.`idarticolo` = 'repnews_271330') and (`m`.`idpadre` = 0) and (`m`.`pubblica` = 1)) order by `m`.`data` desc limit 50) `m_padre` join `messaggio` on(((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`)))) left join `allegati` on((`messaggio`.`id` = `allegati`.`idpost`))) order by `messaggio`.`data` desc,`messaggio`.`id` desc,`allegati`.`id`"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"join_optimization": {
"select#": 2,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`m`.`idarticolo` = 'repnews_271330') and (`m`.`idpadre` = 0) and (`m`.`pubblica` = 1))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal('repnews_271330', `m`.`idarticolo`) and multiple equal(0, `m`.`idpadre`) and multiple equal(1, `m`.`pubblica`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal('repnews_271330', `m`.`idarticolo`) and multiple equal(0, `m`.`idpadre`) and multiple equal(1, `m`.`pubblica`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal('repnews_271330', `m`.`idarticolo`) and multiple equal(0, `m`.`idpadre`) and multiple equal(1, `m`.`pubblica`))"
}
]
}
},
{
"table_dependencies": [
{
"table": "`messaggio` `m`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`messaggio` `m`",
"field": "idpadre",
"equals": "0",
"null_rejecting": false
},
{
"table": "`messaggio` `m`",
"field": "idpadre",
"equals": "0",
"null_rejecting": false
},
{
"table": "`messaggio` `m`",
"field": "pubblica",
"equals": "1",
"null_rejecting": false
},
{
"table": "`messaggio` `m`",
"field": "idarticolo",
"equals": "'repnews_271330'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`messaggio` `m`",
"range_analysis": {
"table_scan": {
"rows": 2445095,
"cost": 567805
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idevento_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idpadre",
"usable": true,
"key_parts": [
"idpadre",
"id"
]
},
{
"index": "ideventoidpadre_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "ideventoidpadrepubblica_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idpadrepubblica_idx",
"usable": true,
"key_parts": [
"idpadre",
"pubblica",
"id"
]
},
{
"index": "ididpadrepubblica",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idUtente_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "data_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "nome",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idbrand_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idarticolo_idx",
"usable": true,
"key_parts": [
"idarticolo",
"id"
]
},
{
"index": "commento_ft_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "titolo_ft_idx",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idpadre",
"ranges": [
"0 <= idpadre <= 0"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1222547,
"cost": 1.47e6,
"chosen": false,
"cause": "cost"
},
{
"index": "idpadrepubblica_idx",
"ranges": [
"0 <= idpadre <= 0 AND 1 <= pubblica <= 1"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1222547,
"cost": 1.47e6,
"chosen": false,
"cause": "cost"
},
{
"index": "idarticolo_idx",
"ranges": [
"repnews_271330 <= idarticolo <= repnews_271330"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 26,
"cost": 32.21,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"intersecting_indices": [
{
"index": "idpadrepubblica_idx",
"index_scan_cost": 1791,
"cumulated_index_scan_cost": 1791,
"disk_sweep_cost": 278008,
"cumulated_total_cost": 279799,
"usable": true,
"matching_rows_now": 1.22e6,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "idarticolo_idx",
"index_scan_cost": 1.4032,
"cumulated_index_scan_cost": 1792.4,
"disk_sweep_cost": 257,
"cumulated_total_cost": 2049.4,
"usable": true,
"matching_rows_now": 13,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "idpadre",
"cumulated_total_cost": 2049.4,
"usable": false,
"cause": "does_not_reduce_cost_of_intersect"
}
],
"clustered_pk": {
"clustered_pk_added_to_intersect": false,
"cause": "no_clustered_pk_index"
},
"chosen": false,
"cause": "cost"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idarticolo_idx",
"rows": 26,
"ranges": [
"repnews_271330 <= idarticolo <= repnews_271330"
]
},
"rows_for_plan": 26,
"cost_for_plan": 32.21,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`messaggio` `m`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idpadre",
"rows": 1.22e6,
"cost": 480861,
"chosen": true
},
{
"access_type": "ref",
"index": "idpadrepubblica_idx",
"rows": 1.22e6,
"cost": 480861,
"chosen": false
},
{
"access_type": "ref",
"index": "idarticolo_idx",
"rows": 26,
"cost": 31.2,
"chosen": true
},
{
"access_type": "range",
"cause": "heuristic_index_cheaper",
"chosen": false
}
]
},
"cost_for_plan": 31.2,
"rows_for_plan": 26,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0) and (`m`.`idarticolo` = 'repnews_271330'))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`messaggio` `m`",
"attached": "((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0) and (`m`.`idarticolo` = 'repnews_271330'))"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`m`.`data` desc",
"items": [
{
"item": "`m`.`data`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`m`.`data` desc"
}
},
{
"refine_plan": [
{
"table": "`messaggio` `m`",
"pushed_index_condition": "(`m`.`idarticolo` = 'repnews_271330')",
"table_condition_attached": "((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0))"
}
]
},
{
"added_back_ref_condition": "((`m`.`idarticolo` <=> 'repnews_271330') and ((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0)))"
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`messaggio` `m`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idarticolo_idx",
"plan_changed": false
}
}
}
]
}
},
{
"transformations_to_nested_joins": {
"transformations": [
"JOIN_condition_to_WHERE",
"parenthesis_removal"
],
"expanded_query": "/* select#1 */ select `messaggio`.`id` AS `id`,`messaggio`.`data` AS `data`,`allegati`.`id` AS `id` from (/* select#2 */ select `m`.`id` AS `id` from `messaggio` `m` where ((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0) and (`m`.`idarticolo` = 'repnews_271330')) order by `m`.`data` desc limit 50) `m_padre` join `messaggio` left join `allegati` on((`messaggio`.`id` = `allegati`.`idpost`)) where ((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`)) order by `messaggio`.`data` desc,`messaggio`.`id` desc,`allegati`.`id`"
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(`m_padre`.`id`, `messaggio`.`idpadre`) or multiple equal(`m_padre`.`id`, `messaggio`.`id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(`m_padre`.`id`, `messaggio`.`idpadre`) or multiple equal(`m_padre`.`id`, `messaggio`.`id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(`m_padre`.`id`, `messaggio`.`idpadre`) or multiple equal(`m_padre`.`id`, `messaggio`.`id`))"
}
]
}
},
{
"table_dependencies": [
{
"table": " `m_padre`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`messaggio`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
},
{
"table": "`allegati`",
"row_may_be_null": true,
"map_bit": 2,
"depends_on_map_bits": [
1
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`allegati`",
"field": "idpost",
"equals": "`messaggio`.`id`",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": " `m_padre`",
"table_scan": {
"rows": 26,
"cost": 11
}
},
{
"table": "`messaggio`",
"table_scan": {
"rows": 2445095,
"cost": 78784
}
},
{
"table": "`allegati`",
"table_scan": {
"rows": 400357,
"cost": 5479
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": " `m_padre`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 26,
"cost": 16.5,
"chosen": true
}
]
},
"cost_for_plan": 16.5,
"rows_for_plan": 26,
"rest_of_plan": [
{
"plan_prefix": [
" `m_padre`"
],
"table": "`messaggio`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 2.45e6,
"cost": 1.48e7,
"chosen": true
}
]
},
"cost_for_plan": 1.48e7,
"rows_for_plan": 6.36e7,
"rest_of_plan": [
{
"plan_prefix": [
" `m_padre`",
"`messaggio`"
],
"table": "`allegati`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idpost_idx",
"rows": 1,
"cost": 6.36e7,
"chosen": true
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
]
},
"cost_for_plan": 9.1e7,
"rows_for_plan": 6.36e7,
"chosen": true
}
]
}
]
},
{
"plan_prefix": [
],
"table": "`messaggio`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 2.45e6,
"cost": 567803,
"chosen": true
}
]
},
"cost_for_plan": 567803,
"rows_for_plan": 2.45e6,
"rest_of_plan": [
{
"plan_prefix": [
"`messaggio`"
],
"table": " `m_padre`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 26,
"cost": 4.03e7,
"chosen": true
}
]
},
"cost_for_plan": 4.09e7,
"rows_for_plan": 6.36e7,
"rest_of_plan": [
{
"plan_prefix": [
"`messaggio`",
" `m_padre`"
],
"table": "`allegati`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idpost_idx",
"rows": 1,
"cost": 6.36e7,
"chosen": true
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
]
},
"cost_for_plan": 1.17e8,
"rows_for_plan": 6.36e7,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": [
"`messaggio`"
],
"table": "`allegati`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idpost_idx",
"rows": 1,
"cost": 2.45e6,
"chosen": true
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
]
},
"cost_for_plan": 3.5e6,
"rows_for_plan": 2.45e6,
"rest_of_plan": [
{
"plan_prefix": [
"`messaggio`",
"`allegati`"
],
"table": " `m_padre`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 26,
"cost": 4.03e7,
"chosen": true
}
]
},
"cost_for_plan": 4.38e7,
"rows_for_plan": 6.36e7,
"chosen": true
}
]
}
]
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`messaggio`",
"attached": null
},
{
"table": "`allegati`",
"attached": null
},
{
"table": " `m_padre`",
"attached": "((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`))"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`messaggio`.`data` desc,`messaggio`.`id` desc,`allegati`.`id`",
"items": [
{
"item": "`messaggio`.`data`"
},
{
"item": "`messaggio`.`id`"
},
{
"item": "`allegati`.`id`"
}
],
"resulting_clause_is_simple": false,
"resulting_clause": "`messaggio`.`data` desc,`messaggio`.`id` desc,`allegati`.`id`"
}
},
{
"refine_plan": [
{
"table": "`messaggio`",
"access_type": "table_scan"
},
{
"table": "`allegati`"
},
{
"table": " `m_padre`",
"access_type": "table_scan"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"table": " `m_padre`",
"row_length": 5,
"key_length": 0,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 3355443
}
}
},
{
"join_execution": {
"select#": 2,
"steps": [
{
"filesort_information": [
{
"direction": "desc",
"table": "`messaggio` `m`",
"field": "data"
}
],
"filesort_priority_queue_optimization": {
"limit": 50,
"rows_estimate": 26963694,
"row_size": 150,
"memory_available": 2097152,
"chosen": true
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 27,
"examined_rows": 27,
"number_of_tmp_files": 0,
"sort_buffer_size": 8058,
"sort_mode": "<sort_key, additional_fields>"
}
}
]
}
},
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"row_length": 13,
"key_length": 0,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 1290555
}
}
},
{
"filesort_information": [
{
"direction": "desc",
"table": "intermediate_tmp_table",
"field": "data"
},
{
"direction": "desc",
"table": "intermediate_tmp_table",
"field": "id"
},
{
"direction": "asc",
"table": "intermediate_tmp_table",
"field": "id"
}
],
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 27,
"examined_rows": 27,
"number_of_tmp_files": 0,
"sort_buffer_size": 1073,
"sort_mode": "<sort_key, rowid>"
}
}
]
}
}
]
} |


w.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: query with bad performance after migration to mysql 5.6
1279
December 10, 2015 08:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.