diff options
| -rw-r--r-- | mysql-test/include/default_mysqld.cnf | 1 | ||||
| -rw-r--r-- | mysql-test/main/mysqld--help.result | 5 | ||||
| -rw-r--r-- | mysql-test/main/stat_tables.result | 56 | ||||
| -rw-r--r-- | mysql-test/main/stat_tables.test | 42 | ||||
| -rw-r--r-- | mysql-test/main/stat_tables_innodb.result | 56 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_embedded.result | 6 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result | 6 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/use_stat_tables_basic.result | 2 | ||||
| -rw-r--r-- | sql/sql_admin.cc | 2 | ||||
| -rw-r--r-- | sql/sql_statistics.cc | 5 | ||||
| -rw-r--r-- | sql/sql_statistics.h | 27 | ||||
| -rw-r--r-- | sql/sys_vars.cc | 5 |
12 files changed, 198 insertions, 15 deletions
diff --git a/mysql-test/include/default_mysqld.cnf b/mysql-test/include/default_mysqld.cnf index 69a2b58288b..edae06ee7be 100644 --- a/mysql-test/include/default_mysqld.cnf +++ b/mysql-test/include/default_mysqld.cnf @@ -107,6 +107,7 @@ loose-performance-schema-consumer-thread-instrumentation=ON binlog-direct-non-transactional-updates default-storage-engine=myisam +use_stat_tables=preferably loose-ssl-ca=@ENV.MYSQL_TEST_DIR/std_data/cacert.pem loose-ssl-cert=@ENV.MYSQL_TEST_DIR/std_data/server-cert.pem diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 9c7e1332285..1e6cdb3ec2c 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -1358,7 +1358,8 @@ The following specify which files/extra groups are read (specified before remain (usually get from GUI tools) --use-stat-tables=name Specifies how to use system statistics tables. One of: - NEVER, COMPLEMENTARY, PREFERABLY + NEVER, COMPLEMENTARY, PREFERABLY, + COMPLEMENTARY_FOR_QUERIES, PREFERABLY_FOR_QUERIES -u, --user=name Run mysqld daemon as user. --userstat Enables statistics gathering for USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS and TABLE_STATISTICS @@ -1723,7 +1724,7 @@ transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 transaction-read-only FALSE updatable-views-with-limit YES -use-stat-tables PREFERABLY +use-stat-tables PREFERABLY_FOR_QUERIES userstat FALSE verbose TRUE wait-timeout 28800 diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index fe3c83d5a23..bcd9b4f062e 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -625,3 +625,59 @@ MAX(pk) NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-17255: New optimizer defaults and ANALYZE TABLE +# +create table t1 (a int, b int); +insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10); +set use_stat_tables= preferably_for_queries; +# +# with use_stat_tables= PREFERABLY_FOR_QUERIES +# analyze table t1 will not collect statistics +# +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select * from mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +analyze +select * from t1 where a = 1 and b=3; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where +# +# with use_stat_tables= PREFERABLY_FOR_QUERIES +# analyze table t1 will collect statistics if we use PERSISTENT +# for columns, indexes or everything +# +analyze table t1 persistent for columns (a) indexes (); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +select * from mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 1 4 0.0000 4.0000 2.5000 0 NULL NULL +# filtered shows that we used the data from stat tables +analyze +select * from t1 where a = 1 and b=3; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 25.00 10.00 Using where +# +# with use_stat_tables= PREFERABLY +# analyze table t1 will collect statistics +# +set use_stat_tables=PREFERABLY; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +select * from mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 1 4 0.0000 4.0000 2.5000 0 NULL NULL +test t1 b 2 10 0.0000 4.0000 1.1111 0 NULL NULL +# filtered shows that we used the data from stat tables +analyze +select * from t1 where a=1 and b=3; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 2.78 10.00 Using where +drop table t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/stat_tables.test b/mysql-test/main/stat_tables.test index 0005e48e0e7..2c1d53a2583 100644 --- a/mysql-test/main/stat_tables.test +++ b/mysql-test/main/stat_tables.test @@ -400,5 +400,47 @@ CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); SELECT MAX(pk) FROM t1; DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-17255: New optimizer defaults and ANALYZE TABLE +--echo # + +create table t1 (a int, b int); +insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10); +set use_stat_tables= preferably_for_queries; +--echo # +--echo # with use_stat_tables= PREFERABLY_FOR_QUERIES +--echo # analyze table t1 will not collect statistics +--echo # + +analyze table t1; +select * from mysql.column_stats; +analyze +select * from t1 where a = 1 and b=3; +--echo # +--echo # with use_stat_tables= PREFERABLY_FOR_QUERIES +--echo # analyze table t1 will collect statistics if we use PERSISTENT +--echo # for columns, indexes or everything +--echo # + +analyze table t1 persistent for columns (a) indexes (); +select * from mysql.column_stats; +--echo # filtered shows that we used the data from stat tables +analyze +select * from t1 where a = 1 and b=3; + +--echo # +--echo # with use_stat_tables= PREFERABLY +--echo # analyze table t1 will collect statistics +--echo # + +set use_stat_tables=PREFERABLY; +analyze table t1; +select * from mysql.column_stats; +--echo # filtered shows that we used the data from stat tables +analyze +select * from t1 where a=1 and b=3; +drop table t1; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index a72e3cec1d4..fc37ece99cb 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -652,5 +652,61 @@ MAX(pk) NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-17255: New optimizer defaults and ANALYZE TABLE +# +create table t1 (a int, b int); +insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10); +set use_stat_tables= preferably_for_queries; +# +# with use_stat_tables= PREFERABLY_FOR_QUERIES +# analyze table t1 will not collect statistics +# +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select * from mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +analyze +select * from t1 where a = 1 and b=3; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where +# +# with use_stat_tables= PREFERABLY_FOR_QUERIES +# analyze table t1 will collect statistics if we use PERSISTENT +# for columns, indexes or everything +# +analyze table t1 persistent for columns (a) indexes (); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +select * from mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 1 4 0.0000 4.0000 2.5000 0 NULL NULL +# filtered shows that we used the data from stat tables +analyze +select * from t1 where a = 1 and b=3; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 25.00 10.00 Using where +# +# with use_stat_tables= PREFERABLY +# analyze table t1 will collect statistics +# +set use_stat_tables=PREFERABLY; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +select * from mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 1 4 0.0000 4.0000 2.5000 0 NULL NULL +test t1 b 2 10 0.0000 4.0000 1.1111 0 NULL NULL +# filtered shows that we used the data from stat tables +analyze +select * from t1 where a=1 and b=3; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 2.78 10.00 Using where +drop table t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; 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 85faa31c064..3efdcca8e44 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -4509,15 +4509,15 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME USE_STAT_TABLES SESSION_VALUE PREFERABLY GLOBAL_VALUE PREFERABLY -GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE PREFERABLY +GLOBAL_VALUE_ORIGIN CONFIG +DEFAULT_VALUE PREFERABLY_FOR_QUERIES VARIABLE_SCOPE SESSION VARIABLE_TYPE ENUM VARIABLE_COMMENT Specifies how to use system statistics tables NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST NEVER,COMPLEMENTARY,PREFERABLY +ENUM_VALUE_LIST NEVER,COMPLEMENTARY,PREFERABLY,COMPLEMENTARY_FOR_QUERIES,PREFERABLY_FOR_QUERIES READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME WAIT_TIMEOUT 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 36a02f6e79f..e49e96e3587 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -5573,15 +5573,15 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME USE_STAT_TABLES SESSION_VALUE PREFERABLY GLOBAL_VALUE PREFERABLY -GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE PREFERABLY +GLOBAL_VALUE_ORIGIN CONFIG +DEFAULT_VALUE PREFERABLY_FOR_QUERIES VARIABLE_SCOPE SESSION VARIABLE_TYPE ENUM VARIABLE_COMMENT Specifies how to use system statistics tables NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST NEVER,COMPLEMENTARY,PREFERABLY +ENUM_VALUE_LIST NEVER,COMPLEMENTARY,PREFERABLY,COMPLEMENTARY_FOR_QUERIES,PREFERABLY_FOR_QUERIES READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME WAIT_TIMEOUT diff --git a/mysql-test/suite/sys_vars/r/use_stat_tables_basic.result b/mysql-test/suite/sys_vars/r/use_stat_tables_basic.result index 3db981c7e86..1de75b65caf 100644 --- a/mysql-test/suite/sys_vars/r/use_stat_tables_basic.result +++ b/mysql-test/suite/sys_vars/r/use_stat_tables_basic.result @@ -10,7 +10,7 @@ SET @@global.use_stat_tables = 2; SET @@global.use_stat_tables = DEFAULT; SELECT @@global.use_stat_tables; @@global.use_stat_tables -PREFERABLY +PREFERABLY_FOR_QUERIES SET @@global.use_stat_tables = 0; SELECT @@global.use_stat_tables; @@global.use_stat_tables diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index d0d959de8f9..b39103e382a 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -767,7 +767,7 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, } collect_eis= (table->table->s->table_category == TABLE_CATEGORY_USER && - (get_use_stat_tables_mode(thd) > NEVER || + (check_eits_collection_allowed(thd) || lex->with_persistent_for_clause)); diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index 04806f07b3b..8c88f7f927f 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3720,9 +3720,8 @@ void set_statistics_for_table(THD *thd, TABLE *table) { TABLE_STATISTICS_CB *stats_cb= &table->s->stats_cb; Table_statistics *read_stats= stats_cb->table_stats; - Use_stat_tables_mode use_stat_table_mode= get_use_stat_tables_mode(thd); table->used_stat_records= - (use_stat_table_mode <= COMPLEMENTARY || + (!check_eits_preferred(thd) || !table->stats_is_read || read_stats->cardinality_is_null) ? table->file->stats.records : read_stats->cardinality; KEY *key_info, *key_info_end; @@ -3730,7 +3729,7 @@ void set_statistics_for_table(THD *thd, TABLE *table) key_info < key_info_end; key_info++) { key_info->is_statistics_from_stat_tables= - (use_stat_table_mode > COMPLEMENTARY && + (check_eits_preferred(thd) && table->stats_is_read && key_info->read_stats->avg_frequency_is_inited() && key_info->read_stats->get_avg_frequency(0) > 0.5); diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 39cddf95188..8439ac8db53 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -16,12 +16,26 @@ #ifndef SQL_STATISTICS_H #define SQL_STATISTICS_H +/* + For COMPLEMENTARY_FOR_QUERIES and PREFERABLY_FOR_QUERIES they are + similar to the COMPLEMENTARY and PREFERABLY respectively except that + with these values we would not be collecting EITS for queries like + ANALYZE TABLE t1; + To collect EITS with these values, we have to use PERSISITENT FOR + analyze table t1 persistent for + columns (col1,col2...) index (idx1, idx2...) + or + analyze table t1 persistent for all +*/ + typedef enum enum_use_stat_tables_mode { NEVER, COMPLEMENTARY, PREFERABLY, + COMPLEMENTARY_FOR_QUERIES, + PREFERABLY_FOR_QUERIES } Use_stat_tables_mode; typedef @@ -87,6 +101,19 @@ Use_stat_tables_mode get_use_stat_tables_mode(THD *thd) { return (Use_stat_tables_mode) (thd->variables.use_stat_tables); } +inline +bool check_eits_collection_allowed(THD *thd) +{ + return (get_use_stat_tables_mode(thd) == COMPLEMENTARY || + get_use_stat_tables_mode(thd) == PREFERABLY); +} + +inline +bool check_eits_preferred(THD *thd) +{ + return (get_use_stat_tables_mode(thd) == PREFERABLY || + get_use_stat_tables_mode(thd) == PREFERABLY_FOR_QUERIES); +} int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables); int collect_statistics_for_table(THD *thd, TABLE *table); diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 8c23db11e35..cf3067ca801 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -5855,12 +5855,13 @@ static Sys_var_ulong Sys_progress_report_time( VALID_RANGE(0, UINT_MAX), DEFAULT(5), BLOCK_SIZE(1)); const char *use_stat_tables_modes[] = - {"NEVER", "COMPLEMENTARY", "PREFERABLY", 0}; + {"NEVER", "COMPLEMENTARY", "PREFERABLY", + "COMPLEMENTARY_FOR_QUERIES", "PREFERABLY_FOR_QUERIES", 0}; static Sys_var_enum Sys_optimizer_use_stat_tables( "use_stat_tables", "Specifies how to use system statistics tables", SESSION_VAR(use_stat_tables), CMD_LINE(REQUIRED_ARG), - use_stat_tables_modes, DEFAULT(2)); + use_stat_tables_modes, DEFAULT(4)); static Sys_var_ulong Sys_histogram_size( "histogram_size", |