revision-id: 00675d2596ff3b15f676a2ee329ea6045adb08b4 (mariadb-10.4.11-422-g00675d2596f) parent(s): 805fcc4c202d601b6bf4f2a39aa15c1859c6f4db author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-18 19:07:42 +0300 message: MDEV-9750: Quick memory exhaustion with 'extended_keys=on' ... Part #3: Introduce a user-visible @@optimizer_max_sel_arg_weight to control the optimization. --- mysql-test/main/range.result | 91 ++++++++++++++++++++++++++++++++++++++++++++ mysql-test/main/range.test | 30 +++++++++++++++ sql/opt_range.cc | 16 +++++--- sql/sql_class.h | 1 + sql/sys_vars.cc | 6 +++ 5 files changed, 138 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 9800d931dd6..c5178885d7f 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3146,6 +3146,9 @@ kp4 int, key key1(kp1, kp2, kp3,kp4) ); insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); +show variables like 'optimizer_max_sel_arg_weight'; +Variable_name Value +optimizer_max_sel_arg_weight 32000 set @tmp_9750=@@optimizer_trace; set optimizer_trace=1; explain select * from t1 where @@ -3179,6 +3182,94 @@ left(@json, 500) "(1,1,6) <= (kp1,kp2,kp3) <= (1,1,6)", "(1,1,7) <= (kp1,kp2,kp3) <= (1,1,7)", " +## Repeat the above with low max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=20; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10) and +kp2 in (1,2,3,4,5,6,7,8,9,10) and +kp3 in (1,2,3,4,5,6,7,8,9,10) and +kp4 in (1,2,3,4,5,6,7,8,9,10) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +from information_schema.optimizer_trace); +select left(@json, 500); +left(@json, 500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1) <= (kp1) <= (1)", + "(2) <= (kp1) <= (2)", + "(3) <= (kp1) <= (3)", + "(4) <= (kp1) <= (4)", + "(5) <= (kp1) <= (5)", + "(6) <= (kp1) <= (6)", + "(7) <= (kp1) <= (7)", + "(8) <= (kp1) <= (8)", + "(9) <= (kp1) <= (9)", + "(10) <= (kp1) <= (10)" + +## Repeat the above with a bit higher max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=120; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10) and +kp2 in (1,2,3,4,5,6,7,8,9,10) and +kp3 in (1,2,3,4,5,6,7,8,9,10) and +kp4 in (1,2,3,4,5,6,7,8,9,10) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +from information_schema.optimizer_trace); +select left(@json, 1500); +left(@json, 1500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1,1) <= (kp1,kp2) <= (1,1)", + "(1,2) <= (kp1,kp2) <= (1,2)", + "(1,3) <= (kp1,kp2) <= (1,3)", + "(1,4) <= (kp1,kp2) <= (1,4)", + "(1,5) <= (kp1,kp2) <= (1,5)", + "(1,6) <= (kp1,kp2) <= (1,6)", + "(1,7) <= (kp1,kp2) <= (1,7)", + "(1,8) <= (kp1,kp2) <= (1,8)", + "(1,9) <= (kp1,kp2) <= (1,9)", + "(1,10) <= (kp1,kp2) <= (1,10)", + "(2,1) <= (kp1,kp2) <= (2,1)", + "(2,2) <= (kp1,kp2) <= (2,2)", + "(2,3) <= (kp1,kp2) <= (2,3)", + "(2,4) <= (kp1,kp2) <= (2,4)", + "(2,5) <= (kp1,kp2) <= (2,5)", + "(2,6) <= (kp1,kp2) <= (2,6)", + "(2,7) <= (kp1,kp2) <= (2,7)", + "(2,8) <= (kp1,kp2) <= (2,8)", + "(2,9) <= (kp1,kp2) <= (2,9)", + "(2,10) <= (kp1,kp2) <= (2,10)", + "(3,1) <= (kp1,kp2) <= (3,1)", + "(3,2) <= (kp1,kp2) <= (3,2)", + "(3,3) <= (kp1,kp2) <= (3,3)", + "(3,4) <= (kp1,kp2) <= (3,4)", + "(3,5) <= (kp1,kp2) <= (3,5)", + "(3,6) <= (kp1,kp2) <= (3,6)", + "(3,7) <= (kp1,kp2) <= (3,7)", + "(3,8) <= (kp1,kp2) <= (3,8)", + "(3,9) <= (kp1,kp2) <= (3,9)", + "(3,10) <= (kp1,kp2 +set optimizer_max_sel_arg_weight= @tmp9750_weight; set optimizer_trace=@tmp_9750; drop table t1; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 642ae3f8a08..a9f374afa3d 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2133,6 +2133,8 @@ create table t1 ( insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); +show variables like 'optimizer_max_sel_arg_weight'; + # 20 * 20 * 20 *20 = 400*400 = 160,000 ranges set @tmp_9750=@@optimizer_trace; set optimizer_trace=1; @@ -2150,9 +2152,37 @@ set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternative --echo # to keep the number of ranges at manageable level: select left(@json, 500); +--echo ## Repeat the above with low max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=20; +explain select * from t1 where + kp1 in (1,2,3,4,5,6,7,8,9,10) and + kp2 in (1,2,3,4,5,6,7,8,9,10) and + kp3 in (1,2,3,4,5,6,7,8,9,10) and + kp4 in (1,2,3,4,5,6,7,8,9,10) +; +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) + from information_schema.optimizer_trace); +select left(@json, 500); + +--echo ## Repeat the above with a bit higher max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=120; +explain select * from t1 where + kp1 in (1,2,3,4,5,6,7,8,9,10) and + kp2 in (1,2,3,4,5,6,7,8,9,10) and + kp3 in (1,2,3,4,5,6,7,8,9,10) and + kp4 in (1,2,3,4,5,6,7,8,9,10) +; +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) + from information_schema.optimizer_trace); +select left(@json, 1500); + +set optimizer_max_sel_arg_weight= @tmp9750_weight; set optimizer_trace=@tmp_9750; drop table t1; + set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index aef9654c646..26d76075e60 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -415,7 +415,7 @@ static bool null_part_in_key(KEY_PART *key_part, const uchar *key, uint length); static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts); -static SEL_ARG *enforce_sel_arg_weight_limit(SEL_ARG *sel_arg); +static SEL_ARG *enforce_sel_arg_weight_limit(THD *thd, SEL_ARG *sel_arg); #include "opt_range_mrr.cc" @@ -9971,7 +9971,8 @@ uint SEL_ARG::verify_weight() static SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2) { - SEL_ARG *res= enforce_sel_arg_weight_limit(key_or(param, key1, key2)); + SEL_ARG *res= enforce_sel_arg_weight_limit(param->thd, key_or(param, + key1, key2)); #ifndef DBUG_OFF if (res) res->verify_weight(); @@ -9984,7 +9985,9 @@ static SEL_ARG *key_and_with_limit(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) { - SEL_ARG *res= enforce_sel_arg_weight_limit(key_and(param, key1, key2, clone_flag)); + SEL_ARG *res= enforce_sel_arg_weight_limit(param->thd, key_and(param, key1, + key2, + clone_flag)); #ifndef DBUG_OFF if (res) res->verify_weight(); @@ -10770,14 +10773,15 @@ void prune_sel_arg_graph(SEL_ARG *sel_arg, uint max_part) limit. */ -SEL_ARG *enforce_sel_arg_weight_limit(SEL_ARG *sel_arg) +SEL_ARG *enforce_sel_arg_weight_limit(THD *thd, SEL_ARG *sel_arg) { - if (!sel_arg || sel_arg->type != SEL_ARG::KEY_RANGE) + if (!sel_arg || sel_arg->type != SEL_ARG::KEY_RANGE || + !thd->variables.optimizer_max_sel_arg_weight) return sel_arg; while (1) { - if (sel_arg->weight <= SEL_ARG::MAX_WEIGHT) + if (sel_arg->weight <= thd->variables.optimizer_max_sel_arg_weight) return sel_arg; uint max_part= sel_arg->get_max_key_part(); diff --git a/sql/sql_class.h b/sql/sql_class.h index 64cd1ed6ba3..6063c51bccf 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -766,6 +766,7 @@ typedef struct system_variables uint column_compression_threshold; uint column_compression_zlib_level; uint in_subquery_conversion_threshold; + ulong optimizer_max_sel_arg_weight; ulonglong max_rowid_filter_size; vers_asof_timestamp_t vers_asof_timestamp; diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 1c84f308a81..6b859363453 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -6367,6 +6367,12 @@ static Sys_var_uint Sys_in_subquery_conversion_threshold( SESSION_VAR(in_subquery_conversion_threshold), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, UINT_MAX), DEFAULT(IN_SUBQUERY_CONVERSION_THRESHOLD), BLOCK_SIZE(1)); +static Sys_var_ulong Sys_optimizer_max_sel_arg_weight( + "optimizer_max_sel_arg_weight", + "The maximum weight of the SEL_ARG graph. Set to 0 for no limit", + SESSION_VAR(optimizer_max_sel_arg_weight), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, ULONG_MAX), DEFAULT(SEL_ARG::MAX_WEIGHT), BLOCK_SIZE(1)); + static Sys_var_enum Sys_secure_timestamp( "secure_timestamp", "Restricts direct setting of a session " "timestamp. Possible levels are: YES - timestamp cannot deviate from "