[Commits] 68b7e231cb3: MDEV-17255: New optimizer defaults and ANALYZE TABLE
revision-id: 68b7e231cb3a1cdfbd968c2cbf72687b29b6d2da (mariadb-10.3.6-209-g68b7e231cb3) parent(s): b9a9055793ab8b9a50200e2f55602463627dedd3 author: Varun Gupta committer: Varun Gupta timestamp: 2018-11-16 23:48:11 +0530 message: MDEV-17255: New optimizer defaults and ANALYZE TABLE Added another value for the use_stat_tables system variable --- sql/opt_range.cc | 2 +- sql/sql_admin.cc | 3 ++- sql/sql_statistics.h | 1 + sql/sys_vars.cc | 4 ++-- 4 files changed, 6 insertions(+), 4 deletions(-) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index d6db365a8a2..f501a5ae085 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3158,7 +3158,7 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) if (thd->variables.optimizer_use_condition_selectivity > 2 && !bitmap_is_clear_all(used_fields) && - thd->variables.use_stat_tables > 0) + get_use_stat_tables_mode(thd) > NEVER) { PARAM param; MEM_ROOT alloc; diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index d0d959de8f9..b5c732737b7 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -767,7 +767,8 @@ 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 || + ((get_use_stat_tables_mode(thd) > NEVER && + get_use_stat_tables_mode(thd) < PREFERABLY_FOR_READS) || lex->with_persistent_for_clause)); diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 39cddf95188..a942c05be09 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -22,6 +22,7 @@ enum enum_use_stat_tables_mode NEVER, COMPLEMENTARY, PREFERABLY, + PREFERABLY_FOR_READS, } Use_stat_tables_mode; typedef diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 92c7d329bb9..3652d728fc1 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -5841,12 +5841,12 @@ 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", "PREFERABLY_FOR_READS", 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(0)); + use_stat_tables_modes, DEFAULT(3)); static Sys_var_ulong Sys_histogram_size( "histogram_size",
Hi Varun, On Fri, Nov 16, 2018 at 11:48:59PM +0530, Varun wrote:
revision-id: 68b7e231cb3a1cdfbd968c2cbf72687b29b6d2da (mariadb-10.3.6-209-g68b7e231cb3) parent(s): b9a9055793ab8b9a50200e2f55602463627dedd3
Does the above say the patch is going into 10.3? The MDEV has fixVersion=10.4 and the parent revision is also in 10.4. So I assume the target version is 10.4?
author: Varun Gupta committer: Varun Gupta timestamp: 2018-11-16 23:48:11 +0530 message:
MDEV-17255: New optimizer defaults and ANALYZE TABLE
Added another value for the use_stat_tables system variable
Please mention in the commit comment: - the name of the new value - the fact that the new value is now the default.
--- sql/opt_range.cc | 2 +- sql/sql_admin.cc | 3 ++- sql/sql_statistics.h | 1 + sql/sys_vars.cc | 4 ++-- 4 files changed, 6 insertions(+), 4 deletions(-)
Please run the testsuite. I did the 'main' testsuite and: - main.mysqld--help, main.stat_tables_disabled - these need updates - a few other tests need to be updated as now opening any table will now cause the server to attept to read EITS stats for it. Please also add test coverage for this particular MDEV. This should be a testcase that shows that - with PREFERABLY, ANALYZE will collect EITS stats, SELECT will use them. - with PREFERABLY_FOR_READS : -- ANALYZE will not collect EITS stats -- ANALYZE .. PERSISTENT FOR ... will update the EITS stats. -- SELECT will use them. .
diff --git a/sql/opt_range.cc b/sql/opt_range.cc index d6db365a8a2..f501a5ae085 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3158,7 +3158,7 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond)
if (thd->variables.optimizer_use_condition_selectivity > 2 && !bitmap_is_clear_all(used_fields) && - thd->variables.use_stat_tables > 0) + get_use_stat_tables_mode(thd) > NEVER) { PARAM param; MEM_ROOT alloc; diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index d0d959de8f9..b5c732737b7 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -767,7 +767,8 @@ 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 || + ((get_use_stat_tables_mode(thd) > NEVER && + get_use_stat_tables_mode(thd) < PREFERABLY_FOR_READS) || lex->with_persistent_for_clause));
diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 39cddf95188..a942c05be09 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -22,6 +22,7 @@ enum enum_use_stat_tables_mode NEVER, COMPLEMENTARY, PREFERABLY,
Please add a comment describing the new value.
+ PREFERABLY_FOR_READS, } Use_stat_tables_mode;
typedef diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 92c7d329bb9..3652d728fc1 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -5841,12 +5841,12 @@ 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", "PREFERABLY_FOR_READS", 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(0)); + use_stat_tables_modes, DEFAULT(3));
static Sys_var_ulong Sys_histogram_size( "histogram_size",
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
participants (2)
-
Sergey Petrunia -
Varun