=== added file 'mysql-test/suite/sys_vars/r/innodb_stats_modified_counter_basic.result' --- mysql-test/suite/sys_vars/r/innodb_stats_modified_counter_basic.result 1970-01-01 00:00:00 +0000 +++ mysql-test/suite/sys_vars/r/innodb_stats_modified_counter_basic.result 2014-11-19 18:27:34 +0000 @@ -0,0 +1,56 @@ +SET @start_global_value = @@global.innodb_stats_modified_counter; +SELECT @start_global_value; +@start_global_value +0 +Valid values are one or above +select @@global.innodb_stats_modified_counter >=1; +@@global.innodb_stats_modified_counter >=1 +0 +select @@global.innodb_stats_modified_counter; +@@global.innodb_stats_modified_counter +0 +select @@session.innodb_stats_modified_counter; +ERROR HY000: Variable 'innodb_stats_modified_counter' is a GLOBAL variable +show global variables like 'innodb_stats_modified_counter'; +Variable_name Value +innodb_stats_modified_counter 0 +show session variables like 'innodb_stats_modified_counter'; +Variable_name Value +innodb_stats_modified_counter 0 +select * from information_schema.global_variables where variable_name='innodb_stats_modified_counter'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_MODIFIED_COUNTER 0 +select * from information_schema.session_variables where variable_name='innodb_stats_modified_counter'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_MODIFIED_COUNTER 0 +set global innodb_stats_modified_counter=10; +select @@global.innodb_stats_modified_counter; +@@global.innodb_stats_modified_counter +10 +select * from information_schema.global_variables where variable_name='innodb_stats_modified_counter'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_MODIFIED_COUNTER 10 +select * from information_schema.session_variables where variable_name='innodb_stats_modified_counter'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_MODIFIED_COUNTER 10 +set session innodb_stats_modified_counter=1; +ERROR HY000: Variable 'innodb_stats_modified_counter' is a GLOBAL variable and should be set with SET GLOBAL +set global innodb_stats_modified_counter=1.1; +ERROR 42000: Incorrect argument type to variable 'innodb_stats_modified_counter' +set global innodb_stats_modified_counter=1e1; +ERROR 42000: Incorrect argument type to variable 'innodb_stats_modified_counter' +set global innodb_stats_modified_counter="foo"; +ERROR 42000: Incorrect argument type to variable 'innodb_stats_modified_counter' +set global innodb_stats_modified_counter=-7; +Warnings: +Warning 1292 Truncated incorrect innodb_stats_modified_counter value: '-7' +select @@global.innodb_stats_modified_counter; +@@global.innodb_stats_modified_counter +0 +select * from information_schema.global_variables where variable_name='innodb_stats_modified_counter'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_MODIFIED_COUNTER 0 +SET @@global.innodb_stats_modified_counter = @start_global_value; +SELECT @@global.innodb_stats_modified_counter; +@@global.innodb_stats_modified_counter +0 === added file 'mysql-test/suite/sys_vars/r/innodb_stats_traditional_basic.result' --- mysql-test/suite/sys_vars/r/innodb_stats_traditional_basic.result 1970-01-01 00:00:00 +0000 +++ mysql-test/suite/sys_vars/r/innodb_stats_traditional_basic.result 2014-11-19 18:27:34 +0000 @@ -0,0 +1,92 @@ +SET @start_global_value = @@global.innodb_stats_traditional; +SELECT @start_global_value; +@start_global_value +0 +Valid values are 'ON' and 'OFF' +select @@global.innodb_stats_traditional in (0, 1); +@@global.innodb_stats_traditional in (0, 1) +1 +select @@global.innodb_stats_traditional; +@@global.innodb_stats_traditional +0 +select @@session.innodb_stats_traditional; +ERROR HY000: Variable 'innodb_stats_traditional' is a GLOBAL variable +show global variables like 'innodb_stats_traditional'; +Variable_name Value +innodb_stats_traditional OFF +show session variables like 'innodb_stats_traditional'; +Variable_name Value +innodb_stats_traditional OFF +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL OFF +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL OFF +set global innodb_stats_traditional='OFF'; +select @@global.innodb_stats_traditional; +@@global.innodb_stats_traditional +0 +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL OFF +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL OFF +set @@global.innodb_stats_traditional=1; +select @@global.innodb_stats_traditional; +@@global.innodb_stats_traditional +1 +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL ON +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL ON +set global innodb_stats_traditional=0; +select @@global.innodb_stats_traditional; +@@global.innodb_stats_traditional +0 +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL OFF +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL OFF +set @@global.innodb_stats_traditional='ON'; +select @@global.innodb_stats_traditional; +@@global.innodb_stats_traditional +1 +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL ON +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL ON +set session innodb_stats_traditional='OFF'; +ERROR HY000: Variable 'innodb_stats_traditional' is a GLOBAL variable and should be set with SET GLOBAL +set @@session.innodb_stats_traditional='ON'; +ERROR HY000: Variable 'innodb_stats_traditional' is a GLOBAL variable and should be set with SET GLOBAL +set global innodb_stats_traditional=1.1; +ERROR 42000: Incorrect argument type to variable 'innodb_stats_traditional' +set global innodb_stats_traditional=1e1; +ERROR 42000: Incorrect argument type to variable 'innodb_stats_traditional' +set global innodb_stats_traditional=2; +ERROR 42000: Variable 'innodb_stats_traditional' can't be set to the value of '2' +set global innodb_stats_traditional=-3; +ERROR 42000: Variable 'innodb_stats_traditional' can't be set to the value of '-3' +select @@global.innodb_stats_traditional; +@@global.innodb_stats_traditional +1 +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL ON +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_STATS_TRADITIONAL ON +set global innodb_stats_traditional='AUTO'; +ERROR 42000: Variable 'innodb_stats_traditional' can't be set to the value of 'AUTO' +SET @@global.innodb_stats_traditional = @start_global_value; +SELECT @@global.innodb_stats_traditional; +@@global.innodb_stats_traditional +0 === added file 'mysql-test/suite/sys_vars/t/innodb_stats_modified_counter_basic.test' --- mysql-test/suite/sys_vars/t/innodb_stats_modified_counter_basic.test 1970-01-01 00:00:00 +0000 +++ mysql-test/suite/sys_vars/t/innodb_stats_modified_counter_basic.test 2014-11-19 18:27:34 +0000 @@ -0,0 +1,47 @@ +--source include/have_innodb.inc + +SET @start_global_value = @@global.innodb_stats_modified_counter; +SELECT @start_global_value; + +# +# exists as global only +# +--echo Valid values are one or above +select @@global.innodb_stats_modified_counter >=1; +select @@global.innodb_stats_modified_counter; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.innodb_stats_modified_counter; +show global variables like 'innodb_stats_modified_counter'; +show session variables like 'innodb_stats_modified_counter'; +select * from information_schema.global_variables where variable_name='innodb_stats_modified_counter'; +select * from information_schema.session_variables where variable_name='innodb_stats_modified_counter'; + +# +# show that it's writable +# +set global innodb_stats_modified_counter=10; +select @@global.innodb_stats_modified_counter; +select * from information_schema.global_variables where variable_name='innodb_stats_modified_counter'; +select * from information_schema.session_variables where variable_name='innodb_stats_modified_counter'; +--error ER_GLOBAL_VARIABLE +set session innodb_stats_modified_counter=1; + +# +# incorrect types +# +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_stats_modified_counter=1.1; +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_stats_modified_counter=1e1; +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_stats_modified_counter="foo"; + +set global innodb_stats_modified_counter=-7; +select @@global.innodb_stats_modified_counter; +select * from information_schema.global_variables where variable_name='innodb_stats_modified_counter'; + +# +# cleanup +# +SET @@global.innodb_stats_modified_counter = @start_global_value; +SELECT @@global.innodb_stats_modified_counter; === added file 'mysql-test/suite/sys_vars/t/innodb_stats_traditional_basic.test' --- mysql-test/suite/sys_vars/t/innodb_stats_traditional_basic.test 1970-01-01 00:00:00 +0000 +++ mysql-test/suite/sys_vars/t/innodb_stats_traditional_basic.test 2014-11-19 18:27:34 +0000 @@ -0,0 +1,65 @@ +--source include/have_innodb.inc + +SET @start_global_value = @@global.innodb_stats_traditional; +SELECT @start_global_value; + +# +# exists as global only +# +--echo Valid values are 'ON' and 'OFF' +select @@global.innodb_stats_traditional in (0, 1); +select @@global.innodb_stats_traditional; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.innodb_stats_traditional; +show global variables like 'innodb_stats_traditional'; +show session variables like 'innodb_stats_traditional'; +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; + +# +# show that it's writable +# +set global innodb_stats_traditional='OFF'; +select @@global.innodb_stats_traditional; +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +set @@global.innodb_stats_traditional=1; +select @@global.innodb_stats_traditional; +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +set global innodb_stats_traditional=0; +select @@global.innodb_stats_traditional; +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +set @@global.innodb_stats_traditional='ON'; +select @@global.innodb_stats_traditional; +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +--error ER_GLOBAL_VARIABLE +set session innodb_stats_traditional='OFF'; +--error ER_GLOBAL_VARIABLE +set @@session.innodb_stats_traditional='ON'; + +# +# incorrect types +# +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_stats_traditional=1.1; +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_stats_traditional=1e1; +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_stats_traditional=2; +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_stats_traditional=-3; +select @@global.innodb_stats_traditional; +select * from information_schema.global_variables where variable_name='innodb_stats_traditional'; +select * from information_schema.session_variables where variable_name='innodb_stats_traditional'; +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_stats_traditional='AUTO'; + +# +# Cleanup +# + +SET @@global.innodb_stats_traditional = @start_global_value; +SELECT @@global.innodb_stats_traditional; === modified file 'storage/innobase/btr/btr0cur.c' --- storage/innobase/btr/btr0cur.c 2014-07-30 07:05:01 +0000 +++ storage/innobase/btr/btr0cur.c 2014-11-19 18:27:34 +0000 @@ -3764,16 +3764,29 @@ ut_error; } - /* It makes no sense to test more pages than are contained - in the index, thus we lower the number if it is too high */ - if (srv_stats_sample_pages > index->stat_index_size) { + if (srv_stats_sample_traditional) { + /* It makes no sense to test more pages than are contained + in the index, thus we lower the number if it is too high */ + if (srv_stats_sample_pages > index->stat_index_size) { + if (index->stat_index_size > 0) { + n_sample_pages = index->stat_index_size; + } else { + n_sample_pages = 1; + } + } else { + n_sample_pages = srv_stats_sample_pages; + } + } else { + /* New logaritmic number of pages that are estimated. We + first pick minimun from srv_stats_sample_pages and number of + pages on index. Then we pick maximum from previous number of + pages and log2(number of index pages) * srv_stats_sample_pages. */ if (index->stat_index_size > 0) { - n_sample_pages = index->stat_index_size; + n_sample_pages = ut_max(ut_min(srv_stats_sample_pages, index->stat_index_size), + log2(index->stat_index_size)*srv_stats_sample_pages); } else { n_sample_pages = 1; } - } else { - n_sample_pages = srv_stats_sample_pages; } /* We sample some pages in the index to get an estimate */ === modified file 'storage/innobase/handler/ha_innodb.cc' --- storage/innobase/handler/ha_innodb.cc 2014-07-29 07:09:52 +0000 +++ storage/innobase/handler/ha_innodb.cc 2014-11-19 18:27:34 +0000 @@ -11739,6 +11739,16 @@ "The number of index pages to sample when calculating statistics (default 8)", NULL, NULL, 8, 1, ~0ULL, 0); +static MYSQL_SYSVAR_ULONGLONG(stats_modified_counter, srv_stats_modified_counter, + PLUGIN_VAR_RQCMDARG, + "The number of rows modified before we calculate new statistics (default 0 = current limits)", + NULL, NULL, 0, 0, ~0ULL, 0); + +static MYSQL_SYSVAR_BOOL(stats_traditional, srv_stats_sample_traditional, + PLUGIN_VAR_RQCMDARG, + "Enable traditional statistic calculation based on number of configured pages (default false)", + NULL, NULL, FALSE); + static MYSQL_SYSVAR_BOOL(adaptive_hash_index, btr_search_enabled, PLUGIN_VAR_OPCMDARG, "Enable InnoDB adaptive hash index (enabled by default). " @@ -12001,6 +12011,8 @@ MYSQL_SYSVAR(rollback_on_timeout), MYSQL_SYSVAR(stats_on_metadata), MYSQL_SYSVAR(stats_sample_pages), + MYSQL_SYSVAR(stats_modified_counter), + MYSQL_SYSVAR(stats_traditional), MYSQL_SYSVAR(adaptive_hash_index), MYSQL_SYSVAR(stats_method), MYSQL_SYSVAR(replication_delay), === modified file 'storage/innobase/include/dict0dict.h' --- storage/innobase/include/dict0dict.h 2014-08-02 19:26:16 +0000 +++ storage/innobase/include/dict0dict.h 2014-11-19 18:27:34 +0000 @@ -1123,7 +1123,13 @@ /*========================*/ const dict_index_t* index); /*!< in: index */ -/** Calculate new statistics if 1 / 16 of table has been modified +/** + +If user has provided upper bound for how many rows needs to be updated +before we calculate new statistics we use minimum of provided value +and 1/16 of table every 16th round. If no upper bound is provided +(srv_stats_modified_counter = 0, default) then calculate new statistics +if 1 / 16 of table has been modified since the last time a statistics batch was run. We calculate statistics at most every 16th round, since we may have a counter table which is very small and updated very often. @@ -1132,7 +1138,9 @@ recalculated */ #define DICT_TABLE_CHANGED_TOO_MUCH(t) \ - ((ib_int64_t) (t)->stat_modified_counter > 16 + (t)->stat_n_rows / 16) + ((ib_int64_t) (t)->stat_modified_counter > (srv_stats_modified_counter ? \ + ut_min(srv_stats_modified_counter, (16 + (t)->stat_n_rows / 16)) : \ + 16 + (t)->stat_n_rows / 16)) /*********************************************************************//** Calculates new estimates for table and index statistics. The statistics === modified file 'storage/innobase/include/srv0srv.h' --- storage/innobase/include/srv0srv.h 2014-07-08 14:21:13 +0000 +++ storage/innobase/include/srv0srv.h 2014-11-19 18:27:34 +0000 @@ -211,6 +211,8 @@ extern ibool srv_innodb_status; extern unsigned long long srv_stats_sample_pages; +extern unsigned long long srv_stats_modified_counter; +extern my_bool srv_stats_sample_traditional; extern ibool srv_use_doublewrite_buf; extern ibool srv_use_atomic_writes; === modified file 'storage/innobase/srv/srv0srv.c' --- storage/innobase/srv/srv0srv.c 2014-08-19 16:28:35 +0000 +++ storage/innobase/srv/srv0srv.c 2014-11-19 18:27:34 +0000 @@ -749,6 +749,13 @@ static ulint srv_meter_high_water2[SRV_MASTER + 1]; static ulint srv_meter_foreground[SRV_MASTER + 1]; #endif +/* The number of rows modified before we calculate new statistics (default 0 += current limits) */ +UNIV_INTERN unsigned long long srv_stats_modified_counter = 0; + +/* Enable traditional statistic calculation based on number of configured +pages default false. */ +UNIV_INTERN my_bool srv_stats_sample_traditional = FALSE; /* The following values give info about the activity going on in the database. They are protected by the server mutex. The arrays