----- Forwarded message from Igor Babaev <igor@mariadb.com> ----- Date: Sun, 25 Nov 2018 11:45:34 -0800 From: Igor Babaev <igor@mariadb.com> To: Sergey Petrunia <sergey@mariadb.com> Subject: Fwd: Re: [Commits] 68b7e231cb3: MDEV-17255: New optimizer defaults and ANALYZE TABLE Hi Sergey, PREFERABLY_FOR_READS sounds confusing, because the stat tables still are used for UPDATE/DELETE. Maybe PREFERABLY_FOR_QUERIES? Besides, don't forget that you need COMPLEMENTARY_FOR_READS[QUERIES] as well. Regards, Igor. -------- Forwarded Message -------- Subject: Re: [Commits] 68b7e231cb3: MDEV-17255: New optimizer defaults and ANALYZE TABLE Date: Sat, 24 Nov 2018 21:47:57 +0300 From: Sergey Petrunia <sergey@mariadb.com> Reply-To: maria-developers@lists.launchpad.net To: Varun <varunraiko1803@gmail.com>, maria-developers@lists.launchpad.net CC: commits@mariadb.org 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 _______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog