diff options
| -rw-r--r-- | mysql-test/main/mysqld--help.result | 4 | ||||
| -rw-r--r-- | mysql-test/main/range_notembedded.result | 179 | ||||
| -rw-r--r-- | mysql-test/main/range_notembedded.test | 66 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff | 155 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_embedded.result | 10 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff | 173 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result | 10 | ||||
| -rw-r--r-- | sql/opt_range.cc | 302 | ||||
| -rw-r--r-- | sql/opt_range.h | 63 | ||||
| -rw-r--r-- | sql/sql_class.h | 1 | ||||
| -rw-r--r-- | sql/sys_vars.cc | 6 |
11 files changed, 811 insertions, 158 deletions
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 7671dfeef59..141ef94ad59 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -681,6 +681,9 @@ The following specify which files/extra groups are read (specified before remain max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors (Automatically configured unless set explicitly) + --optimizer-max-sel-arg-weight=# + The maximum weight of the SEL_ARG graph. Set to 0 for no + limit --optimizer-prune-level=# Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from @@ -1637,6 +1640,7 @@ old-alter-table DEFAULT old-mode old-passwords FALSE old-style-user-limits FALSE +optimizer-max-sel-arg-weight 32000 optimizer-prune-level 1 optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result index 4973f449631..1f6db102624 100644 --- a/mysql-test/main/range_notembedded.result +++ b/mysql-test/main/range_notembedded.result @@ -35,3 +35,182 @@ json_detailed(JSON_EXTRACT(trace, '$**.ranges')) ] set optimizer_trace=@tmp_21958; drop table t2; +# +# MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... +# +create table t1 ( +kp1 int, +kp2 int, +kp3 int, +kp4 int, +key key1(kp1, kp2, kp3,kp4) +); +insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +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 +kp1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and +kp2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and +kp3 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and +kp4 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) +; +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); +# This will show 3-component ranges. +# The ranges were produced, but the optimizer has cut away kp4 +# to keep the number of ranges at manageable level: +select left(@json, 500); +left(@json, 500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1,1,1) <= (kp1,kp2,kp3) <= (1,1,1)", + "(1,1,2) <= (kp1,kp2,kp3) <= (1,1,2)", + "(1,1,3) <= (kp1,kp2,kp3) <= (1,1,3)", + "(1,1,4) <= (kp1,kp2,kp3) <= (1,1,4)", + "(1,1,5) <= (kp1,kp2,kp3) <= (1,1,5)", + "(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 @trace= (select trace from information_schema.optimizer_trace); +set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); +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)" + +set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); +select left(@json, 2500); +left(@json, 2500) +[ + + [ + + { + "sel_arg_weight_heuristic": + { + "key1_field": "kp1", + "key2_field": "kp2", + "key1_weight": 10, + "key2_weight": 10 + } + }, + + { + "sel_arg_weight_heuristic": + { + "key1_field": "kp1", + "key2_field": "kp3", + "key1_weight": 10, + "key2_weight": 10 + } + }, + + { + "sel_arg_weight_heuristic": + { + "key1_field": "kp1", + "key2_field": "kp4", + "key1_weight": 10, + "key2_weight": 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; diff --git a/mysql-test/main/range_notembedded.test b/mysql-test/main/range_notembedded.test index 4dc49429ff1..d50bec23148 100644 --- a/mysql-test/main/range_notembedded.test +++ b/mysql-test/main/range_notembedded.test @@ -31,3 +31,69 @@ from information_schema.optimizer_trace; set optimizer_trace=@tmp_21958; drop table t2; +--echo # +--echo # MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... +--echo # + +create table t1 ( + kp1 int, + kp2 int, + kp3 int, + kp4 int, + key key1(kp1, kp2, kp3,kp4) +); + +insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); +analyze table t1; + +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; +explain select * from t1 where + kp1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and + kp2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and + kp3 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and + kp4 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) +; + +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) + from information_schema.optimizer_trace); +--echo # This will show 3-component ranges. +--echo # The ranges were produced, but the optimizer has cut away kp4 +--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 @trace= (select trace from information_schema.optimizer_trace); +set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); +select left(@json, 500); + +set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); +select left(@json, 2500); + +--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; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff index 510a9c28051..37cebcf70dc 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff @@ -713,7 +713,20 @@ VARIABLE_COMMENT If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 or autoset then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2235,7 +2235,7 @@ +@@ -2235,10 +2235,10 @@ + COMMAND_LINE_ARGUMENT REQUIRED + VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT + VARIABLE_SCOPE SESSION +-VARIABLE_TYPE BIGINT UNSIGNED ++VARIABLE_TYPE INT UNSIGNED + VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit + NUMERIC_MIN_VALUE 0 +-NUMERIC_MAX_VALUE 18446744073709551615 ++NUMERIC_MAX_VALUE 4294967295 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO +@@ -2245,7 +2245,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION @@ -722,7 +735,7 @@ VARIABLE_COMMENT Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space. Meaning: 0 - do not apply any heuristic, thus perform exhaustive search; 1 - prune plans based on number of retrieved rows NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1 -@@ -2245,7 +2245,7 @@ +@@ -2255,7 +2255,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SEARCH_DEPTH VARIABLE_SCOPE SESSION @@ -731,7 +744,7 @@ VARIABLE_COMMENT Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 62 -@@ -2255,7 +2255,7 @@ +@@ -2265,7 +2265,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SELECTIVITY_SAMPLING_LIMIT VARIABLE_SCOPE SESSION @@ -740,7 +753,7 @@ VARIABLE_COMMENT Controls number of record samples to check condition selectivity NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 4294967295 -@@ -2285,17 +2285,17 @@ +@@ -2295,17 +2295,17 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE_MAX_MEM_SIZE VARIABLE_SCOPE SESSION @@ -761,7 +774,7 @@ VARIABLE_COMMENT Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan or an index scan, 2 - use selectivity of index backed range conditions to calculate the cardinality of a partial join in any case, 3 - additionally always use selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join, 4 - use histograms to calculate selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join.5 - additionally use selectivity of certain non-range predicates calculated on record samples NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 5 -@@ -2315,7 +2315,7 @@ +@@ -2325,7 +2325,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME PERFORMANCE_SCHEMA_ACCOUNTS_SIZE VARIABLE_SCOPE GLOBAL @@ -770,7 +783,7 @@ VARIABLE_COMMENT Maximum number of instrumented user@host accounts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2325,7 +2325,7 @@ +@@ -2335,7 +2335,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_DIGESTS_SIZE VARIABLE_SCOPE GLOBAL @@ -779,7 +792,7 @@ VARIABLE_COMMENT Size of the statement digest. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 200 -@@ -2335,7 +2335,7 @@ +@@ -2345,7 +2345,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -788,7 +801,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STAGES_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2345,7 +2345,7 @@ +@@ -2355,7 +2355,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -806,7 +819,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2365,7 +2365,7 @@ +@@ -2375,7 +2375,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -815,7 +828,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STATEMENTS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2375,7 +2375,7 @@ +@@ -2385,7 +2395,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_TRANSACTIONS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -824,7 +837,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_TRANSACTIONS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2385,7 +2385,7 @@ +@@ -2395,7 +2395,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_TRANSACTIONS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -833,7 +846,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_TRANSACTIONS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2395,7 +2395,7 @@ +@@ -2405,7 +2405,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -842,7 +855,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_WAITS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2405,7 +2405,7 @@ +@@ -2415,7 +2415,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -851,7 +864,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_WAITS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2415,7 +2415,7 @@ +@@ -2425,7 +2425,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_HOSTS_SIZE VARIABLE_SCOPE GLOBAL @@ -860,7 +873,7 @@ VARIABLE_COMMENT Maximum number of instrumented hosts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2425,7 +2425,7 @@ +@@ -2435,7 +2435,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_CLASSES VARIABLE_SCOPE GLOBAL @@ -869,7 +882,7 @@ VARIABLE_COMMENT Maximum number of condition instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2435,7 +2435,7 @@ +@@ -2445,7 +2445,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_INSTANCES VARIABLE_SCOPE GLOBAL @@ -878,7 +891,7 @@ VARIABLE_COMMENT Maximum number of instrumented condition objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2445,7 +2445,7 @@ +@@ -2455,7 +2455,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH VARIABLE_SCOPE GLOBAL @@ -887,7 +900,7 @@ VARIABLE_COMMENT Maximum length considered for digest text, when stored in performance_schema tables. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2455,7 +2455,7 @@ +@@ -2465,7 +2465,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_CLASSES VARIABLE_SCOPE GLOBAL @@ -896,7 +909,7 @@ VARIABLE_COMMENT Maximum number of file instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2465,7 +2465,7 @@ +@@ -2475,7 +2475,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_HANDLES VARIABLE_SCOPE GLOBAL @@ -905,7 +918,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented files. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2475,7 +2475,7 @@ +@@ -2485,7 +2485,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -914,7 +927,7 @@ VARIABLE_COMMENT Maximum number of instrumented files. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2485,7 +2485,7 @@ +@@ -2495,7 +2495,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_INDEX_STAT VARIABLE_SCOPE GLOBAL @@ -923,7 +936,7 @@ VARIABLE_COMMENT Maximum number of index statistics for instrumented tables. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2495,7 +2495,7 @@ +@@ -2505,7 +2505,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MEMORY_CLASSES VARIABLE_SCOPE GLOBAL @@ -932,7 +945,7 @@ VARIABLE_COMMENT Maximum number of memory pool instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1024 -@@ -2505,7 +2505,7 @@ +@@ -2515,7 +2515,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_METADATA_LOCKS VARIABLE_SCOPE GLOBAL @@ -941,7 +954,7 @@ VARIABLE_COMMENT Maximum number of metadata locks. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2515,7 +2515,7 @@ +@@ -2525,7 +2525,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_CLASSES VARIABLE_SCOPE GLOBAL @@ -950,7 +963,7 @@ VARIABLE_COMMENT Maximum number of mutex instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2525,7 +2525,7 @@ +@@ -2535,7 +2535,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES VARIABLE_SCOPE GLOBAL @@ -959,7 +972,7 @@ VARIABLE_COMMENT Maximum number of instrumented MUTEX objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2535,7 +2535,7 @@ +@@ -2545,7 +2545,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_PREPARED_STATEMENTS_INSTANCES VARIABLE_SCOPE GLOBAL @@ -968,7 +981,7 @@ VARIABLE_COMMENT Maximum number of instrumented prepared statements. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2545,7 +2545,7 @@ +@@ -2555,7 +2555,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_PROGRAM_INSTANCES VARIABLE_SCOPE GLOBAL @@ -977,7 +990,7 @@ VARIABLE_COMMENT Maximum number of instrumented programs. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2555,7 +2555,7 @@ +@@ -2565,7 +2565,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_CLASSES VARIABLE_SCOPE GLOBAL @@ -986,7 +999,7 @@ VARIABLE_COMMENT Maximum number of rwlock instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2565,7 +2565,7 @@ +@@ -2575,7 +2575,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES VARIABLE_SCOPE GLOBAL @@ -995,7 +1008,7 @@ VARIABLE_COMMENT Maximum number of instrumented RWLOCK objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2575,7 +2575,7 @@ +@@ -2585,7 +2585,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_CLASSES VARIABLE_SCOPE GLOBAL @@ -1004,7 +1017,7 @@ VARIABLE_COMMENT Maximum number of socket instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2585,7 +2585,7 @@ +@@ -2595,7 +2595,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1013,7 +1026,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented sockets. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2595,7 +2595,7 @@ +@@ -2605,7 +2605,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SQL_TEXT_LENGTH VARIABLE_SCOPE GLOBAL @@ -1022,7 +1035,7 @@ VARIABLE_COMMENT Maximum length of displayed sql text. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2605,7 +2605,7 @@ +@@ -2615,7 +2615,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STAGE_CLASSES VARIABLE_SCOPE GLOBAL @@ -1031,7 +1044,7 @@ VARIABLE_COMMENT Maximum number of stage instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2615,7 +2615,7 @@ +@@ -2625,7 +2625,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES VARIABLE_SCOPE GLOBAL @@ -1040,7 +1053,7 @@ VARIABLE_COMMENT Maximum number of statement instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2625,7 +2625,7 @@ +@@ -2635,7 +2635,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_STACK VARIABLE_SCOPE GLOBAL @@ -1049,7 +1062,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STATEMENTS_CURRENT. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 256 -@@ -2635,7 +2635,7 @@ +@@ -2645,7 +2645,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES VARIABLE_SCOPE GLOBAL @@ -1058,7 +1071,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2645,7 +2645,7 @@ +@@ -2655,7 +2655,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1067,7 +1080,7 @@ VARIABLE_COMMENT Maximum number of instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2655,7 +2655,7 @@ +@@ -2665,7 +2665,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_LOCK_STAT VARIABLE_SCOPE GLOBAL @@ -1076,7 +1089,7 @@ VARIABLE_COMMENT Maximum number of lock statistics for instrumented tables. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2665,7 +2665,7 @@ +@@ -2675,7 +2675,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_CLASSES VARIABLE_SCOPE GLOBAL @@ -1085,7 +1098,7 @@ VARIABLE_COMMENT Maximum number of thread instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2675,7 +2675,7 @@ +@@ -2685,7 +2685,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1094,7 +1107,7 @@ VARIABLE_COMMENT Maximum number of instrumented threads. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2685,7 +2685,7 @@ +@@ -2695,7 +2695,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SESSION_CONNECT_ATTRS_SIZE VARIABLE_SCOPE GLOBAL @@ -1103,7 +1116,7 @@ VARIABLE_COMMENT Size of session attribute string buffer per thread. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2695,7 +2695,7 @@ +@@ -2705,7 +2705,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_ACTORS_SIZE VARIABLE_SCOPE GLOBAL @@ -1112,7 +1125,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_ACTORS. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2705,7 +2705,7 @@ +@@ -2715,7 +2715,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_OBJECTS_SIZE VARIABLE_SCOPE GLOBAL @@ -1121,7 +1134,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_OBJECTS. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2715,7 +2715,7 @@ +@@ -2725,7 +2725,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_USERS_SIZE VARIABLE_SCOPE GLOBAL @@ -1130,7 +1143,7 @@ VARIABLE_COMMENT Maximum number of instrumented users. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2765,7 +2765,7 @@ +@@ -2775,7 +2775,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PRELOAD_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1139,7 +1152,7 @@ VARIABLE_COMMENT The size of the buffer that is allocated when preloading indexes NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -2785,7 +2785,7 @@ +@@ -2795,7 +2795,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME PROFILING_HISTORY_SIZE VARIABLE_SCOPE SESSION @@ -1148,7 +1161,7 @@ VARIABLE_COMMENT Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 -@@ -2795,7 +2795,7 @@ +@@ -2805,7 +2805,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PROGRESS_REPORT_TIME VARIABLE_SCOPE SESSION @@ -1157,7 +1170,7 @@ VARIABLE_COMMENT Seconds between sending progress reports to the client for time-consuming statements. Set to 0 to disable progress reporting. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2855,7 +2855,7 @@ +@@ -2865,7 +2865,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME QUERY_ALLOC_BLOCK_SIZE VARIABLE_SCOPE SESSION @@ -1166,7 +1179,7 @@ VARIABLE_COMMENT Allocation block size for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -2865,7 +2865,7 @@ +@@ -2875,7 +2875,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_LIMIT VARIABLE_SCOPE GLOBAL @@ -1175,7 +1188,7 @@ VARIABLE_COMMENT Don't cache results that are bigger than this NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2875,7 +2875,7 @@ +@@ -2885,7 +2885,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_MIN_RES_UNIT VARIABLE_SCOPE GLOBAL @@ -1184,7 +1197,7 @@ VARIABLE_COMMENT The minimum size for blocks allocated by the query cache NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2888,7 +2888,7 @@ +@@ -2898,7 +2898,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The memory allocated to store results from old queries NUMERIC_MIN_VALUE 0 @@ -1193,7 +1206,7 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2925,7 +2925,7 @@ +@@ -2935,7 +2935,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME QUERY_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1202,7 +1215,7 @@ VARIABLE_COMMENT Persistent buffer for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -2938,7 +2938,7 @@ +@@ -2948,7 +2948,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1211,7 +1224,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2948,14 +2948,14 @@ +@@ -2958,14 +2958,14 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1228,7 +1241,7 @@ VARIABLE_COMMENT Allocation block size for storing ranges during optimization NUMERIC_MIN_VALUE 4096 NUMERIC_MAX_VALUE 4294967295 -@@ -2965,7 +2965,7 @@ +@@ -2975,7 +2975,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME READ_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1237,7 +1250,7 @@ VARIABLE_COMMENT Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value NUMERIC_MIN_VALUE 8192 NUMERIC_MAX_VALUE 2147483647 -@@ -2985,7 +2985,7 @@ +@@ -2995,7 +2995,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME READ_RND_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1246,7 +1259,7 @@ VARIABLE_COMMENT When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 2147483647 -@@ -2905,10 +2905,10 @@ +@@ -2915,10 +2915,10 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME ROWID_MERGE_BUFF_SIZE VARIABLE_SCOPE SESSION @@ -1259,7 +1272,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2945,7 +2945,7 @@ +@@ -2955,7 +2955,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SERVER_ID VARIABLE_SCOPE SESSION @@ -1268,7 +1281,7 @@ VARIABLE_COMMENT Uniquely identifies the server instance in the community of replication partners NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 4294967295 -@@ -3015,7 +3015,7 @@ +@@ -3025,7 +3025,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME SLAVE_MAX_ALLOWED_PACKET VARIABLE_SCOPE GLOBAL @@ -1277,7 +1290,7 @@ VARIABLE_COMMENT The maximum packet length to sent successfully from the master to slave. NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -3025,7 +3025,7 @@ +@@ -3035,7 +3035,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLOW_LAUNCH_TIME VARIABLE_SCOPE GLOBAL @@ -1286,7 +1299,7 @@ VARIABLE_COMMENT If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 31536000 -@@ -3068,7 +3068,7 @@ +@@ -3078,7 +3078,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Each thread that needs to do a sort allocates a buffer of this size NUMERIC_MIN_VALUE 1024 @@ -1295,7 +1308,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3275,7 +3275,7 @@ +@@ -3285,7 +3285,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME STORED_PROGRAM_CACHE VARIABLE_SCOPE GLOBAL @@ -1304,7 +1317,7 @@ VARIABLE_COMMENT The soft upper limit for number of cached stored routines for one connection. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 524288 -@@ -3355,7 +3355,7 @@ +@@ -3365,7 +3365,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TABLE_DEFINITION_CACHE VARIABLE_SCOPE GLOBAL @@ -1313,7 +1326,7 @@ VARIABLE_COMMENT The number of cached table definitions NUMERIC_MIN_VALUE 400 NUMERIC_MAX_VALUE 2097152 -@@ -3365,7 +3365,7 @@ +@@ -3375,7 +3375,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_OPEN_CACHE VARIABLE_SCOPE GLOBAL @@ -1322,7 +1335,7 @@ VARIABLE_COMMENT The number of cached open tables NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 1048576 -@@ -3425,7 +3425,7 @@ +@@ -3435,7 +3435,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME THREAD_CACHE_SIZE VARIABLE_SCOPE GLOBAL @@ -1331,7 +1344,7 @@ VARIABLE_COMMENT How many threads we should keep in a cache for reuse. These are freed after 5 minutes of idle time NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16384 -@@ -3508,7 +3508,7 @@ +@@ -3518,7 +3518,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Max size for data for an internal temporary on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@ -1340,7 +1353,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3518,7 +3518,7 @@ +@@ -3528,7 +3528,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. Same as tmp_table_size. NUMERIC_MIN_VALUE 0 @@ -1349,7 +1362,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3528,14 +3528,14 @@ +@@ -3538,14 +3538,14 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Alias for tmp_memory_table_size. If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 0 @@ -1366,7 +1379,7 @@ VARIABLE_COMMENT Allocation block size for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -3545,7 +3545,7 @@ +@@ -3555,7 +3555,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TRANSACTION_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1375,7 +1388,7 @@ VARIABLE_COMMENT Persistent buffer for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -3685,7 +3685,7 @@ +@@ -3695,7 +3695,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME WAIT_TIMEOUT VARIABLE_SCOPE SESSION @@ -1384,7 +1397,7 @@ VARIABLE_COMMENT The number of seconds the server waits for activity on a connection before closing it NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 31536000 -@@ -3712,7 +3712,7 @@ +@@ -3722,7 +3722,7 @@ VARIABLE_NAME LOG_TC_SIZE GLOBAL_VALUE_ORIGIN AUTO VARIABLE_SCOPE GLOBAL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 0648284e580..e0b4e3565bc 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -2233,6 +2233,16 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff index beea596b212..59236c401f5 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff @@ -713,7 +713,20 @@ VARIABLE_COMMENT If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 or autoset then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2395,7 +2395,7 @@ +@@ -2395,10 +2395,10 @@ + COMMAND_LINE_ARGUMENT REQUIRED + VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT + VARIABLE_SCOPE SESSION +-VARIABLE_TYPE BIGINT UNSIGNED ++VARIABLE_TYPE INT UNSIGNED + VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit + NUMERIC_MIN_VALUE 0 +-NUMERIC_MAX_VALUE 18446744073709551615 ++NUMERIC_MAX_VALUE 4294967295 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO +@@ -2405,7 +2405,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION @@ -722,7 +735,7 @@ VARIABLE_COMMENT Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space. Meaning: 0 - do not apply any heuristic, thus perform exhaustive search; 1 - prune plans based on number of retrieved rows NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1 -@@ -2405,7 +2405,7 @@ +@@ -2415,7 +2415,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SEARCH_DEPTH VARIABLE_SCOPE SESSION @@ -731,7 +744,7 @@ VARIABLE_COMMENT Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 62 -@@ -2415,7 +2415,7 @@ +@@ -2425,7 +2425,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SELECTIVITY_SAMPLING_LIMIT VARIABLE_SCOPE SESSION @@ -740,7 +753,7 @@ VARIABLE_COMMENT Controls number of record samples to check condition selectivity NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 4294967295 -@@ -2445,17 +2445,17 @@ +@@ -2455,17 +2455,17 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE_MAX_MEM_SIZE VARIABLE_SCOPE SESSION @@ -761,7 +774,7 @@ VARIABLE_COMMENT Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan or an index scan, 2 - use selectivity of index backed range conditions to calculate the cardinality of a partial join in any case, 3 - additionally always use selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join, 4 - use histograms to calculate selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join.5 - additionally use selectivity of certain non-range predicates calculated on record samples NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 5 -@@ -2475,7 +2475,7 @@ +@@ -2485,7 +2485,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME PERFORMANCE_SCHEMA_ACCOUNTS_SIZE VARIABLE_SCOPE GLOBAL @@ -770,7 +783,7 @@ VARIABLE_COMMENT Maximum number of instrumented user@host accounts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2485,7 +2485,7 @@ +@@ -2495,7 +2495,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_DIGESTS_SIZE VARIABLE_SCOPE GLOBAL @@ -779,7 +792,7 @@ VARIABLE_COMMENT Size of the statement digest. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 200 -@@ -2495,7 +2495,7 @@ +@@ -2505,7 +2505,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -788,7 +801,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STAGES_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2505,7 +2505,7 @@ +@@ -2515,7 +2515,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -797,7 +810,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STAGES_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2515,7 +2515,7 @@ +@@ -2525,7 +2525,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -806,7 +819,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2525,7 +2525,7 @@ +@@ -2535,7 +2535,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -815,7 +828,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STATEMENTS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2535,7 +2535,7 @@ +@@ -2545,7 +2545,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_TRANSACTIONS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -824,7 +837,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_TRANSACTIONS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2545,7 +2545,7 @@ +@@ -2555,7 +2555,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_TRANSACTIONS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -833,7 +846,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_TRANSACTIONS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2555,7 +2555,7 @@ +@@ -2565,7 +2565,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -842,7 +855,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_WAITS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2565,7 +2565,7 @@ +@@ -2575,7 +2575,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -851,7 +864,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_WAITS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2575,7 +2575,7 @@ +@@ -2585,7 +2585,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_HOSTS_SIZE VARIABLE_SCOPE GLOBAL @@ -860,7 +873,7 @@ VARIABLE_COMMENT Maximum number of instrumented hosts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2585,7 +2585,7 @@ +@@ -2595,7 +2595,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_CLASSES VARIABLE_SCOPE GLOBAL @@ -869,7 +882,7 @@ VARIABLE_COMMENT Maximum number of condition instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2595,7 +2595,7 @@ +@@ -2605,7 +2605,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_INSTANCES VARIABLE_SCOPE GLOBAL @@ -878,7 +891,7 @@ VARIABLE_COMMENT Maximum number of instrumented condition objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2605,7 +2605,7 @@ +@@ -2615,7 +2615,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH VARIABLE_SCOPE GLOBAL @@ -887,7 +900,7 @@ VARIABLE_COMMENT Maximum length considered for digest text, when stored in performance_schema tables. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2615,7 +2615,7 @@ +@@ -2625,7 +2625,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_CLASSES VARIABLE_SCOPE GLOBAL @@ -896,7 +909,7 @@ VARIABLE_COMMENT Maximum number of file instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2625,7 +2625,7 @@ +@@ -2635,7 +2635,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_HANDLES VARIABLE_SCOPE GLOBAL @@ -905,7 +918,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented files. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2635,7 +2635,7 @@ +@@ -2645,7 +2645,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -914,7 +927,7 @@ VARIABLE_COMMENT Maximum number of instrumented files. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2645,7 +2645,7 @@ +@@ -2655,7 +2655,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_INDEX_STAT VARIABLE_SCOPE GLOBAL @@ -923,7 +936,7 @@ VARIABLE_COMMENT Maximum number of index statistics for instrumented tables. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2655,7 +2655,7 @@ +@@ -2665,7 +2665,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MEMORY_CLASSES VARIABLE_SCOPE GLOBAL @@ -932,7 +945,7 @@ VARIABLE_COMMENT Maximum number of memory pool instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1024 -@@ -2665,7 +2665,7 @@ +@@ -2675,7 +2675,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_METADATA_LOCKS VARIABLE_SCOPE GLOBAL @@ -941,7 +954,7 @@ VARIABLE_COMMENT Maximum number of metadata locks. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2675,7 +2675,7 @@ +@@ -2685,7 +2685,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_CLASSES VARIABLE_SCOPE GLOBAL @@ -950,7 +963,7 @@ VARIABLE_COMMENT Maximum number of mutex instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2685,7 +2685,7 @@ +@@ -2695,7 +2695,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES VARIABLE_SCOPE GLOBAL @@ -959,7 +972,7 @@ VARIABLE_COMMENT Maximum number of instrumented MUTEX objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2695,7 +2695,7 @@ +@@ -2705,7 +2705,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_PREPARED_STATEMENTS_INSTANCES VARIABLE_SCOPE GLOBAL @@ -968,7 +981,7 @@ VARIABLE_COMMENT Maximum number of instrumented prepared statements. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2705,7 +2705,7 @@ +@@ -2715,7 +2715,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_PROGRAM_INSTANCES VARIABLE_SCOPE GLOBAL @@ -977,7 +990,7 @@ VARIABLE_COMMENT Maximum number of instrumented programs. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2715,7 +2715,7 @@ +@@ -2725,7 +2725,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_CLASSES VARIABLE_SCOPE GLOBAL @@ -986,7 +999,7 @@ VARIABLE_COMMENT Maximum number of rwlock instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2725,7 +2725,7 @@ +@@ -2735,7 +2735,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES VARIABLE_SCOPE GLOBAL @@ -995,7 +1008,7 @@ VARIABLE_COMMENT Maximum number of instrumented RWLOCK objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2735,7 +2735,7 @@ +@@ -2745,7 +2745,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_CLASSES VARIABLE_SCOPE GLOBAL @@ -1004,7 +1017,7 @@ VARIABLE_COMMENT Maximum number of socket instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2745,7 +2745,7 @@ +@@ -2755,7 +2755,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1013,7 +1026,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented sockets. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2755,7 +2755,7 @@ +@@ -2765,7 +2765,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SQL_TEXT_LENGTH VARIABLE_SCOPE GLOBAL @@ -1022,7 +1035,7 @@ VARIABLE_COMMENT Maximum length of displayed sql text. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2765,7 +2765,7 @@ +@@ -2775,7 +2775,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STAGE_CLASSES VARIABLE_SCOPE GLOBAL @@ -1031,7 +1044,7 @@ VARIABLE_COMMENT Maximum number of stage instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2775,7 +2775,7 @@ +@@ -2785,7 +2785,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES VARIABLE_SCOPE GLOBAL @@ -1040,7 +1053,7 @@ VARIABLE_COMMENT Maximum number of statement instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2785,7 +2785,7 @@ +@@ -2795,7 +2795,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_STACK VARIABLE_SCOPE GLOBAL @@ -1049,7 +1062,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STATEMENTS_CURRENT. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 256 -@@ -2795,7 +2795,7 @@ +@@ -2805,7 +2805,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES VARIABLE_SCOPE GLOBAL @@ -1058,7 +1071,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2805,7 +2805,7 @@ +@@ -2815,7 +2815,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1067,7 +1080,7 @@ VARIABLE_COMMENT Maximum number of instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2815,7 +2815,7 @@ +@@ -2825,7 +2825,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_LOCK_STAT VARIABLE_SCOPE GLOBAL @@ -1076,7 +1089,7 @@ VARIABLE_COMMENT Maximum number of lock statistics for instrumented tables. Use 0 to disable, -1 for automated scaling. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2825,7 +2825,7 @@ +@@ -2835,7 +2835,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_CLASSES VARIABLE_SCOPE GLOBAL @@ -1085,7 +1098,7 @@ VARIABLE_COMMENT Maximum number of thread instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2835,7 +2835,7 @@ +@@ -2845,7 +2845,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1094,7 +1107,7 @@ VARIABLE_COMMENT Maximum number of instrumented threads. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2845,7 +2845,7 @@ +@@ -2855,7 +2855,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SESSION_CONNECT_ATTRS_SIZE VARIABLE_SCOPE GLOBAL @@ -1103,7 +1116,7 @@ VARIABLE_COMMENT Size of session attribute string buffer per thread. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2855,7 +2855,7 @@ +@@ -2865,7 +2865,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_ACTORS_SIZE VARIABLE_SCOPE GLOBAL @@ -1112,7 +1125,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_ACTORS. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2865,7 +2865,7 @@ +@@ -2875,7 +2875,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_OBJECTS_SIZE VARIABLE_SCOPE GLOBAL @@ -1121,7 +1134,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_OBJECTS. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2875,7 +2875,7 @@ +@@ -2885,7 +2885,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_USERS_SIZE VARIABLE_SCOPE GLOBAL @@ -1130,7 +1143,7 @@ VARIABLE_COMMENT Maximum number of instrumented users. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2925,7 +2925,7 @@ +@@ -2935,7 +2935,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PRELOAD_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1139,7 +1152,7 @@ VARIABLE_COMMENT The size of the buffer that is allocated when preloading indexes NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -2945,7 +2945,7 @@ +@@ -2955,7 +2955,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME PROFILING_HISTORY_SIZE VARIABLE_SCOPE SESSION @@ -1148,7 +1161,7 @@ VARIABLE_COMMENT Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 -@@ -2955,7 +2955,7 @@ +@@ -2965,7 +2965,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PROGRESS_REPORT_TIME VARIABLE_SCOPE SESSION @@ -1157,7 +1170,7 @@ VARIABLE_COMMENT Seconds between sending progress reports to the client for time-consuming statements. Set to 0 to disable progress reporting. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -3015,7 +3015,7 @@ +@@ -3025,7 +3025,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME QUERY_ALLOC_BLOCK_SIZE VARIABLE_SCOPE SESSION @@ -1166,7 +1179,7 @@ VARIABLE_COMMENT Allocation block size for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -3025,7 +3025,7 @@ +@@ -3035,7 +3035,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_LIMIT VARIABLE_SCOPE GLOBAL @@ -1175,7 +1188,7 @@ VARIABLE_COMMENT Don't cache results that are bigger than this NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -3035,7 +3035,7 @@ +@@ -3045,7 +3045,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_MIN_RES_UNIT VARIABLE_SCOPE GLOBAL @@ -1184,7 +1197,7 @@ VARIABLE_COMMENT The minimum size for blocks allocated by the query cache NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -3048,7 +3048,7 @@ +@@ -3058,7 +3058,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The memory allocated to store results from old queries NUMERIC_MIN_VALUE 0 @@ -1193,7 +1206,7 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3085,7 +3085,7 @@ +@@ -3095,7 +3095,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME QUERY_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1202,7 +1215,7 @@ VARIABLE_COMMENT Persistent buffer for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -3098,7 +3098,7 @@ +@@ -3108,7 +3108,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1211,7 +1224,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3108,14 +3108,14 @@ +@@ -3118,14 +3118,14 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1228,7 +1241,7 @@ VARIABLE_COMMENT Allocation block size for storing ranges during optimization NUMERIC_MIN_VALUE 4096 NUMERIC_MAX_VALUE 4294967295 -@@ -3128,14 +3128,14 @@ +@@ -3138,14 +3138,14 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Maximum speed(KB/s) to read binlog from master (0 = no limit) NUMERIC_MIN_VALUE 0 @@ -1245,7 +1258,7 @@ VARIABLE_COMMENT Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value NUMERIC_MIN_VALUE 8192 NUMERIC_MAX_VALUE 2147483647 -@@ -3155,7 +3155,7 @@ +@@ -3165,7 +3165,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME READ_RND_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1254,7 +1267,7 @@ VARIABLE_COMMENT When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 2147483647 -@@ -3365,10 +3365,10 @@ +@@ -3375,10 +3375,10 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME ROWID_MERGE_BUFF_SIZE VARIABLE_SCOPE SESSION @@ -1267,7 +1280,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3385,20 +3385,20 @@ +@@ -3395,20 +3395,20 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME RPL_SEMI_SYNC_MASTER_TIMEOUT VARIABLE_SCOPE GLOBAL @@ -1292,7 +1305,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3455,10 +3455,10 @@ +@@ -3465,10 +3465,10 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME RPL_SEMI_SYNC_SLAVE_TRACE_LEVEL VARIABLE_SCOPE GLOBAL @@ -1305,7 +1318,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3495,7 +3495,7 @@ +@@ -3505,7 +3505,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SERVER_ID VARIABLE_SCOPE SESSION @@ -1314,7 +1327,7 @@ VARIABLE_COMMENT Uniquely identifies the server instance in the community of replication partners NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 4294967295 -@@ -3635,7 +3635,7 @@ +@@ -3645,7 +3645,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_DOMAIN_PARALLEL_THREADS VARIABLE_SCOPE GLOBAL @@ -1323,7 +1336,7 @@ VARIABLE_COMMENT Maximum number of parallel threads to use on slave for events in a single replication domain. When using multiple domains, this can be used to limit a single domain from grabbing all threads and thus stalling other domains. The default of 0 means to allow a domain to grab as many threads as it wants, up to the value of slave_parallel_threads. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16383 -@@ -3665,7 +3665,7 @@ +@@ -3675,7 +3675,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_MAX_ALLOWED_PACKET VARIABLE_SCOPE GLOBAL @@ -1332,7 +1345,7 @@ VARIABLE_COMMENT The maximum packet length to sent successfully from the master to slave. NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -3685,7 +3685,7 @@ +@@ -3695,7 +3695,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_PARALLEL_MAX_QUEUED VARIABLE_SCOPE GLOBAL @@ -1341,7 +1354,7 @@ VARIABLE_COMMENT Limit on how much memory SQL threads should use per parallel replication thread when reading ahead in the relay log looking for opportunities for parallel replication. Only used when --slave-parallel-threads > 0. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 2147483647 -@@ -3705,7 +3705,7 @@ +@@ -3715,7 +3715,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME SLAVE_PARALLEL_THREADS VARIABLE_SCOPE GLOBAL @@ -1350,7 +1363,7 @@ VARIABLE_COMMENT If non-zero, number of threads to spawn to apply in parallel events on the slave that were group-committed on the master or were logged with GTID in different replication domains. Note that these threads are in addition to the IO and SQL threads, which are always created by a replication slave NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16383 -@@ -3715,7 +3715,7 @@ +@@ -3725,7 +3725,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_PARALLEL_WORKERS VARIABLE_SCOPE GLOBAL @@ -1359,7 +1372,7 @@ VARIABLE_COMMENT Alias for slave_parallel_threads NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16383 -@@ -3755,7 +3755,7 @@ +@@ -3765,7 +3765,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME SLAVE_TRANSACTION_RETRIES VARIABLE_SCOPE GLOBAL @@ -1368,7 +1381,7 @@ VARIABLE_COMMENT Number of times the slave SQL thread will retry a transaction in case it failed with a deadlock, elapsed lock wait timeout or listed in slave_transaction_retry_errors, before giving up and stopping NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -3775,7 +3775,7 @@ +@@ -3785,7 +3785,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_TRANSACTION_RETRY_INTERVAL VARIABLE_SCOPE GLOBAL @@ -1377,7 +1390,7 @@ VARIABLE_COMMENT Interval of the slave SQL thread will retry a transaction in case it failed with a deadlock or elapsed lock wait timeout or listed in slave_transaction_retry_errors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 3600 -@@ -3795,7 +3795,7 @@ +@@ -3805,7 +3805,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLOW_LAUNCH_TIME VARIABLE_SCOPE GLOBAL @@ -1386,7 +1399,7 @@ VARIABLE_COMMENT If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 31536000 -@@ -3838,7 +3838,7 @@ +@@ -3848,7 +3848,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Each thread that needs to do a sort allocates a buffer of this size NUMERIC_MIN_VALUE 1024 @@ -1395,7 +1408,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -4065,7 +4065,7 @@ +@@ -4075,7 +4075,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME STORED_PROGRAM_CACHE VARIABLE_SCOPE GLOBAL @@ -1404,7 +1417,7 @@ VARIABLE_COMMENT The soft upper limit for number of cached stored routines for one connection. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 524288 -@@ -4165,7 +4165,7 @@ +@@ -4175,7 +4175,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TABLE_DEFINITION_CACHE VARIABLE_SCOPE GLOBAL @@ -1413,7 +1426,7 @@ VARIABLE_COMMENT The number of cached table definitions NUMERIC_MIN_VALUE 400 NUMERIC_MAX_VALUE 2097152 -@@ -4175,7 +4175,7 @@ +@@ -4185,7 +4185,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_OPEN_CACHE VARIABLE_SCOPE GLOBAL @@ -1422,7 +1435,7 @@ VARIABLE_COMMENT The number of cached open tables NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 1048576 -@@ -4235,7 +4235,7 @@ +@@ -4245,7 +4245,7 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME THREAD_CACHE_SIZE VARIABLE_SCOPE GLOBAL @@ -1431,7 +1444,7 @@ VARIABLE_COMMENT How many threads we should keep in a cache for reuse. These are freed after 5 minutes of idle time NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16384 -@@ -4408,7 +4408,7 @@ +@@ -4418,7 +4418,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Max size for data for an internal temporary on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@ -1440,7 +1453,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -4418,7 +4418,7 @@ +@@ -4428,7 +4428,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. Same as tmp_table_size. NUMERIC_MIN_VALUE 0 @@ -1449,7 +1462,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -4428,14 +4428,14 @@ +@@ -4438,14 +4438,14 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Alias for tmp_memory_table_size. If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 0 @@ -1466,7 +1479,7 @@ VARIABLE_COMMENT Allocation block size for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -4445,7 +4445,7 @@ +@@ -4455,7 +4455,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TRANSACTION_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1475,7 +1488,7 @@ VARIABLE_COMMENT Persistent buffer for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -4585,7 +4585,7 @@ +@@ -4595,7 +4595,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME WAIT_TIMEOUT VARIABLE_SCOPE SESSION @@ -1484,7 +1497,7 @@ VARIABLE_COMMENT The number of seconds the server waits for activity on a connection before closing it NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 31536000 -@@ -4612,7 +4612,7 @@ +@@ -4622,7 +4622,7 @@ VARIABLE_NAME LOG_TC_SIZE GLOBAL_VALUE_ORIGIN AUTO VARIABLE_SCOPE GLOBAL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 78b8ca0263d..10773514295 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2393,6 +2393,16 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f146fc25126..e04a1e2753f 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -398,6 +398,11 @@ static SEL_ARG *key_or(RANGE_OPT_PARAM *param, static SEL_ARG *key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag); +static SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param, uint keyno, + SEL_ARG *key1, SEL_ARG *key2); +static SEL_ARG *key_and_with_limit(RANGE_OPT_PARAM *param, uint keyno, + SEL_ARG *key1, SEL_ARG *key2, + uint clone_flag); static bool get_range(SEL_ARG **e1,SEL_ARG **e2,SEL_ARG *root1); bool get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key, SEL_ARG *key_tree, uchar *min_key,uint min_key_flag, @@ -409,6 +414,13 @@ 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(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" static bool sel_trees_have_common_keys(SEL_TREE *tree1, SEL_TREE *tree2, @@ -706,7 +718,8 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param, SEL_ARG *key1= (*or_tree)->keys[key_no]; SEL_ARG *key2= tree->keys[key_no]; key2->incr_refs(); - if ((result->keys[key_no]= key_or(param, key1, key2))) + if ((result->keys[key_no]= key_or_with_limit(param, key_no, key1, + key2))) { result_keys.set_bit(key_no); @@ -1872,9 +1885,13 @@ SEL_ARG::SEL_ARG(SEL_ARG &arg) :Sql_alloc() next_key_part=arg.next_key_part; max_part_no= arg.max_part_no; use_count=1; elements=1; + weight=1; next= 0; if (next_key_part) + { ++next_key_part->use_count; + weight += next_key_part->weight; + } } @@ -1891,7 +1908,7 @@ SEL_ARG::SEL_ARG(Field *f,const uchar *min_value_arg, :min_flag(0), max_flag(0), maybe_flag(0), maybe_null(f->real_maybe_null()), elements(1), use_count(1), field(f), min_value((uchar*) min_value_arg), max_value((uchar*) max_value_arg), next(0),prev(0), - next_key_part(0), color(BLACK), type(KEY_RANGE) + next_key_part(0), color(BLACK), type(KEY_RANGE), weight(1) { left=right= &null_element; max_part_no= 1; @@ -1903,7 +1920,7 @@ SEL_ARG::SEL_ARG(Field *field_,uint8 part_, :min_flag(min_flag_),max_flag(max_flag_),maybe_flag(maybe_flag_), part(part_),maybe_null(field_->real_maybe_null()), elements(1),use_count(1), field(field_), min_value(min_value_), max_value(max_value_), - next(0),prev(0),next_key_part(0),color(BLACK),type(KEY_RANGE) + next(0),prev(0),next_key_part(0),color(BLACK),type(KEY_RANGE), weight(1) { max_part_no= part+1; left=right= &null_element; @@ -5447,7 +5464,7 @@ TABLE_READ_PLAN *merge_same_index_scans(PARAM *param, SEL_IMERGE *imerge, if ((*tree)->keys[key_idx]) (*tree)->keys[key_idx]->incr_refs(); if (((*changed_tree)->keys[key_idx]= - key_or(param, key, (*tree)->keys[key_idx]))) + key_or_with_limit(param, key_idx, key, (*tree)->keys[key_idx]))) (*changed_tree)->keys_map.set_bit(key_idx); *tree= NULL; removed_cnt++; @@ -9116,7 +9133,8 @@ int and_range_trees(RANGE_OPT_PARAM *param, SEL_TREE *tree1, SEL_TREE *tree2, key2->incr_refs(); } SEL_ARG *key; - if ((result->keys[key_no]= key =key_and(param, key1, key2, flag))) + if ((result->keys[key_no]= key= key_and_with_limit(param, key_no, + key1, key2, flag))) { if (key && key->type == SEL_ARG::IMPOSSIBLE) { @@ -9678,7 +9696,7 @@ tree_or(RANGE_OPT_PARAM *param,SEL_TREE *tree1,SEL_TREE *tree2) key1->incr_refs(); key2->incr_refs(); } - if ((result->keys[key_no]= key_or(param, key1, key2))) + if ((result->keys[key_no]= key_or_with_limit(param, key_no, key1, key2))) result->keys_map.set_bit(key_no); } result->type= tree1->type; @@ -9752,6 +9770,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? @@ -9764,6 +9785,8 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, key1->right= key1->left= &null_element; key1->next= key1->prev= 0; } + uint new_weight= 0; + for (next=key1->first(); next ; next=next->next) { if (next->next_key_part) @@ -9775,17 +9798,22 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, continue; } next->next_key_part=tmp; + new_weight += 1 + tmp->weight; if (use_count) next->increment_use_count(use_count); if (param->alloced_sel_args > SEL_ARG::MAX_SEL_ARGS) break; } else + { + new_weight += 1 + key2->weight; next->next_key_part=key2; + } } if (!key1) return &null_element; // Impossible ranges key1->use_count++; + key1->weight= new_weight; key1->max_part_no= MY_MAX(key2->max_part_no, key2->part+1); return key1; } @@ -9821,6 +9849,10 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) clone_flag=swap_clone_flag(clone_flag); } // key1->part < key2->part + + if (sel_arg_and_weight_heuristic(param, key1, key2)) + return key1; + key1->use_count--; if (key1->use_count > 0) if (!(key1= key1->clone_tree(param))) @@ -9851,6 +9883,9 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) { // Both are maybe key key1->next_key_part=key_and(param, key1->next_key_part, key2->next_key_part, clone_flag); + + key1->weight= 1 + (key1->next_key_part? key1->next_key_part->weight : 0); + if (key1->next_key_part && key1->next_key_part->type == SEL_ARG::IMPOSSIBLE) return key1; @@ -9901,6 +9936,9 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) if (!new_arg) return &null_element; // End of memory new_arg->next_key_part=next; + if (new_arg->next_key_part) + new_arg->weight += new_arg->next_key_part->weight; + if (!new_tree) { new_tree=new_arg; @@ -9939,6 +9977,72 @@ get_range(SEL_ARG **e1,SEL_ARG **e2,SEL_ARG *root1) return 0; } +#ifndef DBUG_OFF +/* + Verify SEL_TREE's weight. + + Recompute the weight and compare +*/ +uint SEL_ARG::verify_weight() +{ + uint computed_weight= 0; + SEL_ARG *first_arg= first(); + + if (first_arg) + { + for (SEL_ARG *arg= first_arg; arg; arg= arg->next) + { + computed_weight++; + if (arg->next_key_part) + computed_weight+= arg->next_key_part->verify_weight(); + } + } + else + { + // first()=NULL means this is a special kind of SEL_ARG, e.g. + // SEL_ARG with type=MAYBE_KEY + computed_weight= 1; + if (next_key_part) + computed_weight += next_key_part->verify_weight(); + } + + if (computed_weight != weight) + { + sql_print_error("SEL_ARG weight mismatch: computed %u have %u\n", + computed_weight, weight); + DBUG_ASSERT(computed_weight == weight); // Fail an assertion + } + return computed_weight; +} +#endif + +static +SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param, uint keyno, + SEL_ARG *key1, SEL_ARG *key2) +{ + SEL_ARG *res= key_or(param, key1, key2); + res= enforce_sel_arg_weight_limit(param, keyno, res); +#ifndef DBUG_OFF + if (res) + res->verify_weight(); +#endif + return res; +} + + +static +SEL_ARG *key_and_with_limit(RANGE_OPT_PARAM *param, uint keyno, + SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) +{ + SEL_ARG *res= key_and(param, key1, key2, clone_flag); + res= enforce_sel_arg_weight_limit(param, keyno, res); +#ifndef DBUG_OFF + if (res) + res->verify_weight(); +#endif + return res; +} + /** Combine two range expression under a common OR. On a logical level, the @@ -10595,6 +10699,19 @@ end: } key1->use_count++; + /* Re-compute the result tree's weight. */ + { + uint new_weight= 0; + const SEL_ARG *sl; + for (sl= key1->first(); sl ; sl= sl->next) + { + new_weight++; + if (sl->next_key_part) + new_weight += sl->next_key_part->weight; + } + key1->weight= new_weight; + } + key1->max_part_no= max_part_no; return key1; } @@ -10632,6 +10749,160 @@ static bool eq_tree(SEL_ARG* a,SEL_ARG *b) } +/* + Compute the MAX(key part) in this SEL_ARG graph. +*/ +uint SEL_ARG::get_max_key_part() const +{ + const SEL_ARG *cur; + uint max_part= part; + for (cur= first(); cur ; cur=cur->next) + { + if (cur->next_key_part) + { + uint mp= cur->next_key_part->get_max_key_part(); + max_part= MY_MAX(part, mp); + } + } + return max_part; +} + + +/* + Remove the SEL_ARG graph elements which have part > max_part. + + @detail + Also update weight for the graph and any modified subgraphs. +*/ + +void prune_sel_arg_graph(SEL_ARG *sel_arg, uint max_part) +{ + SEL_ARG *cur; + DBUG_ASSERT(max_part >= sel_arg->part); + + for (cur= sel_arg->first(); cur ; cur=cur->next) + { + if (cur->next_key_part) + { + if (cur->next_key_part->part > max_part) + { + // Remove cur->next_key_part. + sel_arg->weight -= cur->next_key_part->weight; + cur->next_key_part= NULL; + } + else + { + uint old_weight= cur->next_key_part->weight; + prune_sel_arg_graph(cur->next_key_part, max_part); + sel_arg->weight -= (old_weight - cur->next_key_part->weight); + } + } + } +} + + +/* + @brief + Make sure the passed SEL_ARG graph's weight is below SEL_ARG::MAX_WEIGHT, + by cutting off branches if necessary. + + @detail + @see declaration of SEL_ARG::weight for definition of weight. + + This function attempts to reduce the graph's weight by cutting off + SEL_ARG::next_key_part connections if necessary. + + 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 + limit. +*/ + +SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, + SEL_ARG *sel_arg) +{ + if (!sel_arg || sel_arg->type != SEL_ARG::KEY_RANGE || + !param->thd->variables.optimizer_max_sel_arg_weight) + return sel_arg; + + Field *field= sel_arg->field; + uint weight1= sel_arg->weight; + + while (1) + { + if (likely(sel_arg->weight <= param->thd->variables. + optimizer_max_sel_arg_weight)) + break; + + uint max_part= sel_arg->get_max_key_part(); + if (max_part == sel_arg->part) + { + /* + We don't return NULL right away as we want to have the information + about the changed tree in the optimizer trace. + */ + sel_arg= NULL; + break; + } + + max_part--; + prune_sel_arg_graph(sel_arg, max_part); + } + + uint weight2= sel_arg? sel_arg->weight : 0; + + if (weight2 != weight1) + { + Json_writer_object wrapper(param->thd); + Json_writer_object obj(param->thd, "enforce_sel_arg_weight_limit"); + 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); + } + return sel_arg; +} + + +/* + @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) { @@ -10670,6 +10941,13 @@ SEL_ARG::insert(SEL_ARG *key) SEL_ARG *root=rb_insert(key); // rebalance tree root->use_count=this->use_count; // copy root info root->elements= this->elements+1; + /* + The new weight is: + old root's weight + +1 for the weight of the added element + + next_key_part's weight of the added element + */ + root->weight = weight + 1 + (key->next_key_part? key->next_key_part->weight: 0); root->maybe_flag=this->maybe_flag; return root; } @@ -10727,6 +11005,17 @@ SEL_ARG::tree_delete(SEL_ARG *key) root=this; this->parent= 0; + /* + Compute the weight the tree will have after the element is removed. + We remove the element itself (weight=1) + and the sub-graph connected to its next_key_part. + */ + uint new_weight= root->weight - (1 + (key->next_key_part? + key->next_key_part->weight : 0)); + + DBUG_ASSERT(root->weight >= (1 + (key->next_key_part ? + key->next_key_part->weight : 0))); + /* Unlink from list */ if (key->prev) key->prev->next=key->next; @@ -10778,6 +11067,7 @@ SEL_ARG::tree_delete(SEL_ARG *key) test_rb_tree(root,root->parent); root->use_count=this->use_count; // Fix root counters + root->weight= new_weight; root->elements=this->elements-1; root->maybe_flag=this->maybe_flag; DBUG_RETURN(root); diff --git a/sql/opt_range.h b/sql/opt_range.h index 664e821f8d1..50cd43c0e85 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -223,6 +223,50 @@ class RANGE_OPT_PARAM; We avoid consuming too much memory by setting a limit on the number of SEL_ARG object we can construct during one range analysis invocation. + + 5. SEL_ARG GRAPH WEIGHT + + A SEL_ARG graph has a property we call weight, and we define it as follows: + + <definition> + If the SEL_ARG graph does not have any node with multiple incoming + next_key_part edges, then its weight is the number of SEL_ARG objects used. + + If there is a node with multiple incoming next_key_part edges, clone that + node, (and the nodes connected to it via prev/next links) and redirect one + of the incoming next_key_part edges to the clone. + + Continue with cloning until we get a graph that has no nodes with multiple + incoming next_key_part edges. Then, the number of SEL_ARG objects in the + graph is the weight of the original graph. + </definition> + + Example: + + kp1 $ kp2 $ kp3 + $ $ + | +-------+ $ $ + \->| kp1=2 |--$--------------$-+ + +-------+ $ $ | +--------+ + | $ $ ==>| kp3=11 | + +-------+ $ $ | +--------+ + | kp1>3 |--$--------------$-+ | + +-------+ $ $ +--------+ + $ $ | kp3=14 | + $ $ +--------+ + $ $ | + $ $ +--------+ + $ $ | kp3=14 | + $ $ +--------+ + + Here, the weight is 2 + 2*3=8. + + The rationale behind using this definition of weight is: + - it has the same order-of-magnitude as the number of ranges that the + SEL_ARG graph is describing, + - it is a lot easier to compute than computing the number of ranges, + - it can be updated incrementally when performing AND/OR operations on + parts of the graph. */ class SEL_ARG :public Sql_alloc @@ -236,6 +280,9 @@ public: /* The ordinal number the least significant component encountered in the ranges of the SEL_ARG tree (the first component has number 1) + + Note: this number is currently not precise, it is an upper bound. + @seealso SEL_ARG::get_max_key_part() */ uint16 max_part_no; /* @@ -263,6 +310,17 @@ public: enum leaf_color { BLACK,RED } color; enum Type { IMPOSSIBLE, MAYBE, MAYBE_KEY, KEY_RANGE } type; + /* + For R-B root nodes only: the graph weight, as defined above in the + SEL_ARG GRAPH WEIGHT section. + */ + uint weight; + enum { MAX_WEIGHT = 32000 }; +#ifndef DBUG_OFF + uint verify_weight(); +#endif + + /* See RANGE_OPT_PARAM::alloced_sel_args */ enum { MAX_SEL_ARGS = 16000 }; SEL_ARG() {} @@ -273,7 +331,7 @@ public: SEL_ARG(enum Type type_arg) :min_flag(0), max_part_no(0) /* first key part means 1. 0 mean 'no parts'*/, elements(1),use_count(1),left(0),right(0), - next_key_part(0), color(BLACK), type(type_arg) + next_key_part(0), color(BLACK), type(type_arg), weight(1) {} /** returns true if a range predicate is equal. Use all_same() @@ -287,6 +345,9 @@ public: return true; return cmp_min_to_min(arg) == 0 && cmp_max_to_max(arg) == 0; } + + uint get_max_key_part() const; + /** returns true if all the predicates in the keypart tree are equal */ diff --git a/sql/sql_class.h b/sql/sql_class.h index 79dcd47bbc2..eaa97c2778d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -815,6 +815,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 12b6ea96182..f56bd5d8875 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -6693,6 +6693,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 " |