revision-id: 3bbb233bf5c3de11a623905bd1678ffe5553b6e9 (mariadb-10.4.11-425-g3bbb233bf5c) parent(s): 63e8f07e468c5cf44a0910e955887d01f87e7d96 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-19 19:11:21 +0300 message: MDEV-9750: Quick memory exhaustion with 'extended_keys=on', part#6 Move the heuristic as requested. Make it use the user-settable parameter --- mysql-test/main/range_notembedded.result | 27 +++++++++------- sql/opt_range.cc | 55 ++++++++++++++++++++++++++------ 2 files changed, 61 insertions(+), 21 deletions(-) diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result index 7d5dc41d7ed..82ae2eb3cd4 100644 --- a/mysql-test/main/range_notembedded.result +++ b/mysql-test/main/range_notembedded.result @@ -87,29 +87,32 @@ left(@json, 2500) [ { - "enforce_sel_arg_weight_limit": + "sel_arg_weight_heuristic": { - "index": "key1", - "old_weight": 110, - "new_weight": 10 + "key1_field": "kp1", + "key2_field": "kp2", + "key1_weight": 10, + "key2_weight": 10 } }, { - "enforce_sel_arg_weight_limit": + "sel_arg_weight_heuristic": { - "index": "key1", - "old_weight": 110, - "new_weight": 10 + "key1_field": "kp1", + "key2_field": "kp3", + "key1_weight": 10, + "key2_weight": 10 } }, { - "enforce_sel_arg_weight_limit": + "sel_arg_weight_heuristic": { - "index": "key1", - "old_weight": 110, - "new_weight": 10 + "key1_field": "kp1", + "key2_field": "kp4", + "key1_weight": 10, + "key2_weight": 10 } } ] diff --git a/sql/opt_range.cc b/sql/opt_range.cc index b67a85457c5..df9d6ba68e0 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -418,6 +418,9 @@ static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts); static SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, SEL_ARG *sel_arg); +static +bool sel_arg_and_weight_heuristic(RANGE_OPT_PARAM *param, SEL_ARG *key1, + SEL_ARG *key2); #include "opt_range_mrr.cc" @@ -9723,6 +9726,9 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, SEL_ARG *next; ulong use_count=key1->use_count; + if (sel_arg_and_weight_heuristic(param, key1, key2)) + return key1; + if (key1->elements != 1) { key2->use_count+=key1->elements-1; //psergey: why we don't count that key1 has n-k-p? @@ -9800,14 +9806,7 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) } // key1->part < key2->part - /* - Do not combine the trees if their total weight is likely to exceed the - MAX_WEIGHT. - (It is possible that key1 has next_key_part that has empty overlap with - key2. In this case, the combined tree will have a smaller weight than we - predict. We assume this is rare.) - */ - if (key1->weight + key1->elements*key2->weight > SEL_ARG::MAX_WEIGHT) + if (sel_arg_and_weight_heuristic(param, key1, key2)) return key1; key1->use_count--; @@ -10771,6 +10770,9 @@ void prune_sel_arg_graph(SEL_ARG *sel_arg, uint max_part) We start with maximum used keypart and then remove one keypart after another until the graph's weight is within the limit. + @seealso + sel_arg_and_weight_heuristic(); + @return tree pointer The tree after processing, NULL If it was not possible to reduce the weight of the tree below the @@ -10784,6 +10786,7 @@ SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, !param->thd->variables.optimizer_max_sel_arg_weight) return sel_arg; + Field *field= sel_arg->field; uint weight1= sel_arg->weight; while (1) @@ -10808,7 +10811,11 @@ SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, { Json_writer_object wrapper(param->thd); Json_writer_object obj(param->thd, "enforce_sel_arg_weight_limit"); - obj.add("index", param->table->key_info[param->real_keynr[keyno]].name); + if (param->using_real_indexes) + obj.add("index", param->table->key_info[param->real_keynr[keyno]].name); + else + obj.add("pseudo_index", field->field_name); + obj.add("old_weight", (longlong)weight1); obj.add("new_weight", (longlong)weight2); } @@ -10816,6 +10823,36 @@ SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, } +/* + @detail + Do not combine the trees if their total weight is likely to exceed the + MAX_WEIGHT. + (It is possible that key1 has next_key_part that has empty overlap with + key2. In this case, the combined tree will have a smaller weight than we + predict. We assume this is rare.) +*/ + +static +bool sel_arg_and_weight_heuristic(RANGE_OPT_PARAM *param, SEL_ARG *key1, + SEL_ARG *key2) +{ + DBUG_ASSERT(key1->part < key2->part); + + ulong max_weight= param->thd->variables.optimizer_max_sel_arg_weight; + if (max_weight && key1->weight + key1->elements*key2->weight > max_weight) + { + Json_writer_object wrapper(param->thd); + Json_writer_object obj(param->thd, "sel_arg_weight_heuristic"); + obj.add("key1_field", key1->field->field_name); + obj.add("key2_field", key2->field->field_name); + obj.add("key1_weight", (longlong)key1->weight); + obj.add("key2_weight", (longlong)key2->weight); + return true; // Discard key2 + } + return false; +} + + SEL_ARG * SEL_ARG::insert(SEL_ARG *key) {