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_u... 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@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits