maria-developers team mailing list archive

[Daniel Black <daniel.black@xxxxxxxxxxx>]

So this is making log-queries-not-using-indexes to take on the same
meaning as min_examined_row_limit for the case of no-index and both seam
to apply?

Is thd->get_examined_row_count() the right comparison value if the
non-indexed table had much fewer rows examined?

Note -
https://mariadb.com/kb/en/mariadb/server-system-variables/#log_queries_not_using_indexes
needs updating and needs to reference min_examined_row_limit as well.

Are log_slow_admin_statements / log_slow_slave_statements going to be
modified in the same way and min-examined-row-limit to be deprecated?

Is giving log-queries-not-using-indexes a default value of 5000 (as
suggested for min-examined-row limit) applicable here?


On 14/10/16 10:09, Nirbhay Choubey wrote:
> revision-id: d0064c6e94414cfd6bbfcf171b1efabababe1d2e (mariadb-10.2.1-55-gd0064c6)
> parent(s): 0d70fd0f9b7b8480b6053ef2dfcb55d917de2bca
> author: Nirbhay Choubey
> committer: Nirbhay Choubey
> timestamp: 2016-10-13 19:09:53 -0400
> message:
> 
> MDEV-7635: Convert log_queries_not_using_indexes to ulong
> 
> ---
>  mysql-test/r/mysqld--help.result                       |  7 ++++---
>  mysql-test/r/show_check.result                         | 10 +++++-----
>  mysql-test/r/variables.result                          |  4 ++--
>  .../r/log_queries_not_using_indexes_basic.result       | 18 ++++++++++--------
>  .../t/log_queries_not_using_indexes_basic.test         | 14 +++++++-------
>  mysql-test/t/show_check.test                           |  4 ++--
>  sql/mysqld.cc                                          |  2 +-
>  sql/mysqld.h                                           |  2 +-
>  sql/sql_parse.cc                                       |  3 ++-
>  sql/sys_vars.cc                                        |  9 +++++----
>  10 files changed, 39 insertions(+), 34 deletions(-)
> 
> diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
> index cb1399d..2a570f8 100644
> --- a/mysql-test/r/mysqld--help.result
> +++ b/mysql-test/r/mysqld--help.result
> @@ -365,9 +365,10 @@ The following options may be given as the first argument:
>   --log-isam[=name]   Log all MyISAM changes to file.
>   --log-output=name   How logs should be written. Any combination of: NONE, 
>   FILE, TABLE
> - --log-queries-not-using-indexes 
> + --log-queries-not-using-indexes[=#] 
>   Log queries that are executed without benefit of any
> - index to the slow log if it is open
> + index and that examined fewer rows than specified to the
> + slow log if it is open
>   --log-short-format  Don't log extra information to update and slow-query
>   logs.
>   --log-slave-updates Tells the slave to log the updates from the slave thread
> @@ -1257,7 +1258,7 @@ log-bin-trust-function-creators FALSE
>  log-error 
>  log-isam myisam.log
>  log-output FILE
> -log-queries-not-using-indexes FALSE
> +log-queries-not-using-indexes 0
>  log-short-format FALSE
>  log-slave-updates FALSE
>  log-slow-admin-statements FALSE
> diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
> index 19a2597..db1a0ee 100644
> --- a/mysql-test/r/show_check.result
> +++ b/mysql-test/r/show_check.result
> @@ -1228,27 +1228,27 @@ use test;
>  flush status;
>  show variables like "log_queries_not_using_indexes";
>  Variable_name	Value
> -log_queries_not_using_indexes	ON
> +log_queries_not_using_indexes	1
>  select 1 from information_schema.tables limit 1;
>  1
>  1
>  show status like 'slow_queries';
>  Variable_name	Value
>  Slow_queries	1
> -set global log_queries_not_using_indexes=OFF;
> +set global log_queries_not_using_indexes=0;
>  show variables like "log_queries_not_using_indexes";
>  Variable_name	Value
> -log_queries_not_using_indexes	OFF
> +log_queries_not_using_indexes	0
>  select 1 from information_schema.tables limit 1;
>  1
>  1
>  show status like 'slow_queries';
>  Variable_name	Value
>  Slow_queries	1
> -set global log_queries_not_using_indexes=ON;
> +set global log_queries_not_using_indexes=1;
>  show variables like "log_queries_not_using_indexes";
>  Variable_name	Value
> -log_queries_not_using_indexes	ON
> +log_queries_not_using_indexes	1
>  select 1 from information_schema.tables limit 1;
>  1
>  1
> diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
> index 50379a5..1423284 100644
> --- a/mysql-test/r/variables.result
> +++ b/mysql-test/r/variables.result
> @@ -995,10 +995,10 @@ select @@log_queries_not_using_indexes;
>  0
>  show variables like 'log_queries_not_using_indexes';
>  Variable_name	Value
> -log_queries_not_using_indexes	OFF
> +log_queries_not_using_indexes	0
>  select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes';
>  VARIABLE_NAME	VARIABLE_VALUE
> -LOG_QUERIES_NOT_USING_INDEXES	OFF
> +LOG_QUERIES_NOT_USING_INDEXES	0
>  select @@"";
>  ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '""' at line 1
>  select @@&;
> diff --git a/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result b/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result
> index fcb5abb..9dafcb7 100644
> --- a/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result
> +++ b/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result
> @@ -29,10 +29,12 @@ SELECT @@global.log_queries_not_using_indexes;
>  @@global.log_queries_not_using_indexes
>  0
>  SET GLOBAL log_queries_not_using_indexes= ON;
> +ERROR 42000: Incorrect argument type to variable 'log_queries_not_using_indexes'
>  SELECT @@global.log_queries_not_using_indexes;
>  @@global.log_queries_not_using_indexes
> -1
> +0
>  SET GLOBAL log_queries_not_using_indexes= OFF;
> +ERROR 42000: Incorrect argument type to variable 'log_queries_not_using_indexes'
>  SELECT @@global.log_queries_not_using_indexes;
>  @@global.log_queries_not_using_indexes
>  0
> @@ -47,20 +49,20 @@ SELECT @@global.log_queries_not_using_indexes;
>  @@global.log_queries_not_using_indexes
>  0
>  SET @@global.log_queries_not_using_indexes= 'DEFAULT';
> -ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'DEFAULT'
> +ERROR 42000: Incorrect argument type to variable 'log_queries_not_using_indexes'
>  SET @@global.log_queries_not_using_indexes= 'true';
> -ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'true'
> +ERROR 42000: Incorrect argument type to variable 'log_queries_not_using_indexes'
>  SET @@global.log_queries_not_using_indexes= BLABLA;
> -ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'BLABLA'
> +ERROR 42000: Incorrect argument type to variable 'log_queries_not_using_indexes'
>  SET @@global.log_queries_not_using_indexes= 25;
> -ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of '25'
>  SET GLOBAL log_queries_not_using_indexes= -1;
> -ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of '-1'
> +Warnings:
> +Warning	1292	Truncated incorrect log_queries_not_using_indexes value: '-1'
>  SET @badvar= 'true';
>  SET @@global.log_queries_not_using_indexes= @badvar;
> -ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'true'
> +ERROR 42000: Incorrect argument type to variable 'log_queries_not_using_indexes'
>  SET GLOBAL log_queries_not_using_indexes= 'DEFAULT';
> -ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'DEFAULT'
> +ERROR 42000: Incorrect argument type to variable 'log_queries_not_using_indexes'
>  SET log_queries_not_using_indexes= TRUE;
>  ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL
>  SET SESSION log_queries_not_using_indexes= TRUE;
> diff --git a/mysql-test/suite/sys_vars/t/log_queries_not_using_indexes_basic.test b/mysql-test/suite/sys_vars/t/log_queries_not_using_indexes_basic.test
> index a726bff..806030d 100644
> --- a/mysql-test/suite/sys_vars/t/log_queries_not_using_indexes_basic.test
> +++ b/mysql-test/suite/sys_vars/t/log_queries_not_using_indexes_basic.test
> @@ -47,9 +47,11 @@ SELECT @@global.log_queries_not_using_indexes;
>  SET GLOBAL log_queries_not_using_indexes= DEFAULT;
>  SELECT @@global.log_queries_not_using_indexes;
>  
> +--error ER_WRONG_TYPE_FOR_VAR
>  SET GLOBAL log_queries_not_using_indexes= ON;
>  SELECT @@global.log_queries_not_using_indexes;
>  
> +--error ER_WRONG_TYPE_FOR_VAR
>  SET GLOBAL log_queries_not_using_indexes= OFF;
>  SELECT @@global.log_queries_not_using_indexes;
>  
> @@ -66,26 +68,24 @@ SELECT @@global.log_queries_not_using_indexes;
>  # Check if the value in GLOBAL Table matches value in variable  #
>  #################################################################
>  
> ---error ER_WRONG_VALUE_FOR_VAR
> +--error ER_WRONG_TYPE_FOR_VAR
>  SET @@global.log_queries_not_using_indexes= 'DEFAULT';
>  
> ---error ER_WRONG_VALUE_FOR_VAR
> +--error ER_WRONG_TYPE_FOR_VAR
>  SET @@global.log_queries_not_using_indexes= 'true';
>  
> ---error ER_WRONG_VALUE_FOR_VAR
> +--error ER_WRONG_TYPE_FOR_VAR
>  SET @@global.log_queries_not_using_indexes= BLABLA;
>  
> ---error ER_WRONG_VALUE_FOR_VAR
>  SET @@global.log_queries_not_using_indexes= 25;
>  
> ---error ER_WRONG_VALUE_FOR_VAR
>  SET GLOBAL log_queries_not_using_indexes= -1;
>  
>  SET @badvar= 'true';
> ---error ER_WRONG_VALUE_FOR_VAR
> +--error ER_WRONG_TYPE_FOR_VAR
>  SET @@global.log_queries_not_using_indexes= @badvar;
>  
> ---error ER_WRONG_VALUE_FOR_VAR
> +--error ER_WRONG_TYPE_FOR_VAR
>  SET GLOBAL log_queries_not_using_indexes= 'DEFAULT';
>  
>  --error ER_GLOBAL_VARIABLE
> diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
> index a14c42d..9644b77 100644
> --- a/mysql-test/t/show_check.test
> +++ b/mysql-test/t/show_check.test
> @@ -945,11 +945,11 @@ flush status;
>  show variables like "log_queries_not_using_indexes";
>  select 1 from information_schema.tables limit 1;
>  show status like 'slow_queries';
> -set global log_queries_not_using_indexes=OFF;
> +set global log_queries_not_using_indexes=0;
>  show variables like "log_queries_not_using_indexes";
>  select 1 from information_schema.tables limit 1;
>  show status like 'slow_queries';
> -set global log_queries_not_using_indexes=ON;
> +set global log_queries_not_using_indexes=1;
>  show variables like "log_queries_not_using_indexes";
>  select 1 from information_schema.tables limit 1;
>  show status like 'slow_queries';
> diff --git a/sql/mysqld.cc b/sql/mysqld.cc
> index 28e91e2..f02f2b0 100644
> --- a/sql/mysqld.cc
> +++ b/sql/mysqld.cc
> @@ -395,7 +395,7 @@ my_bool disable_log_notes;
>  static my_bool opt_abort;
>  ulonglong log_output_options;
>  my_bool opt_userstat_running;
> -my_bool opt_log_queries_not_using_indexes= 0;
> +ulong opt_log_queries_not_using_indexes= 0;
>  bool opt_error_log= IF_WIN(1,0);
>  bool opt_disable_networking=0, opt_skip_show_db=0;
>  bool opt_skip_name_resolve=0;
> diff --git a/sql/mysqld.h b/sql/mysqld.h
> index 846a01a..d577a9b 100644
> --- a/sql/mysqld.h
> +++ b/sql/mysqld.h
> @@ -114,7 +114,7 @@ extern my_bool opt_backup_history_log;
>  extern my_bool opt_backup_progress_log;
>  extern ulonglong log_output_options;
>  extern ulong log_backup_output_options;
> -extern my_bool opt_log_queries_not_using_indexes;
> +extern ulong opt_log_queries_not_using_indexes;
>  extern bool opt_disable_networking, opt_skip_show_db;
>  extern bool opt_skip_name_resolve;
>  extern bool opt_ignore_builtin_innodb;
> diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
> index c76e22a..de2da9e 100644
> --- a/sql/sql_parse.cc
> +++ b/sql/sql_parse.cc
> @@ -2417,7 +2417,8 @@ void log_slow_statement(THD *thd)
>    if (((thd->server_status & SERVER_QUERY_WAS_SLOW) ||
>         ((thd->server_status &
>           (SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED)) &&
> -        opt_log_queries_not_using_indexes &&
> +        (opt_log_queries_not_using_indexes > 0) &&
> +        (thd->get_examined_row_count() >= opt_log_queries_not_using_indexes) &&
>          !(sql_command_flags[thd->lex->sql_command] & CF_STATUS_COMMAND))) &&
>        thd->get_examined_row_count() >= thd->variables.min_examined_row_limit)
>    {
> diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
> index 47a0a38..d11d690 100644
> --- a/sql/sys_vars.cc
> +++ b/sql/sys_vars.cc
> @@ -1178,12 +1178,13 @@ static Sys_var_charptr Sys_log_error(
>         CMD_LINE(OPT_ARG, OPT_LOG_ERROR),
>         IN_FS_CHARSET, DEFAULT(disabled_my_option));
>  
> -static Sys_var_mybool Sys_log_queries_not_using_indexes(
> +static Sys_var_ulong Sys_log_queries_not_using_indexes(
>         "log_queries_not_using_indexes",
> -       "Log queries that are executed without benefit of any index to the "
> -       "slow log if it is open",
> +       "Log queries that are executed without benefit of any index and that "
> +       "examined fewer rows than specified to the slow log if it is open",
>         GLOBAL_VAR(opt_log_queries_not_using_indexes),
> -       CMD_LINE(OPT_ARG), DEFAULT(FALSE));
> +       CMD_LINE(OPT_ARG), VALID_RANGE(0, UINT_MAX), DEFAULT(0),
> +       BLOCK_SIZE(1));
>  
>  static Sys_var_mybool Sys_log_slow_admin_statements(
>         "log_slow_admin_statements",
> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
>