← Back to team overview

maria-developers team mailing list archive

Fwd: Re: [Commits] 68b7e231cb3: MDEV-17255: New optimizer defaults and ANALYZE TABLE

 

----- Forwarded message from Igor Babaev <igor@xxxxxxxxxxx> -----

Date: Sun, 25 Nov 2018 11:45:34 -0800
From: Igor Babaev <igor@xxxxxxxxxxx>
To: Sergey Petrunia <sergey@xxxxxxxxxxx>
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@xxxxxxxxxxx>
Reply-To: 	maria-developers@xxxxxxxxxxxxxxxxxxx
To: 	Varun <varunraiko1803@xxxxxxxxx>, maria-developers@xxxxxxxxxxxxxxxxxxx
CC: 	commits@xxxxxxxxxxx



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@xxxxxxxxxxx
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